Button option and a search bar

mynguyen129

New Member
Joined
Sep 5, 2021
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I'm new to VBA. So I have no idea how to work my button. I followed instructions from How To Create A Filtering Search Box For Your Excel Data — TheSpreadsheetGuru. I also changed the cell reference. I am currently getting an error msg. Not sure what to do with this.

This is what I copied:
Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value
'SOURCE: www.TheSpreadsheetGuru.com

Dim myButton As OptionButton
Dim SearchString As String
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("B15:R89") '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

'Determine if user is searching for number or text
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If

'Loop Through Option Buttons
For Each myButton In sht.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:=SearchString, _
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


Buttons.xlsx
ABCDEFGHIJKLMNOP
2Search
3
4
5
6
7
8
9
10
11
12
13
145FacultyClassEXAMSurnameGiven NameTime TakenTime Taken (in secs)ScoreExam completionTest Duration (in mins)Attended AtStatusComments
152-SepEAL/DEAL2EALDNguyenTruong1 hr 29 mins 50 secs53900Submit902021-09-02 00:00:06 UTC#N/A
162-SepEAL/DEAL4EALDAsliwaRami Gorgees Asliwa1 hr 27 mins 2 secs52220Submit902021-09-02 00:00:07 UTC#N/A
172-SepEAL/DEAL3EALDTruongThi Hien Trang1 hr 28 mins 3 secs52830Submit902021-09-02 00:00:07 UTC#N/A
182-SepEAL/DEAL4EALDKashiRita1 hr 30 mins54000Submit902021-09-02 00:00:08 UTC#N/A
192-SepEAL/DEAL3EALDDanyliukVictoria1 hr 29 mins 15 secs53550Submit902021-09-02 00:00:08 UTC#N/A
202-SepEAL/DEAL2EALDNguyenHoang Quan1 hr 29 mins 59 secs53990Submit902021-09-02 00:00:09 UTC#N/A
212-SepEAL/DEAL1EALDVuViet Anh1 hr 30 mins54000Submit902021-09-02 00:00:09 UTC#N/A
222-SepEAL/DEAL2EALDTruongLinh Dan1 hr 30 mins54000Submit902021-09-02 00:00:09 UTC#N/A
232-SepEAL/DEAL3EALDSannaDelan Dhiaa Shamoon1 hr 30 mins54000Submit902021-09-02 00:00:10 UTC#N/A
242-SepEAL/DEAL2EALDLuuTuan Anh1 hr 17 mins 2 secs46220Submit902021-09-02 00:00:10 UTC#N/A
252-SepEAL/DEAL4EALDAlsamaniRafael Ghanim Gorgees1 hr 28 mins 8 secs52880Submit902021-09-02 00:00:10 UTC#N/A
262-SepEAL/DEAL3EALDMatiLeelyan Silewa Mati1 hr 28 mins 40 secs53200Submit902021-09-02 00:00:11 UTC#N/A
272-SepEAL/DEAL2EALDSayasengKanthanouxay1 hr 22 mins 40 secs49600Submit902021-09-02 00:00:11 UTC#N/A
282-SepEAL/DEAL4EALDXueGuanru1 hr 30 mins54000Submit902021-09-02 00:00:11 UTC#N/A
292-SepEAL/DEAL4EALDLowGavin Yong Hui50 mins 33 secs30330Submit902021-09-02 00:00:11 UTC#N/A
302-SepEAL/DEAL3EALDNguyenNgoc Quynh Anh1 hr 21 mins 29 secs48890Submit902021-09-02 00:00:13 UTC#N/A
312-SepEAL/DEAL3EALDIskandarMalak Bassel1 hr 29 mins 58 secs53980Submit902021-09-02 00:00:15 UTC#N/A
322-SepEAL/DEAL3EALDNguyenDuc Thien Phuong1 hr 23 mins 32 secs50120Submit902021-09-02 00:00:16 UTC#N/A
Dashboard
 

Attachments

  • Screen Shot 2021-09-06 at 7.03.51 am.png
    Screen Shot 2021-09-06 at 7.03.51 am.png
    166.3 KB · Views: 14

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Welcome to board mynguyen129 !

Your Code is working fine just change the set range as shown in picture. If there are still pop message then attach that message.
Set DataRange = sht.Range("B15:R89") 'Cell Range

Change range B14

Set DataRange = sht.Range("B14:R89") 'Cell Range
 

Attachments

  • Screenshot_1.jpg
    Screenshot_1.jpg
    50.5 KB · Views: 9
  • Screenshot_2.jpg
    Screenshot_2.jpg
    74.8 KB · Views: 9
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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