Combo box instead of command buttons

[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

 

[Harald Staff]

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.

 

[Guest]

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

 

[Harald Staff]

Hi John

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

 

[John]

Harald,

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

Thanks

John

 

[Harald Staff]

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

..Clear

 

[John]

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

 

[Harald Staff]

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

 

[John]

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

 

[Harald Staff]

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.