Hi all: I have a workbook with 75 worksheets. I have one set up for each
truck in our fleet. I use it to record details and conversations with
the drivers. I have a script that someone on here sent me that date and
time stamps each new entry. What I would like to do is have a main page
with a cell that I would input the truck number which would in turn "go
to" the proper work sheet for that truck. I can edit the worksheet name
as the truck #, and go to it by clicking the tab, but this means a lot
of scrolling to get to the appropriate worksheet. I now have a main
page set up with 75 macro buttons with a "go to" macro to each sheet
but it requires a lot of maintenance as trucks come and go and the
numbers change on a frequent basis. Is there a way that I can have an
input cell where I can input the truck # and go directly to the proper
worksheet.

Thanks Kevin


--
kevint
------------------------------------------------------------------------
kevint's Profile: http://www.excelforum.com/member.php...o&userid=28293
View this thread: http://www.excelforum.com/showthread...hreadid=478658

Hi Kevin

One way is to use a change event on your main sheet eg:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Address = "$A$1" Then
On Error Resume Next
Sheets(Target.Value).Activate
On Error GoTo 0
End If
End Sub

Every time you change the value of cell A1 (change this as required) the
appropriate sheet will be activated.

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Hope this helps
Rowan

kevint wrote:
Hi all: I have a workbook with 75 worksheets. I have one set up for each
truck in our fleet. I use it to record details and conversations with
the drivers. I have a script that someone on here sent me that date and
time stamps each new entry. What I would like to do is have a main page
with a cell that I would input the truck number which would in turn "go
to" the proper work sheet for that truck. I can edit the worksheet name
as the truck #, and go to it by clicking the tab, but this means a lot
of scrolling to get to the appropriate worksheet. I now have a main
page set up with 75 macro buttons with a "go to" macro to each sheet
but it requires a lot of maintenance as trucks come and go and the
numbers change on a frequent basis. Is there a way that I can have an
input cell where I can input the truck # and go directly to the proper
worksheet.

Thanks Kevin

Hi Kevint,

Copy the following macro and function into a standard module in your
workbook:

'====================
Sub SheetFinder()
Dim strInput As String

strInput = InputBox("Enter Truck #")

If StrPtr(strInput) = 0 Then
MsgBox "You pressed Cancel"
Else
If Len(strInput) = 0 Then
MsgBox "OK was pressed but no entry was made."
Else
If WorksheetExists(strInput) Then
Sheets(strInput).Activate
Else
MsgBox "Sheet " & strInput & " not found!"
End If
End If
End If
End Sub

'---------------------

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function
'<<====================

Now:
Views | Toolbars | Customize
Select Commands tab
Select 'Macros' in the 'Categories' listbox
From the right-hand 'Commands' box, drag the smiley button and drop it onto
a toolbar
Right-Click the new toolbar button | 'Assign Macro'
Select or type 'SheetFinder'
Ok | Close

You can now navigate to and from any sheet by clicking the toolbar button.

---
Regards,
Norman


"kevint" wrote in
message ...

Hi all: I have a workbook with 75 worksheets. I have one set up for each
truck in our fleet. I use it to record details and conversations with
the drivers. I have a script that someone on here sent me that date and
time stamps each new entry. What I would like to do is have a main page
with a cell that I would input the truck number which would in turn "go
to" the proper work sheet for that truck. I can edit the worksheet name
as the truck #, and go to it by clicking the tab, but this means a lot
of scrolling to get to the appropriate worksheet. I now have a main
page set up with 75 macro buttons with a "go to" macro to each sheet
but it requires a lot of maintenance as trucks come and go and the
numbers change on a frequent basis. Is there a way that I can have an
input cell where I can input the truck # and go directly to the proper
worksheet.

Thanks Kevin


--
kevint
------------------------------------------------------------------------
kevint's Profile:
http://www.excelforum.com/member.php...o&userid=28293
View this thread: http://www.excelforum.com/showthread...hreadid=478658

Norman,
An interesting sub, thanks. Is it possible to have something like a
"droplist of sheetnames" appear on screen for selection instead when Smiley
is clicked ? Currently, I have to enter the sheetname in the inputbox.
Perhaps functionally something equiv. to a floating TOC, I guess. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

Try:

'============
Sub ShowSheetList()
'Jim Rech
On Error Resume Next
If ActiveWorkbook.Sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub

'<<============

---
Regards,
Norman



"Max" wrote in message
...
Norman,
An interesting sub, thanks. Is it possible to have something like a
"droplist of sheetnames" appear on screen for selection instead when
Smiley
is clicked ? Currently, I have to enter the sheetname in the inputbox.
Perhaps functionally something equiv. to a floating TOC, I guess. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

For a different approach, using a dialog sheet, see the following which was
posted by Bob Phillips:

'==============
Sub BrowseSheets()
'Bob Phillips
Const nPerColumn As Long = 38 '# of items/column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption
Dim cLeft As Long
Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, _
cLetters * nWidth, 16.5
.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) _
* nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete

End With

End Sub
'<<==============
---
Regards,
Norman



"Max" wrote in message
...
Norman,
An interesting sub, thanks. Is it possible to have something like a
"droplist of sheetnames" appear on screen for selection instead when
Smiley
is clicked ? Currently, I have to enter the sheetname in the inputbox.
Perhaps functionally something equiv. to a floating TOC, I guess. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks, Norman !
The sub by Jim Rech runs great
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks, Norman !

Bob's BrowseSheets sub runs marvellous, too.

But I did notice that the selection dialog: Select sheet to goto
seems a shade too large ("oversized" - nearly fills the entire screen <g)
whenever there's a lot of sheets

Is there a way that the dialog could be sized just "right" ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Is there a way that the dialog could be sized just "right" ?

A further clarification/request:
The selection buttons/sheetnames appear listed down in one "column" on the
dialog. If there's lots of sheets involved, the dialog would extend
below/beyond the screenview. Could this be refined to either have the
selections auto-wrapped into multiple cols (if necess) so that all
selections stay within screenview, or something to that effect ? Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

Bob's BrowseSheets sub runs marvellous, too.

Indeed.

Is there a way that the dialog could be sized just "right" ?

If you care to wait about 4 hours, Bob will doubtless respond in person.


At least in the interim, does Jim's procdedure not satify your needs?

---
Regards,
Norman



"Max" wrote in message
...
Thanks, Norman !

Bob's BrowseSheets sub runs marvellous, too.

But I did notice that the selection dialog: Select sheet to goto
seems a shade too large ("oversized" - nearly fills the entire screen <g)
whenever there's a lot of sheets

Is there a way that the dialog could be sized just "right" ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Norman. No problem. I'll hang around for further insights ..
I'm spoilt for choice, and I want it all (if it's possible) <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

I am not quite sure what you mean by sizing just right, could you clarify?

As to the second point, it already caters for that. If you look in the code,
there is a constant named nPerColumn which is defaulted at 38. This can be
changed to force a multi-column display.

Regards

Bob

"Max" wrote in message
...
Is there a way that the dialog could be sized just "right" ?

A further clarification/request:
The selection buttons/sheetnames appear listed down in one "column" on the
dialog. If there's lots of sheets involved, the dialog would extend
below/beyond the screenview. Could this be refined to either have the
selections auto-wrapped into multiple cols (if necess) so that all
selections stay within screenview, or something to that effect ? Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks for the clarifications, Bob !
(In my haste to play, I din't look closely in the code earlier, sorry)

.. by sizing just right ..
... within one screen view, that is. Some of my earlier obs
were that the dialog seems to fill the entire screen

I've since played around with the settings a bit,
and think it's ok now. Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
Hi Max,

I am not quite sure what you mean by sizing just right, could you clarify?

As to the second point, it already caters for that. If you look in the
code,
there is a constant named nPerColumn which is defaulted at 38. This can be
changed to force a multi-column display.

Regards

Bob

Excellent!

I set those up as constants that could be adjusted as I struggled to come up
with a method that would be consistent across various monitors, various
resolutions.

Bob


"Max" wrote in message
...
Thanks for the clarifications, Bob !
(In my haste to play, I din't look closely in the code earlier, sorry)

.. by sizing just right ..
.. within one screen view, that is. Some of my earlier obs
were that the dialog seems to fill the entire screen

I've since played around with the settings a bit,
and think it's ok now. Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
Hi Max,

I am not quite sure what you mean by sizing just right, could you
clarify?

As to the second point, it already caters for that. If you look in the
code,
there is a constant named nPerColumn which is defaulted at 38. This can
be
changed to force a multi-column display.

Regards

Bob

Hi Max,

If you care to wait about 4 hours, Bob will doubtless respond in person.

Re-reading my response, I realise that my comment is open to another,
malevolent, and totally unintended interpretation.

I an sure that you will have appreciated that the comment related to time
zones and I hope that Bob, who is one of the most assiduous contributors to
this NG, understood that my comment was entirely innocent in intention.


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Max,

Bob's BrowseSheets sub runs marvellous, too.

Indeed.

Is there a way that the dialog could be sized just "right" ?

If you care to wait about 4 hours, Bob will doubtless respond in person.


At least in the interim, does Jim's procdedure not satify your needs?

---
Regards,
Norman



"Max" wrote in message
...
Thanks, Norman !

Bob's BrowseSheets sub runs marvellous, too.

But I did notice that the selection dialog: Select sheet to goto
seems a shade too large ("oversized" - nearly fills the entire screen
<g)
whenever there's a lot of sheets

Is there a way that the dialog could be sized just "right" ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Excellent!
That aptly describes it, Bob <g !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Not at all, Norman ! Certainly none of that sort of interp over here,
and I believe that position holds true for Bob as well.

.. Bob, who is one of the most assiduous contributors to this NG
Of course, I/we know that <bg Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Lighten up Norman, no-one in their right minds would have read it as that
<evbg. As it happens, I wouldn't have picked up in this thread as it
started two days ago and I wasn't tracking it. I only picked up as Max
flagged it elsewhere.

BTW, I thought you were in St Albans, seeing your posting times makes me
think that I am way
off-track, or you are an insomniac.

Regards

Bob


"Norman Jones" wrote in message
...
Hi Max,

If you care to wait about 4 hours, Bob will doubtless respond in person.

Re-reading my response, I realise that my comment is open to another,
malevolent, and totally unintended interpretation.

I an sure that you will have appreciated that the comment related to time
zones and I hope that Bob, who is one of the most assiduous contributors
to
this NG, understood that my comment was entirely innocent in intention.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Max,

Bob's BrowseSheets sub runs marvellous, too.

Indeed.

Is there a way that the dialog could be sized just "right" ?

If you care to wait about 4 hours, Bob will doubtless respond in person.


At least in the interim, does Jim's procdedure not satify your needs?

---
Regards,
Norman



"Max" wrote in message
...
Thanks, Norman !

Bob's BrowseSheets sub runs marvellous, too.

But I did notice that the selection dialog: Select sheet to goto
seems a shade too large ("oversized" - nearly fills the entire screen
<g)
whenever there's a lot of sheets

Is there a way that the dialog could be sized just "right" ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Bob,

Lighten up Norman, no-one in their right minds would have read it
as that

It was the others that I was worried about!!

BTW, I thought you were in St Albans

You were right, that is my home city.

seeing your posting times makes me think that I am way
off-track, or you are an insomniac.

No, just recent pressure of work!

---
Regards,
Norman



"Bob Phillips" wrote in message
...
Lighten up Norman, no-one in their right minds would have read it as that
<evbg. As it happens, I wouldn't have picked up in this thread as it
started two days ago and I wasn't tracking it. I only picked up as Max
flagged it elsewhere.

BTW, I thought you were in St Albans, seeing your posting times makes me
think that I am way
off-track, or you are an insomniac.

Regards

Bob


"Norman Jones" wrote in message
...
Hi Max,

If you care to wait about 4 hours, Bob will doubtless respond in
person.

Re-reading my response, I realise that my comment is open to another,
malevolent, and totally unintended interpretation.

I an sure that you will have appreciated that the comment related to time
zones and I hope that Bob, who is one of the most assiduous contributors
to
this NG, understood that my comment was entirely innocent in intention.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Max,

Bob's BrowseSheets sub runs marvellous, too.

Indeed.

Is there a way that the dialog could be sized just "right" ?

If you care to wait about 4 hours, Bob will doubtless respond in
person.


At least in the interim, does Jim's procdedure not satify your needs?

---
Regards,
Norman



"Max" wrote in message
...
Thanks, Norman !

Bob's BrowseSheets sub runs marvellous, too.

But I did notice that the selection dialog: Select sheet to goto
seems a shade too large ("oversized" - nearly fills the entire screen
<g)
whenever there's a lot of sheets

Is there a way that the dialog could be sized just "right" ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--