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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top