VBA - Userform - Combobox - Select Workbook/Sheet

Question

I asked another question recently, which helped me get to a certain point with my 'combobox' userform but now need a little more help to get me over the line please.

I've created a userform which has 2 comboboxes, 1 which displays all the open excel workbooks and 1 which displays the worksheets for the selected workbook.

I've tried creating a button that uses the value selected under the worksheet box to copy data from the specified sheet.

The issue I'm having is relating the worksheet combobox (Cb_Ws) value to a VBA command that will 'on click' of the button, select that worksheet and then copy a specific range e.g. Worksheets("Cb_Ws.Value").Range("X77:X84").Copy

I'm afraid my knowledge isn't great as I'm starting out although I think I'm somewhere near the right lines.

Here's the code I've got so far:

Option Explicit

Dim wb As Workbook
Dim ws As Worksheet

Private Sub Cb_Wb_Change()
Me.Cb_Ws.Clear
On Error Resume Next
For Each ws In Workbooks(Me.Cb_Wb.Value).Worksheets
Me.Cb_Ws.AddItem ws.Name
Next ws
End Sub

Private Sub CommandButton1_Click()

Dim Worksheets as (Cb_Ws.Value)

Worksheets.Range("X77:X84").Copy

End Sub


Private Sub UserForm_Initialize()

For Each wb In Application.Workbooks
Me.Cb_Wb.AddItem wb.Name
Me.Label2.Caption = "Select WorkSheet:"
Me.Label1.Caption = "Select Workbook:"
Next wb

End Sub

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-05-13T09:39:12

That is an incorrect way of working with objects. Is this what you are trying?

Private Sub CommandButton1_Click()
   Dim ws As Worksheet

   Set ws = Workbooks(Cb_Wb.Value).Sheets(Cb_Ws.Value)

   ws.Range("X77:X84").Copy
End Sub

VBA - Userform - Combobox - Select Workbook/Sheet

1 Answers