Home > database >  Attempt to add option button to spreadsheet in VBA succeeds in one-line With statement, throws error
Attempt to add option button to spreadsheet in VBA succeeds in one-line With statement, throws error

Time:10-14

I am trying to put together the 'Hello, World' of a macro button that will make an option/radio button on a cell in an Excel spreadsheet, the simplest possible snippet that will do the job, so I can better understand OptionButtons.Add and later build on it.

The following VBA code throws the error "Compile error: Expected: =":

Sub BrokenOptionButtonTest()
Dim rang As Range
Set rang = Cells(Selection.Row, Selection.Column)
ActiveSheet.OptionButtons.Add(rang.Left, rang.Top, rang.Width, rang.Height)
End Sub

The following VBA code does not:

Sub PuzzlingOptionButtonTest()
Dim rang As Range
Set rang = Cells(Selection.Row, Selection.Column)
With ActiveSheet.OptionButtons.Add(rang.Left, rang.Top, rang.Width, rang.Height)
End With
End Sub

What in buggery is going on here? It doesn't seem to have much to do with the typically envisioned use case for With statements.

CodePudding user response:

Drop the parentheses from the first one, since you're not using the return value from Buttons.Add

In the second block you're using the return value as the subject of the With block, so no problem there.

See VBA: Usage of parentheses for a method for example

Sub OptionButtonTest()
    With Selection.Cells(1)
        .Parent.OptionButtons.Add .Left, .Top, .Width, .Height
    End With
End Sub
  • Related