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

Dave-

I am still having troubles with the same issue and getting the macro to
run. I really appreciate all the help wi th this - extrememly
frustrated now.

I have read every tutorial known to man, bought every book on vba for
excel, and still have no knowledge of what I REALLY want to accomplish.
(maybe I am over exaggerating)

This is what I have so far... as of now two of my five macros work.
[image: http://www.digital8ball.net/excel.jpg]


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

Dave-

I finally got it to work... Unfortunately it's not what I'm lookin
for... I would preferrably have it setup up the first method
proposed

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

Does worksheet mean workbook? I'm confused about what open means?

I'm guessing that worksheets means worksheets.

I put a combobox from the control toolbox toolbar on Sheet1.


I put this code behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In Me.Worksheets
If wks.Name = Me.Worksheets("Sheet1").Name Then
'do nothing
Else
Me.Worksheets("Sheet1").ComboBox1.AddItem wks.Name
End If
Next wks
End Sub

Then I put this code behind the Sheet1 module:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "" Then Exit Sub
On Error Resume Next
Me.Parent.Worksheets(Me.ComboBox1.Value).Select
If Err.Number < 0 Then
MsgBox "Error while changing sheets"
End If
On Error GoTo 0
End Sub




jaysen wrote:

Dave-

I finally got it to work... Unfortunately it's not what I'm looking
for... I would preferrably have it setup up the first method I
proposed.

--
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-

Appreciate the help... really - I do. I might have my terminology
mixed up. When I refer to 'worksheet' I am referring to the sheets in
the workbook (tabs at the bottom). My understanding of workbook is
each *.xls file - a workbook. I hope I'm correct.

Let me try explainging myself again;
(see screenshot for update)
I have a workbook with 70+ sheets (aka accounts)...
Instead of having to scroll with the tabs at the bottom to access each
sheet/account, I would like to have a listbox setup (see picture) so I
can select the 'account', use the command buttons at the right to
either; OPEN, PRINT, ETC... Before I try getting the other Macros to
work I really would like the Open - command to function properly.

EDIT: WHen I refer to "OPEN" I mean change the current sheet
(screenshot which is known as the main sheet) to the new selected
sheet/account

Hopefully I've clarified enough... THanks!

http://www.digital8ball.net/excel.jpg


--
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 you're going to have to modify each of the routines that the buttons
run.

Dim Wks as worksheet
On Error Resume Next
set wks = Me.Parent.Worksheets(Me.ComboBox1.Value)
on error goto 0

if wks is nothing then
beep
exit sub
end if

Then use wks as the worksheet in your other code.

Wks.printout

Wks.select

Wks.whateveryoudo



jaysen wrote:

Dave-

Appreciate the help... really - I do. I might have my terminology
mixed up. When I refer to 'worksheet' I am referring to the sheets in
the workbook (tabs at the bottom). My understanding of workbook is
each *.xls file - a workbook. I hope I'm correct.

Let me try explainging myself again;
(see screenshot for update)
I have a workbook with 70+ sheets (aka accounts)...
Instead of having to scroll with the tabs at the bottom to access each
sheet/account, I would like to have a listbox setup (see picture) so I
can select the 'account', use the command buttons at the right to
either; OPEN, PRINT, ETC... Before I try getting the other Macros to
work I really would like the Open - command to function properly.

EDIT: WHen I refer to "OPEN" I mean change the current sheet
(screenshot which is known as the main sheet) to the new selected
sheet/account

Hopefully I've clarified enough... THanks!

http://www.digital8ball.net/excel.jpg

--
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 Peterson Wrote:
I think you're going to have to modify each of the routines that the
buttons
run.

Dim Wks as worksheet
On Error Resume Next
set wks = Me.Parent.Worksheets(Me.ComboBox1.Value)
on error goto 0

if wks is nothing then
beep
exit sub
end if

Then use wks as the worksheet in your other code.

Wks.printout

Wks.select

Wks.whateveryoudo


Where exactly is this placed... ? and does this go in conjunction with
the code above...? *sigh* im so lost... hah.


--
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 you're going to have to modify each routine that is associated with each
button.

I don't know what that code looks like, but I thought that you'd need to add
something like I posted to each of those routines.

jaysen wrote:

Dave Peterson Wrote:
I think you're going to have to modify each of the routines that the
buttons
run.

Dim Wks as worksheet
On Error Resume Next
set wks = Me.Parent.Worksheets(Me.ComboBox1.Value)
on error goto 0

if wks is nothing then
beep
exit sub
end if

Then use wks as the worksheet in your other code.

Wks.printout

Wks.select

Wks.whateveryoudo


Where exactly is this placed... ? and does this go in conjunction with
the code above...? *sigh* im so lost... hah.

--
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... I think what I wanted to accomplish is going to be extrememl
difficult. I am starting to grow on the toolbar idea. I was wonderin
if we can accomplish the same idea as the toolbar in a combobox on th
worksheet? Perhaps I can just modify the code a bit to use as
combobox instead of a toolbar

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

I think that the bad news is that you have to look at each of the other
routines. Those are the ones that need to be modified. They have to be smart
enough to know which worksheet to use.

That toolbar stuff was only good for activating the worksheet. If you want to
run the code against the selected worksheet name, you'll have to do something so
that each routine knows what sheet to use.

I don't see any other way at all.







jaysen wrote:

Dave... I think what I wanted to accomplish is going to be extrememly
difficult. I am starting to grow on the toolbar idea. I was wondering
if we can accomplish the same idea as the toolbar in a combobox on the
worksheet? Perhaps I can just modify the code a bit to use as a
combobox instead of a toolbar?

--
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