Assigning a Macro to a Shape in another Macro

[tommyboy2340]

Hello, I am relatively new to coding in VBA. I am trying to make a code where it looks at a list of company names (That is dynamic), makes a shape (button) for every value in the list, assigns a name and a macro to each shape based on the list. I am able to get the shapes and the names on the shapes, however, the macros are not assigning to each shape. Each shape is supposed to filter a different list based on whatever the name of the company is (the name on the original list=shape name=filter name). I have attached my code, if anyone has any tips I would appreciate it!

Option Explicit

Public Sub ListContractors()

Dim Contractor As String, ContrID As String, ListName As String
Dim ContrName As String
Dim ContrCol As Long, ContrRow As Long, LastContrRow As Long
Dim ListRow As Long, ListCol As Long
Dim LastResultRow As Long, ResultRow As Long
Dim shp As Shape, i As Long

    Sheet1.Select
    LastContrRow = 999
    ListRow = 2
'Create list of Contractors
  Sheet3.Select
  ListName = "Georlia"
  LastContrRow = 999
  ListRow = 2
    For ContrRow = 2 To LastContrRow
    Contractor = Sheet3.Range("C" & ContrRow).Value
    If Contractor = "zzzz" Then
        GoTo NoContractors:
    ElseIf Contractor = "" Then
        GoTo NoContractors:
    ElseIf Contractor = ListName Then
        ContrRow = ContrRow
    ElseIf Contractor <> ListName Then Range("C" & ContrRow).Select
                Set shp = Sheet1.Shapes.AddShape(msoShapeRoundedRectangle, 275, 300 - i, 300, 25)
                i = i - 30
                ListName = Sheet3.Range("C" & ListRow).Value
                ListRow = ListRow + 1
            
                With shp
                    .TextFrame.Characters.Text = ListName
                    .TextFrame.Characters.Font.Bold = True
                    .Fill.Visible = msoTrue
                    .Fill.Transparency = 0#
                    .Line.Visible = msoFalse
                    .OnAction = "Macro"
                    Sheet3.Select
                End With
     End If

NoContractors:  Application.CutCopyMode = False
'Cont:   Worksheets("Table").Range("$AB$1:$AF$999").AutoFilter Field:=5, Criteria1:=Contractor
     Next ContrRow

    
End Sub

Public Sub Macro(Optional ByVal ListName As String)

Call ListContractors

ActiveWorkbook.Worksheets("Table").Range("$AB$1:$AF$999").AutoFilter Field:=5, Criteria2:=ListName
End Sub

 

[My Aswer Is This]

I'm never in favor of having a lot of buttons on a sheet.
You do know you can run a script by double clicking on a cell.
So lets say in Range("A1") you double click on Range("A1") and Range("A1") has the value "Alpha" in it then a small script could be written one time that would say Sheets(Taget.value).activate

Just curious what do your button do?

 

[jolivanes]

As "My Answer Is This" alluded to, explain in a concise manner what you want to achieve.
It is very much appreciated that at least you have most of your problem covered.
However, it looks like it can be simplified and the "selecting" should be changed.
Selecting sheets, cells or ranges is very seldom required and it slows down the execution time of the macro.

By the looks of, you could have a listbox on a userform that will be populated with all your contractor names.
Once when you click on the name of the contractor, it will run the code for that particular contactor.
You could have as many names as contractors as you wish and just one button.

 

[My Aswer Is This]

My script was just a example. Not suggesting sheets need to be activated to perform a script.

It could be more like sheets(Target.value).Range("A1").value="Me"
If you double clicked on the value "David" This would enter data in sheet named "David"

 

[jolivanes]

@ My Answer Is This
All we can do for now is speculating and, as you mentioned, give examples.

 

[tommyboy2340]

I'm never in favor of having a lot of buttons on a sheet.
You do know you can run a script by double clicking on a cell.
So lets say in Range("A1") you double click on Range("A1") and Range("A1") has the value "Alpha" in it then a small script could be written one time that would say Sheets(Taget.value).activate

Just curious what do your button do?

Every day a list of people will be imported to this workbook. On any given day a new Company (Contractor) could be added, which is creating the biggest headache. My idea is that there is 1 button per contractor and it is supposed to adjust to the list depending on how many companies are added. The button that is assigned to the specific company will then be used to filter a list by the companies name. This document will be used as a time sheet at our check in point so this filter will make it easier to find the worker, rather than looking through a list of 100+ people. I am open to any suggestions if buttons are not the smartest way to go about it.

 

[My Aswer Is This]

It surely sounds like you do not need a lot of buttons.
So give some specific information about how your work is laid out.
Like give me the specific details I will need. Like in what column is the company name in.
Say like column A or B not column name.

If you were going to use buttons. Show me the script you were going to use in the button.
We always need specific details like specific column where you data is and specific sheet names

 

[tommyboy2340]

The company names are in Sheet("Table") Column C.
Sheet("Home") is where these buttons need to be created.
The specific script that would run on the buttons would be:

ActiveWorkbook.Worksheets("Table").Range("$AB$1:$AF$999").AutoFilter Field:=5,_
Criteria1:=ListName
'ListName would be the name of the company

This will Filter the table by the company name.
I have included an image of the "Table Sheet"

It surely sounds like you do not need a lot of buttons.
So give some specific information about how your work is laid out.
Like give me the specific details I will need. Like in what column is the company name in.
Say like column A or B not column name.

If you were going to use buttons. Show me the script you were going to use in the button.
We always need specific details like specific column where you data is and specific sheet names

 

[My Aswer Is This]

My suggestion is you double click on the company name in column C
and the script will run. This is called a Auto sheet event script which requires no buttons.
When you double click on a cell in column C which has a company name the script runs
Would something like this be something you would like to try.
If you double click on "Alpha" then the script knows the company name is "Alpha"

 

[My Aswer Is This]

If the script is the same for all company names.
And you want to use buttons.
Why not have the script in the button ask you what is the sheet name.

Or you can use a Combobox with all the sheet name and when you click on "Alpha"
The script would know the company name

No need to have 20 buttons if all the scripts are the same except for company names

If you want something like this I can write a script to do that.