Vba help for beginner

[Chr15R]

Hi I’m hoping someone can help me I’m really new to vba and think I might be trying to do something either not possible or too hard for me to figure out on my own.

I have a workbook where there’s a main page worksheet and then each employee has a worksheet tailored to their profile with an id listed somewhere on their worksheet

On the main page I’ve added a text box where you can input part of the employee Id in case you don’t know the whole number and then a go button. What id like it to do if possible is you put either the whole or part of the employee Id in the text box then click the go button and it searches the other worksheets for the match and goes to that worksheet.

Is this something that can be achieved or not? I’m hoping one of you experienced people can point me in the right direction

Thanks

 

[mumps]

Perhaps I could suggest a slightly different approach. You could have a cell in the main page worksheet which contains a drop down list of all the employee worksheets. You would simply select the desired sheet from the drop down list and that sheet would be activated. If this approach would work for you, copy and paste the macros below into the worksheet code module. Do the following: right click the tab name for your "Main Page" sheet and click 'View Code'. Paste the macros into the empty code window that opens up. The macros assume that you have a sheet named "Main Page" and that the drop down list is in cell A1. Change the sheet name (in red) and the cell (in blue - two occurrences) to suit your needs. Close the code window to return to your sheet and click on cell A1 of Main Page to select a sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    Dim ws As Worksheet, sh As String
    For Each ws In Sheets
        If ws.Name <> "Main Page" Then
            If sh = "" Then sh = ws.Name Else sh = sh & "," & ws.Name
        End If
    Next ws
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=sh
    End With
    Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    Sheets(Target.Value).Activate
    Application.ScreenUpdating = False
End Sub

 

[Chr15R]

Hi

That’s a good option that I’ll consider so thanks for the help and advice.

The only issue with that is because it’s something I’m developing for the whole site, some people using the sheet may only have the id number or part of it for the person and no name and we have just over 100 people currently on the team. Is there any way to get it to search via a partial match to their id number or am I clutching at straws

Again thanks for the help so far

 

[mumps]

Is the ID number in a specific cell or can it be anywhere on the employee sheet?

 

[Chr15R]

I can put it in a specific cell on each worksheet if that’s easier

 

[mumps]

That would make it easier. Which cell would that be? You have to take into consideration that searching for a partial match can lead to not finding the desired ID. For example, if the ID is 1234, then using a partial search of 123 or 234 or 23 or 12 would return the same result.

 

[Chr15R]

The id is in a merged cell that goes from G to K and rows 9 and 10

 

[mumps]

You should avoid merging cells at all cost because they almost always cause problems for macros. You could simply widen a column if you need more space or do a little research into "CenterAcrossSelection". This has the same effect as merging without actually merging any cells. Begin by unmerging all the merged cells and place the ID in cell G9. You can place the ID in any cell you want. Just change the range (in red) to suit your needs, then try this macro:

Sub ActivateSheet()
    Application.ScreenUpdating = False
    Dim ID As Range, response As String, ws As Worksheet
    response = InputBox("Enter the ID number to serach.")
    If response = "" Then Exit Sub
    For Each ws In Sheets
        Set ID = ws.Range("G9").Find(response, LookIn:=xlValues, lookat:=xlPart)
        If Not ID Is Nothing Then
            ws.Activate
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub