VBA Excel "Expected Function or variable" error when inserting form control button by macro

Question

I would like to create the form control button by macro, as discussed here:

Adding command buttons to worksheet at run time and also define events

and explained here:

https://www.mrexcel.com/board/threads/vba-code-to-create-macro-insert-form-control-button-assign-macro-to-button.832394/

Unfortunately, suddenly I got an error: Expected fonctuon or variable" with debugger pointing roughly the Selection statement.

  Sub SunButton()
  '
  ' Macro1 Macro
  '

   '
    ActiveSheet.Buttons.Add(964.2, 119.4, 139.2, 49.8).Select
    Selection.OnAction = "Sun"
    Selection.Characters.Text = "Sun"
    With Selection.Characters(Start:=1, Length:=3).Font
    .Name = "Calibri"
    .FontStyle = "Bold"
    .Size = 16
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 32
    .Placement = xlFreeFloating
    .PrintObject = False
    End With
   End Sub

I don't know what can be wrong here.

According to this thread:

https://www.mrexcel.com/board/threads/compile-error-expected-function-or-variable.308885/

it occurs, when you have another macro called "selection" but I don't have it in my case.

How can I remove this error and proceed my recorded macro?

Rory · Accepted Answer · 2021-02-09 13:56:18Z

1

Your code is wrong, as you are applying button properties to its Font. Try:

  Sub SunButton()
  '
  ' Macro1 Macro
  '

   '
    With ActiveSheet.Buttons.Add(964.2, 119.4, 139.2, 49.8)
    .OnAction = "Sun"
    .Caption = "Sun"
    With .Characters.Font
       .Name = "Calibri"
       .FontStyle = "Bold"
       .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 32
      End With
      .Placement = xlFreeFloating
      .PrintObject = False
    End With
   End Sub

answered Feb 9, 2021 at 13:56

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FaneDuru · Accepted Answer · 2021-02-09 13:59:49Z

1

Anyhow, it is good to avoid 'Selection' when possible...

Sub testButtonCharDif()
  Dim bt As Button
  Set bt = ActiveSheet.Buttons.Add(964.2, 119.4, 139.2, 49.8)
    With bt
        .OnAction = "Sun"
        .Characters.Text = "Sun"
        With .Characters(Start:=1, length:=3).Font
            .name = "Calibri"
            .FontStyle = "Bold"
            .size = 16
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 32
        End With
        .Placement = xlFreeFloating
        .PrintObject = False
     End With
End Sub

edited Feb 9, 2021 at 13:59

answered Feb 9, 2021 at 13:56

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

1 Comment

Geographos Over a year ago

Thank you very much. The Selection comes with a recorded macro. In my other example, it was working without the problem, but here I was surprised. However your way is good-looking for the future, where I won't use recorded macro again, but start to develop my own code in order to set the button. Thanks a lot!

Collectives™ on Stack Overflow

VBA Excel "Expected Function or variable" error when inserting form control button by macro

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related