Use list box to navigate to different sheets...
OK, Application.goto seems to be adversely affected by being in the sheet
module even though the range is qualified.
try it this way:
Private Sub ListSheets_Click()
On Error Resume Next
Set sh = ThisWorkbook.Worksheets(Listsheets.Value)
On Error GoTo 0
If Not sh Is Nothing Then
sh.Activate
sh.Range("A1").Select
End If
End Sub
--
Regards,
Tom Ogilvy
Fede Querio wrote in message
...
Tom,
I'm afraid it didn't work....are you sure the code is ok?
Federico
"Tom Ogilvy" wrote in message
...
If you add it from the control toolbox toolbar, then it is an activeX
listbox. Right after you add it, then you will be in design mode.
Double
click on the listbox and you will be taken to the click event (if you
are
going to rename the listbox, do that first by right clicking on it an
selecting properties. Then type in the new name. Let's assume your
name
it
listSheets)
Private Sub ListSheets_Click()
On Error Resume Next
set sh = thisWorkbook.worksheets(ListSheets.Value)
On Error goto 0
if not sh is nothing then
Application.goto sh.Range("A1")
End sub
You can also populate the listbox with the activate event in the same
module.
Private Sub Worksheet_Activate()
Listsheets.clear
for each sh in thisworkbook.Worksheets
if sh.name < me.name then
listsheets.additem sh.name
end if
Next
End sub
On the control toolbox toolbar, the upper left icon toggles design mode.
You need to get out of design mode for the listbox code to work.
Regards,
Tom Ogilvy
MrAlMackay wrote in message
...
How do I actually use this? Should this be part of a macro, or
through
VBA?
How do I know if it is an ActiveX listbox type?
Thanks for your help on this.
Al.
|