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
 
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
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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