Excel VBA: Click two buttons (shapes) that run two separate VBA scripts on spreadsheet open

Question

I have a spreadsheet completely locked down and control all sorting and filtering through VBA. I also have another script that hides the sheet on close and saves the file automatically to keep that sheet hidden.

I've been trying to figure out how I can use VBA to 'click' on one button (shape) which would clear anything that's been filtered and then 'click' on another button (shape) which would sort the spreadsheet alphabetically. The buttons (shapes) already work perfectly with user-interaction but I would also like these buttons (shapes) to automatically get triggered when the sheet is opened.

The first button is assigned to macro, SearchBox, associated with the following VBA -

Sub SearchBox()

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

  Set sht = ActiveSheet

  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0

  Set DataRange = sht.ListObjects("DataTable").Range

  mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text

  If IsNumeric(mySearch) = True Then
    SearchString = "=" & mySearch
  Else
    SearchString = "=*" & mySearch & "*"
  End If

  For Each myButton In sht.OptionButtons
    If myButton.Value = 1 Then
      ButtonName = myButton.Text
      Exit For
    End If
  Next myButton

  myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)

  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:=SearchString, _
    Operator:=xlAnd

  sht.Shapes("UserSearch").TextFrame.Characters.Text = ""

End Sub

The second one is much more simple which just sorts the data table by that specific column -

Sub Sort_Name()

Dim oneRange As Range
Dim aCell As Range

Set oneRange = Range("A4:H1162")
Set aCell = Range("A4")

oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes

End Sub

Basically, I'm still learning and I feel it's possible to just trigger these buttons with a script but I've yet to figure it out. Any help would be appreciated.

Answer 1

So the best way to explain how I did what I was wanting to do is to look at what the button itself is calling to by right clicking it and clicking assign macro again. You'll see it look like this -

'YourSpreadSheetName.xlsm'!Sheet2.SearchBox

For me, what was throwing me off is I wasn't including the Sheet2 which is where the code is I'm trying to call.

By placing this code within the ThisWorkbook section with the Workbook_Open script, I was able to get it working the way I wanted it to -

Call Sheet2.SearchBox

Thanks again for the help all who commented.

Answer 2

In the code-behind for ThisWorkbook, you will be able to handle workbook events, including the Open event, which is fired by the workbook when it is opened.

Navigate to the module (double-click ThisWorkbook in the VBE's project explorer [Ctrl+R]), then locate the dropdowns at the top of the editor's code pane. From the left-hand dropdown, select Workbook; then, from the right-hand dropdown, select the Open event; the VBE automatically creates a method stub with the correct signature for you:

Private Sub Workbook_Open()

End Sub

Notice that the underscore has a very special meaning in VBA; when naming your procedures (especially implicitly or explicitly Public ones), consistently stick to PascalCase and avoid Snake_Case; this may not matter now, but as you progress as a developer you'll come to appreciate consistency in naming, and when you start working with interfaces, that underscore-in-public-members thing will start making the difference between code that works and code that doesn't even compile: taking the good habits early will save you headaches later.

Now, you need to invoke two procedures in that handler.

When you do this:

oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes

You're invoking the Sort method of the oneRange object, which is an instance of the Range class.

When you do this:

MsgBox "Hi!"

You're invoking the MsgBox function that's in the VBA library, under the Interaction module (find it in the object browser [F2]). This would be equivalent:

VBA.Interaction.MsgBox "Hi!"

So, to invoke your SearchBox and then your SortName method, all you need to do is this:

Private Sub Workbook_Open()
    SearchBox
    SortName  'formerly known as Sort_Name
End Sub

Procedures do something - their names should always start with a verb, they're actions. "SearchBox" looks like a name, not an action. In programming, names are more like classes - a Range, a Workbook, etc.; consider renaming SearchBox to better convey what it does. If that's hard to do, it's likely because your procedure does too many things - like getting the name to use for filtering, and then applying a filter to a specific table, and then clearing the text of some shape.

You'll also want to watch out for implicit ActiveSheet references; currently SortName is assuming what the ActiveSheet is, and this is very likely to cause issues down the line.