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