passing a comboxbox result into sheets.select
 

I have a form that has a combo box in it. The combo box allows the user to
select a chapter. The chapter happens to be worksheet in a workbook.

How do i pass from the combo box into the macro.

This is what I want:
With ComboBox8
.AddItem "Chapter 1"
.AddItem "Chapter 2"
.AddItem "Chapter 3"
End With
Chapter = ComboBox8.Value
Sheets(Chapter).Select

*** if I write Sheets("Chapter 1") it works great...but just putting it
Chapter fails

passing a comboxbox result into sheets.select
 

Where is this code, ou are only showing a snippet. The code below is using
the Form1_Initialize() event to load the items in the combobox and a button
on the form, hides it and selects the sheet from the combobox value.

Private Sub UserForm_Initialize()
With UserForm1.ComboBox8
.AddItem "Chapter 1"
.AddItem "Chapter 2"
.AddItem "Chapter 3"
End With
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
Dim Chapter As String
Chapter = UserForm1.ComboBox8.Value
UserForm1.Hide
Sheets(Chapter).Select
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"tbaam" wrote in message
...
I have a form that has a combo box in it. The combo box allows the user to
select a chapter. The chapter happens to be worksheet in a workbook.

How do i pass from the combo box into the macro.

This is what I want:
With ComboBox8
.AddItem "Chapter 1"
.AddItem "Chapter 2"
.AddItem "Chapter 3"
End With
Chapter = ComboBox8.Value
Sheets(Chapter).Select

*** if I write Sheets("Chapter 1") it works great...but just putting it
Chapter fails

passing a comboxbox result into sheets.select
 

The problem is that you are adding the items in the same
procedure as you are using to select the sheet.

Remove the following lines from you existing code

Chapter = ComboBox8.Value
Sheets(Chapter).Select

and insert the following code in the sheet's code module:

Private Sub ComboBox8_Change()
Dim Chapter As String
Chapter = Me.ComboBox8.Value
Worksheets(Chapter).Select
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"tbaam" wrote in message
...
I have a form that has a combo box in it. The combo box allows
the user to
select a chapter. The chapter happens to be worksheet in a
workbook.

How do i pass from the combo box into the macro.

This is what I want:
With ComboBox8
.AddItem "Chapter 1"
.AddItem "Chapter 2"
.AddItem "Chapter 3"
End With
Chapter = ComboBox8.Value
Sheets(Chapter).Select

*** if I write Sheets("Chapter 1") it works great...but just
putting it
Chapter fails

passing a comboxbox result into sheets.select
 

Try this:

'In the change event of your combobox
Private Sub ComboBox1_Change()

If Not Me.ComboBox1.Value = "" Then
Sheets(Me.ComboBox1.Value).Select
End If

End Sub

'To load sheet names when initializing the form
Private Sub UserForm_Initialize()

Dim s As Excel.Worksheet

For Each s In ThisWorkbook.Worksheets
Me.ComboBox1.AddItem s.Name
Next

End Sub

HTH.

- Luther

passing a comboxbox result into sheets.select
 

Thank you....i wasn't sure how much of the code I should put in the post...but
all of you answered my question.
It works great!

"lcifers" wrote:

Try this:

'In the change event of your combobox
Private Sub ComboBox1_Change()

If Not Me.ComboBox1.Value = "" Then
Sheets(Me.ComboBox1.Value).Select
End If

End Sub

'To load sheet names when initializing the form
Private Sub UserForm_Initialize()

Dim s As Excel.Worksheet

For Each s In ThisWorkbook.Worksheets
Me.ComboBox1.AddItem s.Name
Next

End Sub

HTH.

- Luther