Feeling beatup by Combobox

G

Greg Hadrych

This sounds strange but this is what i have. I have the
code written for when a user selects a item from a combo
box, it will take them to the desired worksheet.


Private Sub ComboBox1_Click()
Select Case ComboBox1.Value
Case 0
Case 1
Sheets("User Name Change").Select
Case 2
Sheets("Price plan change - vision").Select
Case 3
Sheets("Price plan change - I2K").Select
Case 4
Sheets("Add 1yr contract - vision").Select
Case 5
Sheets("Validate NPA NXX").Select
Case 6
Sheets("Group ID - Acct").Select
Case 7
Sheets("Group ID - MTN").Select

End Select
End Sub

But the problem is that i can not add the text to the
combobox. I can not find a way to show the text when you
click on the down arrow.
 
B

Bob Phillips

List the sheets in a range on a worksheet and set the comboboxes
ListFillRange property to that range address(A1:A10 for instance)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Harald Staff

It's not clear to me what your problem is. But if your combo contains sheet
names then

Private Sub ComboBox1_Click()
Sheets(ComboBox1.Text).Activate
End Sub

and if the problem is to make it contain sheet names then

Private Sub FillCombo()
Dim Sh As Object
With ComboBox1
..Clear
For Each Sh In ActiveWorkbook.Sheets
.AddItem Sh.Name
Next
End With
End Sub

HTH. Best wishes Harald
 
G

Greg Hadrych

This worked but now when I click on of the names in the
drop down, nothing happens. Do I need to assign a macro
to each selection?

As far the other response, your second code worked, but
there are a few sheets I don't want to show. Is it
possible to manually enter in the sheet names?
 
H

Harald Staff

As far the other response, your second code worked,

Man am I glad to hear that.
but
there are a few sheets I don't want to show. Is it
possible to manually enter in the sheet names?
Click to expand...

Something like this perhaps ?

Private Sub FillCombo()
With ComboBox1
..Clear
..AddItem "But"
..AddItem "Marge"
..AddItem "they're"
..AddItem "only"
..AddItem "space"
..AddItem "mutants"
End With
End Sub

HTH. Best wishes Harald
 
B

Bob Phillips

Using a list of names in a worksheet range, just don't add the names you
don't want. It's easier to maintain a worksheet than code (IMO).

As to the other question, Harald has answered that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top