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

[sprintcar]

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

 

[IRstuff]

> 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

 

[handleman]

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.

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)

 

[FrancisL]

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!

 

[sprintcar]

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