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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows
Try using the Range.Find method of locating the textbox value in column AA
Something along the lines of

VBA Code:
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
    End If
End With
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
How about
VBA Code:
Private Sub TextBox1_AfterUpdate()

If IsError(Application.Match(Me.TextBox1.Value), ThisWorkbook.Worksheets("Master").Range("A:A") Then
    On Error Resume Next
    ThisWorkbook.Worksheets(Me.TextBox1.Value).Select
    On Error Goto 0
End If

End Sub
 

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48

ADVERTISEMENT

thanks to you too Mike but this doesn't work either.
VBA Code:
Private Sub TextBox1_AfterUpdate()

If IsError(Application.Match(Me.TextBox1.Value), ThisWorkbook.Worksheets("Main").Range("AA:AA")) Then
    On Error Resume Next
    ThisWorkbook.Worksheets(Me.TextBox1.Value).Select
    On Error Goto 0
End If

End Sub
 

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48

ADVERTISEMENT

Sorry, the scenario is this:
I am referring to the vba editor.
On the 'Main' sheet I have a Sub which adds the name of a new created sheet.
In the 'Orders' form a Combobox retrieves a name and enters the data in various textboxes.
The value of TextBox1 is the user's name (example: John Doe), this value is assigned to the name of the new sheet.

So we will have in the 'Customers' sheet
1-Mario Pop
2-Jane Doe
3-Mike Colt

Simultaneously in Sheet 'Main' Range AA: AA the list of sheets with the same names:
1-Mario Pop
2-Jane Doe
3-Mike Colt

So if TextBox1.Value (Text) is equal to a name present in Main! AA: AA you have to select the corresponding sheet.

I also tried to associate the code to a button but the problem remains, there must be a comparison between the value of the txtbox (which is equal to a value on the 'Customers' sheet)
and column AA of the Main Sheet.
I hope I was clear, thanks
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows
So if TextBox1.Value (Text) is equal to a name present in Main! AA: AA you have to select the corresponding sheet
setting up a test file as you describe,
is that not what my suggestion does ?
 

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
Thanks NoSparks for the file. I am attaching my file. The 'Orders' form is the one concerned. When you select a name from combobox, the txtboxes are populated. The 'Insert User Sheet' button creates a new sheet
File
 

Watch MrExcel Video

Forum statistics

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