control toolbar combo box and command button

[maijiuli]

Hello,

I'm interested in using a combo box and command button from the control
toolbar. The combo box will contain the names of all the worksheets in this
file. This is done with a dynamic named range. The command button will act
like a "submit" button to take the user to the desired worksheet selected
from the combo box.

The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
Is there a way to do this?

Thanks in advance,

MJ

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure where you are running your controls from (a userform or the
spreadsheet directly), so where you put this is up in the air. Here, I have
assumed that you have a userform, so I placed it in the Activate event for
that userform...

Private Sub UserForm_Activate()
Dim SH As Worksheet
If ComboBox1.ListCount = 0 Then
For Each SH In Worksheets
ComboBox1.AddItem SH.Name
Next
End If
End Sub

If the ComboBox has nothing in it, the above code will populate it with the
worksheet names. Put this in the CommandButton's Click event to select the
worksheet name in the ComboBox...

Private Sub CommandButton1_Click()
If Len(ComboBox1.Text) Then
Worksheets(ComboBox1.Text).Activate
Else
MsgBox "Please select a worksheet to go to."
End If
End Sub

Rick

 

[maijiuli]

Thank you Mr. Rothstein. My controls are placed directly on the spreadsheet
so I put your codes into the Combo Box change and Command button click areas
of the VB editor. Thank you so much!

 

[Dave Peterson]

Maybe you could use something like:
http://contextures.com/xlToolbar01.html

 

[maijiuli]

Dave or should I say Sir Peterson, you are a genious. I can't thank you
enough for this one.

MJ

 

[maijiuli]

Sire, is there a way to change your Navigation toolbar to live only in a
specific file.

I've been playing around with it for a couple hours now and it works great
but when I open other files the Navigation Toolbar always pops up in the
floating method.

To share this with my company I need the toolbar to either live in only one
file so it does not disrupt others files.

OR

If we can find a way to change the macro so it is not in the float position
on open. Maybe if the macro can be changed to live with all the other
toolbars on top when a file is open then this would probably be ok. At least
this way it is not in the way to the other users who want to use this addin.

Sorry if I'm asking for too much but it's too good to let go,

 

[Dave Peterson]

I wouldn't put it a specific workbook. The point of the refresh button is so
that you can change workbooks and click that button and use it there. (Or
add/delete worksheets and refresh the list.)

Personally, I liked to make that toolbar appear in the worksheet. By floating
it over the worksheet, the user has a better chance of noticing it. They can
always reposition it where they want--either by dragging it or by double
clicking on the title bar.

But you can dock it if you add a couple of lines of code:

With cb
.Visible = True
.RowIndex = msoBarRowLast '<-- Added
.Position = msoBarTop '<-- Added

========
I'd keep this code separate from every other workbook, too. I'd put it in a
dedicated workbook and save it as an addin. Then just load it on demand, or put
it in my XLStart folder, or use Tools|Addins to install it.

Sire, is there a way to change your Navigation toolbar to live only in a
specific file.

I've been playing around with it for a couple hours now and it works great
but when I open other files the Navigation Toolbar always pops up in the
floating method.

To share this with my company I need the toolbar to either live in only one
file so it does not disrupt others files.

OR

If we can find a way to change the macro so it is not in the float position
on open. Maybe if the macro can be changed to live with all the other
toolbars on top when a file is open then this would probably be ok. At least
this way it is not in the way to the other users who want to use this addin.

Sorry if I'm asking for too much but it's too good to let go,

 

[maijiuli]

Brilliant! Thanks again Sir.
--
Thank You!

I wouldn't put it a specific workbook. The point of the refresh button is so
that you can change workbooks and click that button and use it there. (Or
add/delete worksheets and refresh the list.)

Personally, I liked to make that toolbar appear in the worksheet. By floating
it over the worksheet, the user has a better chance of noticing it. They can
always reposition it where they want--either by dragging it or by double
clicking on the title bar.

But you can dock it if you add a couple of lines of code:

With cb
.Visible = True
.RowIndex = msoBarRowLast '<-- Added
.Position = msoBarTop '<-- Added

========
I'd keep this code separate from every other workbook, too. I'd put it in a
dedicated workbook and save it as an addin. Then just load it on demand, or put
it in my XLStart folder, or use Tools|Addins to install it.