0

I try to add a button to a newly created workbook and set the text on the button.

When I debug the code (F8) it works fine. But when I run the code it seems the workbook does not open before the code is at the button code and it returns runtime error 438.

This is the code:

' more code is above here...
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet


Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Inläsningsfil")
Set wbO = Workbooks.Add

With wbO
    Set wsO = wbO.Sheets("Blad1")
    wbI.Activate
    LR = Sheets("Inläsningsfil").Cells(Sheets("Inläsningsfil").Rows.Count, "A").End(xlUp).Row
    wsI.Range("A1:H" & LR).copy

    wsO.Range("A1").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
wsO.Range("A1").AutoFilter
wsO.Columns("A:I").EntireColumn.AutoFit

' here is the issue
wsO.Buttons.Add(975, 0, 229, 30).Select ' works
DoEvents 
Selection.Characters.text = "Plocka fram tomma Utl.yta" ' no error here but it's not carried out
Selection.OnAction = ThisWorkbook.Name & "!FilterUtlYta" ' here it errors on 438, but if I debug then press F8 it works fine
With Selection.Characters(Start:=1, Length:=25).Font
    .Name = "Arial"
    .FontStyle = "Fet"
    .Size = 16
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
End With
With Selection
    .Placement = xlFreeFloating
    .PrintObject = False
End With

I know selection is not a good way but even if I give the shape a name and access it using the name it does not work.

selection.name = "btn"
wsO.Shapes.Range(Array("btn")).Characters.text = "Plocka fram tomma Utl.yta"

Still creates the error, but letting the workbook open then pressing F8 resolves the issue.
Why do I get this issue on this workbook and what can I do to solve it? I use the same code in a different workbook and there it works fine.

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • `Buttons.Add` *returns* the added button, so there's no need for selecting. Eg. see this post: https://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data – Tim Williams Jul 20 '21 at 06:23
  • I see.. I did not know you could make the button a VBA object like that. – Andreas Jul 20 '21 at 08:07

0 Answers0