[SOLVED] Create button with code, to run working code

Create button with code, to run working code

This code works.

Sub test()
    Dim Rng1 As Range, Rng2 As Range
    Dim ws1 As Worksheet, WS2 As Worksheet, i As Long
    Set ws1 = ThisWorkbook.Worksheets("Test Summary")
    Set Rng1 = ws1.Range("E2")
    Set WS2 = ThisWorkbook.Worksheets("Examples")
    Set Rng2 = WS2.Range("F2")
    
    ' Clear original data on Summary
    i = 0
    Do Until IsEmpty(Rng1.Offset(i, 0))
        Rng1.Offset(i, 0).EntireRow.ClearContents
        i = i + 1
    Loop
    i = 0
    
    Do Until IsEmpty(Rng2.Offset(0, i))
       If Rng2.Value <> "" Then
            Rng1.Offset(0, 0).Formula = "='" & WS2.Name & "'!" & Rng2.Cells([7], [1]).Address 'Project
            Rng1.Offset(0, 1).Formula = "='" & WS2.Name & "'!" & Rng2.Cells([9], [1]).Address 'Model
            Rng1.Offset(0, 2).Formula = "='" & WS2.Name & "'!" & Rng2.Cells([10], [1]).Address 'ID`
            Set Rng2 = Rng2.Offset(0, i + 1)
        End If
        Set Rng1 = Rng1.Offset(1, 0) ' Move to next row to check
    Loop
   i = 0
    ' Clean up
    Set Rng1 = Nothing
    Set ws1 = Nothing
    Set Rng2 = Nothing
    Set WS2 = Nothing
End Sub

When I try to programmatically add a button to trigger the macro, it shows:

run-time error '13', type mismatch

This is the code for creating the button and assigning the macro.

Sub CommandButton_Click()
    Dim b As Button
    Dim ws10 As Worksheet
    Set ws10 = ThisWorkbook.Worksheets("Test Summary")
    Set b = ws10.Buttons.Add(200, 5, 80, 18.75)       ' left, top, width, height
    b.OnAction = "test" ' a sub name in module
    b.Characters.Text = "Refresh"  ' text on the button
End Sub

The code for macro and button is in different module.

The button is a one-time thing.

I tried adding the button manually and assigning the macro.
The same error occurred on:

Rng1.Offset(0, 0).Formula = "='" & WS2.Name & "'!" & Rng2.Cells([7], [1]).Address 'Project

Solution

There are a couple of issues with your current Sub CommandButton_Click()

ws10.Buttons.Add(200, 5, 80, 18.75) - why do you create a new button each time you click on it?
b.OnAction = - this is C# code, not VBA

This simple code should be enough:

Sub CommandButton_Click()
    Call test
End Sub