I am creating a listbox on a worksheet that will have about 70
"accounts". Each account has it's own seperate worksheet. I woul
ultimately like to be able to select the appropriate "account" an
using a command button be able to open it. I am a completel
unfamiliar with VBA and purchased a book to learn it - but I am stil
confused.

Any help/references would be GREATLY appreciated! thanks

--
jayse
-----------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=38069

How about a floating toolbar that can show you the names of worksheets to
select:
http://groups.google.com/groups?thre...1C74%40msn.com

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaysen wrote:

I am creating a listbox on a worksheet that will have about 70+
"accounts". Each account has it's own seperate worksheet. I would
ultimately like to be able to select the appropriate "account" and
using a command button be able to open it. I am a completely
unfamiliar with VBA and purchased a book to learn it - but I am still
confused.

Any help/references would be GREATLY appreciated! thanks.

--
jaysen
------------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...o&userid=24120
View this thread: http://www.excelforum.com/showthread...hreadid=380698

--

Dave Peterson

Dave-

Thanks! I am having trouble getting the floating toolbar to work...
giving me some erros when trying to run. Just of curiosity how
hard/difficult would it be to accomplish what I'm asking. (above)


--
jaysen
------------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...o&userid=24120
View this thread: http://www.excelforum.com/showthread...hreadid=380698

I think the code would look very similar to the code that populates the floating
toolbar.

You may want to post more details about the problems you're having.

Another alternative is to use a table of contents worksheet that uses hyperlinks
to go to the other sheets.

You can find some code at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

jaysen wrote:

Dave-

Thanks! I am having trouble getting the floating toolbar to work...
giving me some erros when trying to run. Just of curiosity how
hard/difficult would it be to accomplish what I'm asking. (above)

--
jaysen
------------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...o&userid=24120
View this thread: http://www.excelforum.com/showthread...hreadid=380698

--

Dave Peterson

I am running into Error 91 in sub ChangeTheSheet

Line: *If .ListIndex = 0 Then*

Any help would be greatly appreciated.


--
jaysen
------------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...o&userid=24120
View this thread: http://www.excelforum.com/showthread...hreadid=380698

Copying and pasting from Google seems to add blanks or dashes.

I copied and pasted and cleaned up and this worked ok:

Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
ctrl.AddItem wks.Name
Next wks
End Sub


If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaysen wrote:

I am running into Error 91 in sub ChangeTheSheet

Line: *If .ListIndex = 0 Then*

Any help would be greatly appreciated.

--
jaysen
------------------------------------------------------------------------
jaysen's Profile: http://www.excelforum.com/member.php...o&userid=24120
View this thread: http://www.excelforum.com/showthread...hreadid=380698

--

Dave Peterson