Use combobox value as sheetname to set sheet object
 

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

Use combobox value as sheetname to set sheet object
 

Set wks = ActiveWorkbook.Sheets(ComboBox1.Text)

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


"Greg Snidow" wrote:

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

Use combobox value as sheetname to set sheet object
 

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


"Greg Snidow" wrote:

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

Use combobox value as sheetname to set sheet object
 

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



Greg Snidow wrote:

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

--

Dave Peterson