How to Run code on Textbox Change?

[RyanTG]

Hi


Looking through all of the forums I have come up empty handed.

How can I run code when the value of a textbox named UserSearch changes?


My macro currently runs off a shortcut key (ctr+f).

Would you know how to write that code and where I would place it in my macro?


1. Excel 2016; Windows 10

2. PC

 

[Norie]

Where is this textbox located and, if it's located on a sheet, which type of textbox is it - Forms or ActiveX?

 

[RyanTG]

It is a normal text box I inserted from "Insert" > "Text Box".

I placed it in cell B1.


Thanks for your help!!!

 

[RyanTG]

 

[Norie]

Sorry but as far as I know there's no such thing as a Change event for a Forms textbox, which is what you have.

Perhaps you could try using an ActiveX textbox?

 

[RyanTG]

Would you know the solution if it were an ActiveX textbox?

 

[Norie]

If you insert an ActiveX textbox and then either double click it, or right click it, select View code... you should see something like this.

Private Sub TextBox1_Change()

End Sub

 

[RyanTG]

Thanks

How do I integrate that with this code:

Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text
'SOURCE: www.TheSpreadsheetGuru.com






Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant
   
'Load Sheet into A Variable
  Set sht = ActiveSheet


'Unfilter Data (if necessary)
  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0
   
'Filtered Data Range (include column heading cells)
  Set DataRange = sht.Range("b2:m27658") 'Cell Range
  'Set DataRange = sht.ListObjects("Table1").Range 'Table


'Retrieve User's Search Input
  mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
  'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
  'mySearch = sht.Range("A1").Value 'Cell Input


'Loop Through Option Buttons
  For Each myButton In ActiveSheet.OptionButtons
      If myButton.Value = 1 Then
        ButtonName = myButton.Text
        Exit For
      End If
  Next myButton
  
'Determine Filter Field
  On Error GoTo HeadingNotFound
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
  On Error GoTo 0
  
'Filter Data
  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:="=" & mySearch & "*", _
    Operator:=xlAnd
  
'Clear Search Field
  'sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
  'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
  'sht.Range("A1").Value = "" 'Cell Input


Exit Sub


'ERROR HANDLERS
HeadingNotFound:
  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
    
End Sub

 

[Norie]

Kind of sprung that one on me.

Do you want this code to be called when a change is made in the textbox?

If so you could try this.

Private Sub TextBox1_Change()
    Call SearchBox
End Sub

 

[RyanTG]

THANK YOU SO MUCH!!!!

You have no idea how helpful you just were!

It works perfect now