How to Run code on Textbox Change?

RyanTG

New Member
Joined
Nov 29, 2015
Messages
42
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Where is this textbox located and, if it's located on a sheet, which type of textbox is it - Forms or ActiveX?
 

RyanTG

New Member
Joined
Nov 29, 2015
Messages
42
It is a normal text box I inserted from "Insert" > "Text Box".

I placed it in cell B1.


Thanks for your help!!!
 
Last edited:

RyanTG

New Member
Joined
Nov 29, 2015
Messages
42
oisnf6.png
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
Private Sub TextBox1_Change()

End Sub
 

RyanTG

New Member
Joined
Nov 29, 2015
Messages
42
Thanks :)

How do I integrate that with this code:

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
Private Sub TextBox1_Change()
    Call SearchBox
End Sub
 

RyanTG

New Member
Joined
Nov 29, 2015
Messages
42
THANK YOU SO MUCH!!!!

You have no idea how helpful you just were!

It works perfect now :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

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
Top