Use combobox value as sheetname to set sheet object

[Greg Snidow]

Greetings all. I've got a userform with a combo that is populated with the
workbook sheet names with code I found here...

For Each sh In ActiveWorkbook.Sheets
Me.ComboBox1.AddItem sh.Name
Next

I would like to set a sheet object using combobox1, but I can not figure it
out. Basically this is what I want to do

Dim Ws as worksheet

Set Ws = Activeworkbook.sheet(combobox1).

Now I know this does not work, but I can't figure out how to do it. My
first thought was to have the combo box be 2 columns, one of which would
contain the index number of the sheets in the workbook. Any ideas? Thank
you.

Greg

 

[Ryan H]

Set wks = ActiveWorkbook.Sheets(ComboBox1.Text)

Hope this helps! If so, let me know, click "YES" below.

 

[Jeff]

you're alomost there

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets(ComboBox1.Value)
ws.Activate

End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Sheets
Me.ComboBox1.AddItem sh.Name
Next


End Sub

 

[Dave Peterson]

You may want to add some checks, too:

Option Explicit
Private Sub CommandButton1_Click()
Dim Ws As Object 'still any kind of sheet

Set Ws = Nothing
On Error Resume Next
Set Ws = ActiveWorkbook.Sheets(Me.ComboBox1.Value)
On Error GoTo 0

If Ws Is Nothing Then
Me.Label1.Caption = "No Sheet with that name in the active workbook"
Else
Me.Label1.Caption = ""
Ws.Activate
End If
End Sub
Private Sub UserForm_Initialize()
Dim sh As Object 'any kind of sheet
For Each sh In ActiveWorkbook.Sheets
Me.ComboBox1.AddItem sh.Name
Next sh
Me.Label1.Caption = ""
End Sub