How to create a button, using a subroutine, that calls another subroutine to which it also passes variablesin VBA

Question

I wish to use a piece of code to create a series of buttons. Each button then calls a different subroutine called "loadboat". I need to pass variables to "loadboat" that tell it what to do.

The "loadboat" subroutine is already written and the code to create the buttons is already written (see below). I know how to set the onclick code of the buttons to a subroutine, but not how to pass variable to that subroutine. The subroutine displayed below creates a series of validation lists with buttons under each one. each button needs to call a subroutine and pass both the model name (this is displayed directly above the button) and the verion number (this is selected from the validation list directly above the button).

The validation selection needs to be made after the button has been created, which means that the arguments that are passed to "loadboat" are not defined on creation of the button.

j = 1

For i = 5 To B.UsedRange.Columns.Count 'iterate through all the models'    
    If B.Cells(2, i).Value <> 0 Then 'case where there are versions of that model in build'        
        A.Cells(5, 2 * j).Value = B.Cells(1, i).Value 'print name of the model'        
        k = B.Cells(2, i).Value - B.Cells(3, i).Value + 1 'set k = to the total number of that model ever to be in build - the number of that model completed before the start of this year'

        ReDim Ar1(k) As String 'set Ar1 to be a size such that it can contain all the models versions I wish to display'

        For l = 0 To k - 1 'iterate through all of the model versions and add them to an array'                
            If B.Cells(3, i).Value + l < 10 Then 'version number is less than 10'                
                Ar1(l) = ("00" & CStr(B.Cells(3, i).Value + l))                    
            ElseIf l > 9 And l < 100 Then 'version number is between 10 and 100                
                Ar1(l) = ("0" & CStr(B.Cells(3, i).Value + l))                    
            Else 'version number is greater than 100'                
                Ar1(l) = CStr(B.Cells(3, i).Value + l)                    
            End If

            m = m + 1                
        Next l

        With A.Cells(7, 2 * j).Validation 'selecting the cell to place the listbox in and creating it'
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Ar1, ",") 'adding the array containing the the version numbers'
            .InCellDropdown = True
            .InputTitle = A.Cells(7, 2 * j).Value                    
        End With

        Set Ran1 = A.Range(A.Cells(9, 2 * j), A.Cells(9, 2 * j)) 'picking the cell to create the button in'            
        Set Btn = A.Buttons.Add(Ran1.Left, Ran1.Top, Ran1.Width, Ran1.Height) 'creating the button'            
        With Btn                
            .OnAction = "loadboat"
            .Caption = "Edit"
            .Name = "Btn" & j            
        End With

        j = j + 1        
    End If        
Next i

I have no idea how to pass the subroutine that the button runs variables

Sounds like you need a user defined function or sub with arguments http://www.cpearson.com/excel/writingfunctionsinvba.aspx — SJR, Jan 22 '19 at 15:43
Yes I agree, my problem is how to pass that function the variables — Robert Morris, Jan 22 '19 at 15:44
What specifically is the problem? That link, among many others, should give pointers. — SJR, Jan 22 '19 at 15:45
Why don't you just read the cells that you need to directly from your code? (I'm presuming that the values you want to pass are in one of your sheets? — Zac, Jan 22 '19 at 15:45
not at this point, the variables are the model name and the version number they choose from the dropdown after this code has run — Robert Morris, Jan 22 '19 at 15:50
Are the dropdowns in one of your sheets? If so, same applies. Your alternate is to have a UDF that takes the values as input and then on `button_ckick` method, call the UDF with whatever values you want.. @SJR comments should help with this approach — Zac, Jan 22 '19 at 15:53
Can't you just assign all the buttons to call a wrapper `Sub` that uses `Application.Caller` to detect the `j` in the `"btn" & j` name... from there can you grab the model name & verson number from the respective cells in column related to `j` and then pass those values into the desired routine — Kubie, Jan 22 '19 at 16:03
The dropdowns are also created in the above code, the: With A.Cells(7, 2 * j).Validation bit. — Robert Morris, Jan 22 '19 at 16:05
@Kubie That should work, thank you, completely forgot about application.caller — Robert Morris, Jan 22 '19 at 16:06

JohnyL · Accepted Answer · 2019-01-23T10:30:35.750

2

The following code creates button and assigns procedure to be called passing a parameter. You can integrate literal parameter (AsStatic) or use a variable (AsDynamic):

Sub loadboat(param$)
    MsgBox param
End Sub

Sub AsStatic()
    With Sheet1.Range("A1:B2")
        With .Parent.Buttons.Add(.Left, .Top, .Width, .Height)
            .Caption = "GO!"
            .OnAction = "'loadboat ""hello""'"
        End With
    End With
End Sub

Sub AsDynamic()
    Const param$ = "hello"
    With Sheet1.Range("A1:B2")
        With .Parent.Buttons.Add(.Left, .Top, .Width, .Height)
            .Caption = "GO!"
            .OnAction = "'loadboat """ & param & """'"
        End With
    End With
End Sub

edited Jan 23 '19 at 10:30

answered Jan 22 '19 at 16:06

JohnyL

6,894
3
22
41

Thank you, this is exactly what I was looking for – Robert Morris Jan 23 '19 at 09:40
@RobertMorris You're welcome! Don't forget to flag it as answer! ;) – JohnyL Jan 23 '19 at 10:29

How to create a button, using a subroutine, that calls another subroutine to which it also passes variablesin VBA

1 Answers1