Combo box instead of command buttons

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,

I have a three command buttons, one links to a particular
cell on a worksheet and the other two open up a browser
window. The VB for these buttons are:

Private Sub CommandButton1_Click()
ActiveWorkbook.FollowHyperlink
Address:="HTML\DevTeam.htm", _
NewWindow:=True
End Sub


Private Sub CommandButton2_Click()
ActiveWorkbook.FollowHyperlink
Address:="HTML\DevTeam2.htm", _
NewWindow:=True
End Sub

Private Sub CommandButton3_Click()
Application.Goto Reference:=Worksheets("MetaData").Range
("B6"), Scroll:=True
End Sub

What i want to do is have a combobox that has these three
buttons inside. Therefore acting as a drop down menu and
preventing having three buttons displayed.

Thanks for the help in advance.

John
 
Hi John

One way (among plenty) that I hope get you started. Place a ComboBox1 on a
sheet. Rightclicksheet tab, choose "View code", paste this in:

Private Sub Worksheet_Activate()
With Me.ComboBox1
..Clear
..AddItem "(Select one:)"
..AddItem "Go to location 1"
..AddItem "Go to location 2"
..AddItem "Go to location 3"
..ListIndex = 0
End With
End Sub

Private Sub ComboBox1_Change()
Select Case ComboBox1.ListIndex
Case 1
MsgBox "call Macro one here"
Case 2
MsgBox "call Macro two here"
Case 3
MsgBox "call Macro three here"
Case Else
End Select
End Sub

Now return to Excel, select another sheet and go back to fill the box.
 
Harald,

Thanks for gettting back to me. I've done what you
suggested and got a compile error:systex error. It
highlighted in yellow "Private Sub Worksheet_Activate()"
and the "..clear" down to "..ListIndex" is coloured red.

Any ideas. Have i done something wrong? The box in the
excel hasn't filled with any text.

Thanks again for the help. I'm very grateful.

Regards,

John
 
Hi John

Which version are you using ?
Use a combobox from the Controls Toolbax, not from the Forms toolbar.
 
Harald,

I'm using Excel 2002 (XP). I did use the combobox from the
Controls Toolbar. Bizzare!

Thanks

John
 
Hey, do I see two dots in front of each command ?
...Clear
When did that happen ? It's only one in each line:

..Clear
 
Ah Ha,

The code is working now. So when it says "call Macro one
here" do i replace this with the command button text:

Private Sub CommandButton1_Click()
ActiveWorkbook.FollowHyperlink
Address:="HTML\DevTeam.htm", _
NewWindow:=True
End Sub

or would this confuse it?

Regards,

John
 
That's a question of personal style. You can replace it with either

ActiveWorkbook.FollowHyperlink Address:="HTML\DevTeam.htm", _
NewWindow:=True

or

Call CommandButton1_Click
-assuming that you won't delete CommandButton1, that is. Or you can replace
with

Call Macro1

and if so add this macro either below the sub or in a standard module:

Sub Macro1()
ActiveWorkbook.FollowHyperlink Address:="HTML\DevTeam.htm", _
NewWindow:=True
End Sub
 
Brilliant. I've played around with it and managed to get
it working ok. My full VB is:

Private Sub Worksheet_Activate()
With Me.ComboBox1
..Clear
..AddItem "Select one"
..AddItem "MoreInfo1"
..AddItem "MoreInfo2"
..AddItem "MetaData"
..ListIndex = 0
End With
End Sub

Private Sub ComboBox1_Change()
Select Case ComboBox1.ListIndex
Case 1
ActiveWorkbook.FollowHyperlink
Address:="HTML\DevTeam.htm", _
NewWindow:=True
Case 2
ActiveWorkbook.FollowHyperlink
Address:="HTML\DevTeam2.htm", _
NewWindow:=True
Case 3
Application.Goto Reference:=Worksheets("MetaData").Range
("B6"), Scroll:=True
Case Else
End Select
End Sub

Its working ok but one final question. How do i get the
combobox to stay in the cell i place it in. I've used the
various move and size with cells option but i want the
combobox to stay in the cell and not resize in anyway if a
user adds or delete text in the cell. I know i could use a
column next to the cell with the button in but it will
iterrupt my ordering. Is there a way to house it in the
cell.

Thanks again for all your help. I'm very grateful.

Regards,

Jon
 
Hi Jon

Rightclick the combo, chose Format Control. Somewhere there (I don't have an
english version at hand) there's a setting "resize/move with cells" or
similar. But note that these things floatr above the actual sheet on a
separate layer, so you may or may not get a perfect match, but it's not at
any point "in a cell", just completely or part on top of it.

Note also that the event that fills the box at the moment is the
sheet-activate event. You may have other more suitable events for it,
depending on the task. See
http://www.cpearson.com/excel/events.htm
on this.

Glad you're happy. I'm just hungry, thirsty and tired. Logging of for now.
 
Back
Top