Assign a macro to a button via VBA code

[AverageCanadian]

Edit: Sorry, wrong thread, can somebody move to programming please?

Hello,

Looking for a little help here. Basically what I need to do is assign a macro to a button I create in VBA code. Ultimately what is happening in my program is:

I need a simple way for our program assistant to generate certain data in a fixed way so she can do a mail merge later on. The data she needs is stored in a SQL database. What I want to do is pull a list of SessionID's for her in column A, in column B create a Button that she can click and when clicked with create the excel document that she can use in her mail merge.

I have the SQL code done(for both sections, just showing part 1 now), now I just need to get my button to work. So far this is what I have.

Sub btnS()
     MsgBox ("Test")
End Sub

Sub getData()
    Dim conn As Variant
    Dim rs As Variant
    Dim cs As String
    Dim query As String
    Dim row As Integer
    Dim btn As Button
    Dim t As Range
    
    Sheet1.Cells.Clear
    
    Set conn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    
    'The database in this instance has been set as Northwind.
    'you will need to update the database for what yours is called.
    'The IP Address '127.0.0.1' represents localhost.  If you
    'are trying to connect to a remote sql server then you will
    'either need to enter the ip address or URL of that server.
    'In the connection string below, 1433 is the port number
    'the SQL server is listening on.  If your sql server is
    'listening on a different port you'll have to change it.
    '1433 is the default port for SQL Server.
    
    
    cs = "DRIVER=SQL Server;"
    cs = cs & "DATABASE=***;"
    cs = cs & "SERVER=***"
    
    'parameters here are connectionSring, username, password
    'you will need to put the actual username and password in
    'quotes here for this code to work.
    conn.Open cs, "***", "***"
        
    query = "SELECT SessionNumber FROM cerSession WHERE RowStatus = 'A' ORDER BY SessionNumber DESC"
    rs.Open query, conn
    
   
    row = 0
    Do Until rs.EOF
        row = row + 1
        Cells(row, 1).Value = rs.Fields("SessionNumber").Value
        Set t = ActiveSheet.Range(Cells(row, 2), Cells(row, 2))
        Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
        With btn
            .Caption = "Generate"
            .Name = rs.Fields("SessionNumber").Value
            .OnAction = "btnS"
        End With
        rs.movenext
    Loop

     
    'If rs.State = adStateOpen Then
        rs.Close
    '    Set rs = Nothing
    'End If
    
    conn.Close
    Set conn = Nothing
    
    btnS

End Sub

The above code populates column A with the SessionID and column B with a button with the title 'Generate' however when I click the button I get this following error:

"Cannot run the macro 'Book2.xlsm!btnS'. The macro may not be available in this workbook or all macros may be disabled."

even though the btnS call at the end of my code works. Perhaps I am looking at this all wrong but any help would be greatly appreciated. I've spent a few hours scouring Google but nothing has helped so far.

[abousetta]

... Deleted Previous Post...

A stripped down version of your code works.

Sub getData()
Dim btn As Object
        Set btn = ActiveSheet.Buttons.Add(Range("A1").Left, Range("A1").Top, Range("A1").Width, Range("A1").Height)
        With btn
            .Caption = "Generate"
            .OnAction = "btnS"
        End With
End Sub

Sub btnS()
     MsgBox ("Test")
End Sub

So will have to study the problem closer because it isn't with creating the button or calling it.

[AverageCanadian]

... Deleted Previous Post...

A stripped down version of your code works.

Sub getData()
Dim btn As Object
        Set btn = ActiveSheet.Buttons.Add(Range("A1").Left, Range("A1").Top, Range("A1").Width, Range("A1").Height)
        With btn
            .Caption = "Generate"
            .OnAction = "btnS"
        End With
End Sub

Sub btnS()
     MsgBox ("Test")
End Sub

So will have to study the problem closer because it isn't with creating the button or calling it.

Thanks.

Also interesting, the above code works on your computer? I just ran it on a new workbook on my computer and I get the same error, so it might not be code related but computer related?

However if I let the VBA code generate the button, and then right click on the button and assign the macro ThisWorkbook.btnS it works.

[AverageCanadian]

Thanks for the help and pointing me in the correct direction. I figured it out.

I changed
.OnAction = "btnS"
to
.OnAction = "ThisWorkbook.btnS"

and all is good now. Now lets see what can stump me next