Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Additional Help Please

    Hi Rory!!!


    thank you for this info! I want to make a macro on sheet 1 that will be able to search for a text / number code located at Column A Row 1 across 50 sheets (sheet 2 to 51). I want to be transferred to the sheet that has the text / number code that i searched for. Say for example i typed "AAAA11" which is on sheet 3 Column A Row 1, I want to be automatically transferred from sheet 1 to sheet 3. Also, I want to add an "exit" or "return" or "back" macro button on all sheets (sheet 2 to 51) for me to return to sheet 1 after. There are no repetitions on the text / number codes that i will be searching for..


    THANK YOU SO MUCH!!!

  2. #2
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,763
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Alahaye,

    Here is a sample workbook with 3 sheets of data. If you click on the Search button, a search form appears where you can begin entering a search keyword. The entire book will be searched as you type and the results will be filled in the list box. The more you type, the more filtered the list will become. The code will adapt to any number of sheets or rows. The search keyword is case insensitive meaning that a search keyword of "Te" will find words containing: "TE", "Te", "tE", or "te". In this example, I have entered "Te" as the search keyword and the results are listed below it.

    Note: location is Sheet1 (homepage)
    alyhaye1.png

    When you have found the match you want, you can click on it from within the list box and you will be taken directly to the sheet and the cell.

    Note: location is Sheet2 (location of selected search result)
    alyhaye2.png

    When finished, click on the Return button to return to the home screen. The form is not modal meaning that you can leave the form showing on the screen and continue to work on your sheets or you can close it if you like.

    If you would like to use it and need help adapting to your project, let me know.

    Maud

    Code:
    Private Sub CommandButton1_Click()
    'CLOSE FORM
    SearchForm.Hide
    End Sub
    
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    'FILTERS VALUESET SHEET ON KEY UPSTROKE IN TEXTBOX
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim ws As Worksheet
    Dim I As Long, ROW As Long
    ROW = 0
    Vset.Clear
    '----------------------------
    'CYCLE THROUGH EACH SHEET
    For Each ws In ThisWorkbook.Worksheets
        With ws
        LastRow = .Cells(Rows.Count, 1).End(xlUp).ROW
    '----------------------------
    'SEARCH EACH ROW FOR KEYWORD AND ADD TO LIST BOX
        For I = 1 To LastRow
            If InStr(1, .Cells(I, 1), TextBox1.Value, 1) Then
                Vset.AddItem (.Cells(I, 1).Value)
                Vset.List(ROW, 1) = ws.Name
                Vset.List(ROW, 2) = "row " & I
                ROW = ROW + 1
            End If
        Next I
        End With
    Next ws
    End Sub
    
    Private Sub CommandButton2_Click()
    'RETURN TO HOME SCREEN
    Worksheets(1).Activate
    [a1].Select
    Vset.Clear
    TextBox1.Value = ""
    TextBox1.SetFocus
    End Sub
    
    Private Sub UserForm_Activate()
    SearchForm.Left = 472
    SearchForm.Top = 310
    TextBox1.Value = ""
    Vset.Clear
    TextBox1.SetFocus
    End Sub
    
    Private Sub UserForm_Initialize()
    SearchForm.Left = 472
    SearchForm.Top = 310
    TextBox1.Value = ""
    Vset.Clear
    End Sub
    
    Private Sub Vset_Click()
    'JUMP TO SELECTED SHEET AND CELL
    num = Vset.ListIndex
    sht = Vset.List(num, 1)
    ROW = Right(Vset.List(num, 2), Len(Vset.List(num, 2)) - 4)
    Worksheets(sht).Activate
    Range("A" & ROW).Select
    End Sub
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,992
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi alyhaye

    ..are there any hidden worksheets????
    ..are all worksheets visible???

    zeddy

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    7,220
    Thanks
    256
    Thanked 1,009 Times in 922 Posts
    alyhaye,

    Here's a simple solution using key sequences to get the job done.
    Code:
    Option Explicit
    
    Sub FindSheet()
    
    '*** Assigned to key sequence: Ctrl+Shift+F
    
       Dim zFindVal As String
       Dim sht      As Worksheet
       
       zFindVal = UCase(InputBox("Enter Search Value"))
       
       If zFindVal <> vbNullString Then
       
         For Each sht In ActiveWorkbook.Worksheets
         
            If UCase(sht.Range("A1").Value) = zFindVal Then
              sht.Activate
              [A1].Select
              Exit For
            End If
            
         Next sht
       
       End If
       
    End Sub   'FindSheet
    
    Sub GoHome()
    
    '*** Assigned to key sequence: Ctrl+Shift+G
    
       Sheets("Sheet1").Activate
       [A1].Select
       
    End Sub  'GoHome
    Test File: alyhaye.xlsm

    Of course you could assign the macros to buttons if you wish but 50+ buttons argh!

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,763
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Zeddy,

    I think I know where you are going with this.

    Code:
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = True Then
            With ws
            LastRow = .Cells(Rows.Count, 1).End(xlUp).ROW
    '----------------------------
    'SEARCH EACH ROW FOR KEYWORD AND ADD TO LIST BOX
            For I = 1 To LastRow
                If InStr(1, .Cells(I, 1), TextBox1.Value, 1) Then
                    Vset.AddItem (.Cells(I, 1).Value)
                    Vset.List(ROW, 1) = ws.Name
                    Vset.List(ROW, 2) = "row " & I
                    ROW = ROW + 1
                End If
            Next I
            End With
        End If
    Next ws

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,992
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi Maud

    Aha! Sort of.
    After carefully reading the first post, what we have is ~50 sheets, each with a unique 'ITEMCODE' in cell [A1] on each of the sheets.

    So rather than using a search method, I thought it would be simpler to just have these unique ITEMCODES in a dropdown list, on the startup sheet.

    So, in my attached version, the dropdown list is created automatically whenever the file is loaded (with macros enabled). (This allows the list to be updated if new sheets are added)
    I have used columns [AA], [AB] and [AC] on the startup sheet. These could be any available unused columns to suit the user's own startup sheet. These 'navigation' columns could also be hidden.

    To return to the startup sheet, just double-click in cell [A1] on ANY sheet.

    This dropdown-selection method allows sheet navigation to existing sheets only.

    zeddy
    Attached Files Attached Files

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,992
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi RG

    ..a very nice solution!

    ..I would add a line..
    sht.Visible = True

    before..
    sht.Activate

    ..just to be on the safe side.

    zeddy

  8. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-05-25)

  9. #8
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,763
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Well, I kinda mis-read the question again! I thought the search was for a value in the entire column A on all of the sheets not just the header. Well maybe it can be used as a solution for another problem.

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,992
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi Maud

    I will certainly use it.
    Nice code.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •