How to solve the Excel VBA Run-time error '13', type mismatch

Question

The macro code initially works perfectly fine until I try to add a button to trigger the macro, it kept showing run-time error '13', type mismatch. Below is my macro code which works perfectly fine until I used the code to add button to trigger the macro. The code for macro and button is in different module.

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

This is the code for creating the button and assigning it to 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

Answer 1

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