Userform help

[Damo10]

Hi,

I am trying to set the worksheet name in my code to that of a selection from a combobox. I cant seem to get the code right for this part, the rest works fine can anyone help?

Regards

Private Sub CommandButton1_Click()
Dim lr As Long
Dim x As Long
Dim list As String
Dim ws1 As Worksheet
Dim sh As String
ws1 = Me.cboSheet - Problem with this line
With ws1
lr = .Range("B" & Rows.Count).End(xlUp).Row
End With
For x = 10 To lr
With ws1
list = .Range("B" & x) & " " & .Range("C" & x)
Me.cboList.AddItem list
End With
Next x
End Sub
Private Sub UserForm_Initialize()
Dim sh As String
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
sh = ws.Name
Me.cboSheet.AddItem sh
Next ws
End Sub

 

[Hermanito]

Since it's an object you need to use Set when assigning, like this:

Set ws1 = Me.cboSheet 'no more problem with this line
[/COLOR]

 

[Damo10]

Hi,

I tried that first and still get the same error

Run time error 91
Object variable or with block variable not set

Regards

 

[Hermanito]

oops, my bad, I only looked at the line in red

you should declare ws1 and instantiate it, like this

Dim ws1 As New Worksheet

or like this

Dim ws1 As Worksheet
Set ws1 = new Worksheet

difference between the two is cause for major debate, but comes down to personal preference

 

[Damo10]

Hi,

I have tried that and it does not work

I am trying to use a userform to select one of the existing sheets in my workbook and then from the selection populate a combobox with the values from the sheet selected from the first combobox.

Regards

 

[Hermanito]

Hmm, okay, so the rest of your code does not work fine, like you claimed

This commandbutton, is that a button on a worksheet or on your userform?

What error do you get now, and at what line in the code?

 

[Damo10]

the button is from the userform.
The error I get now is still on the same line and is a run time error 13 type mismatch

 

[Hermanito]

Can you try this:

Set ws1 = ThisWorkbook.Worksheets(Me.cboSheet)

I'm still trying to figure out your code...

 

[Hashiru]

Hi how are you.

Yes there is a mismatch you declare wsl as a worksheet and then you equate that to Me.cbo...... which is an object on the userform. What do you what to achieve from this line?

Are you loading the activesheets name of the activework at initialization ( when loading the userform). Can you through light on the overall plan of the userform. This will help to resolve the issues around the errors