VBA - Including a variable within the Activex Option Button naming

Question

I have an Order Sheet which includes a combination of check boxes and also option buttons.

There are 10 rows of 3 option buttons and 1 checkbox. Each row is grouped together.
The check boxes work fine and I am able to refer to them using a variable i which refers to the row number.
- I have tried to do the same for the Option buttons. I.e I am trying to refer to the option buttons using variable i. However, I have been unable to find a solution.

This is the code I am using to refer to the check-boxes using variable i. This works fine.

    If Sheets("Order Sheet").Shapes("Check Box " & i).ControlFormat.Value = 1 Then

I have used a similar logic to try and refer to the option buttons. However, all variations of my trial and error based on vaguely similar things that I have found online have failed.

If OLEObjects("OptionButton" & i + 30).Object.Value = True Then

The current error with the code snippet above is "Sub or function not defined". Highlighting the OLEObjects line of code.

Any help or direction on where to go with including a variable in the naming of the option buttons would be greatly appreciated.

Did you try If Sheets("Order Sheet").Shapes("Option Button " & i).ControlFormat.Value = 1 Then ? — z32a7ul
– z32a7ul, Commented Mar 18, 2019 at 10:25
Can you determine the exact name of the option buttons? If you click on them you should see their name next to the formula bar where you usually see the name of the active celll (e.g. A1). What is their name? — z32a7ul
– z32a7ul, Commented Mar 18, 2019 at 10:27

Pᴇʜ · Accepted Answer · 2019-03-18 10:15:34Z

2

You need to specify in which worksheet your OLEObjects is:

ThisWorkbook.Worksheets("Sheet1").OLEObjects

Also see the documentation: OLEObjects object.

answered Mar 18, 2019 at 10:15

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

1 Comment

M.Laszkowski Over a year ago

That was easy! I had the line of code in a with statement, so I was just missing a dot preceding the OLEObjects. I'll have a look at the documentation also.

Collectives™ on Stack Overflow

VBA - Including a variable within the Activex Option Button naming

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related