TxtBox value displays sheet

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
Hi everyone
I would like that when a value appears in the TextBox1, that value must be compared with the column "AA" of the 'Main' sheet, and if the value is present select the sheet with the name corresponding to the searched value.
I tried but can't, please help / advice, thanks in advance.

VBA Code:
Private Sub TextBox1_AfterUpdate()
Dim ws As Worksheet

Dim wk_master As Workbook
Dim ws_master As Worksheet

Set wk_master = ActiveWorkbook
Set ws_master = wk_master.Worksheets("Main")

If Me.TextBox1.Value = ws_master.Cells(1, 26).Value Then
Set ws = ThisWorkbook.Worksheets(Me.TextBox1.Value)
ws.Select

End If
End Sub
 

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
Sorry Nolan the code seemed strange to me, in fact he tells me "Sub or Function not defined" (Private cannot be called) I put the segment of the 'txtbox1_afterupdate' in a Module such as Sub but it always gives the same error
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows
Code for the form and its contents must be in the form's code module.

You are getting the "Sub or Function not defined" because you don't have a TextBox1_AfterUpdate sub.

try adding this:
VBA Code:
Private Sub TextBox1_AfterUpdate()
    Dim ws As Worksheet
    Dim wk_master As Workbook
    Dim ws_master As Worksheet
    Dim fndRng As Range

Set wk_master = ActiveWorkbook
Set ws_master = wk_master.Worksheets("Main")

If Me.TextBox1.Value <> "" Then
    With ws_master.Range("AA:AA")
        Set fndRng = .Find(What:=Me.TextBox1.Value, _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False)
        If Not fndRng Is Nothing Then
            Set ws = ThisWorkbook.Worksheets(Me.TextBox1.Value)
            ws.Select
        Else
            MsgBox Me.TextBox1.Value & " is not in column AA"
        End If
    End With
End If
End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows
I'm not sure that it's perfect as I didn't thoroughly test things, but glad to have helped you over that hurdle.
Good luck with the rest of your project.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top