Initializing a radio button on worksheet open event

[allan]

I have two radio buttons created on Sheet1 of a 3-sheet workbook. I
have named the buttons opMemo and opNoMemo in the Properties of each.
When clicked, the buttons change the text of several cells on Sheet1
via _Click events. My problem is this: I want the opNoM button to be
checked when the workbook is opened.

I have this code under ThisWorkbook:

Private Sub Workbook_open()
Call opNoM
End Sub

The procedure opNoM is in a Module in the Project and looks like this:

Public Sub opNoM()
.... change the text in various cells on Sheet1
End Sub

This successfully sets up the sheet for the NoMemo mode, but if the
opMemo button was checked the last time the workbook was saved, it is
still checked when the workbook is opened. How do I ensure that the
opNoMemo button is checked when the workbook is opened?

Any help is greatly appreciated.
AllanK

 

[Ron de Bruin]

Hi Allan

You can set the value of the control

ControlName.Value = True

 

[Jon Peltier]

Or change the value of the linked cell.

- Jon

 

[allan]

If I do

Public Sub opNoM()
opNoMemo.Value = True
.. do more stuff
End Sub

I get a "Compile Error, Variable not defined" even though the Radio
Button Name has been set to opNoMemo in its Properties

I know I'm missing something basic here, please bear with me...

 

[allan]

OK, this is what I did to get around the problem. The selection of
Memo vs. NoMemo is only done once, when the workbook is opened, so,
instead of embedding the two radio buttons on Sheet1, I created a
UserForm with the two radio buttons and an OK button on it. The
Workbook_Open event loads the form, and then this code does what I
wanted:

Private Sub Workbook_Open()
Call opNoM 'This does the various cell
text changes to default to NoMemo
With fmSelect
.opNoMemo.Value = True
.Show
End With
unload fmSelect
End Sub

I guess my previous problem was the fact that the two buttons were not
on a form, per se, just on the worksheet itself.

Cheers,
Allan

 

[Jon Peltier]

Throw away the userform. If your radio buttons are Forms toolbar option
buttons, use this:

activeworkbook.worksheets(1).optionbuttons("opNoM").value = true

If they are Controls Toolbox option buttons, use this:

activeworkbook.worksheets(1).opNoM.value = true

- Jon