×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

?How to GoTo from list to a worksheet with workbook?

?How to GoTo from list to a worksheet with workbook?

?How to GoTo from list to a worksheet with workbook?

(OP)
I posted this in a different forum, (Thanks IRstuff) so let's see if how you folks can help. I've been away from spreadsheet building for too long I guess.

I can't find it in my Excel Help section - I guess MS is getting cheaper with the assistance.

I've created a workbook of customers, cell A1 is the account name in all sheets. If I have a list of customers in column A of the first worksheet, how can I click on the customer I want and have it go to that particular tab or worksheet?

I'm running XP on a laptop, I think Excel is Win2000 version

Thanks in advance.  

There is a theory which states that if ever anybody discovers exactly what the Universe is for and why it is here, it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another theory which states that this has already happened. -- Douglas Adams

RE: ?How to GoTo from list to a worksheet with workbook?


>  Create a hyperlink in the cell to the sheet required.  Unfortunately, renaming the target sheet will break the link.

>  Brute force macro
Public Sub Jump()
    Target = ActiveCell.Value
    Sheets(Target).Select
End Sub

You can assign that to a button.  Select the correct cell, click the button or run the macro

 

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: ?How to GoTo from list to a worksheet with workbook?

So, the behavior you want is:

1. User clicks any cell in column A
2. Excel switches to to the worksheet that has the same value in cell A1 as the clicked cell, regardless of sheet name.

The only way to do exactly this would be to program it into the worksheet's "SelectionChange" event.  However, if you did that you would never be able to edit the data in the main sheet's Column A.  However, minimal functionality would be lost if it were programmed to the BeforeDoubleClick event.

Go into the VBA editor (Alt-F11 is quickest) and double-click on the name of the first worksheet on the left-hand tree.  It should bring up a blank window or area with two dropdowns at the top containing "(General)" and "(Declarations)".  Paste the code below into that blank area.  Then try double-clicking on a cell in Column A of your client list sheet.

CODE

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim sMsg As String
Dim FindString As String
Dim FoundSheets As New Collection
Dim i As Long

If Target.Column <> 1 Then
    Exit Sub
End If

FindString = Target.Text
If FindString = "" Then
    Exit Sub
End If

Cancel = True

For i = 1 To Worksheets.Count
    If Not (Worksheets(i) Is Application.ActiveSheet) Then
        If StrComp(FindString, Worksheets(i).Cells(1, 1).Text, vbTextCompare) = 0 Then
            FoundSheets.Add Worksheets(i)
        End If
    End If
Next i

If FoundSheets.Count = 0 Then
    MsgBox "No sheet found with """ & FindString & "."""
ElseIf FoundSheets.Count = 1 Then
    FoundSheets(1).Activate
Else
    sMsg = FoundSheets.Count & " sheets were found with """ _
    & FindString & ".""" & vbCrLf & vbCrLf
    For i = 1 To FoundSheets.Count
        sMsg = sMsg & i & ". " & FoundSheets(i).Name & vbCrLf
    Next i
    sMsg = sMsg & vbCrLf & "Please enter the number of the desired sheet:"
    On Error GoTo QUITME
    i = CLng(InputBox(sMsg, "Choose Sheet", "1"))
    FoundSheets(i).Activate
End If
Exit Sub
QUITME:
MsgBox "Invalid input.  Please try again"
End Sub

-handleman, CSWP (The new, easy test)

RE: ?How to GoTo from list to a worksheet with workbook?

If you use Search (Ctrl-F) then Options then Search within Workbook you get a list of all the sheets that contain the search term - and you can click a row to go to the sheet.
For example if all the sheets had cell (B2 say) with a formula containing "Customer" & A1 then searching for Customer would list the sheets along with the customer name. No VBA required
Of course what you are trying to do should really be done with a database!

RE: ?How to GoTo from list to a worksheet with workbook?

(OP)
Handleman - it works GREAT!!  THANKS!!

There is a theory which states that if ever anybody discovers exactly what the Universe is for and why it is here, it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another theory which states that this has already happened. -- Douglas Adams

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources