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

Hi Allan

You can set the value of the control

ControlName.Value = True


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ps.com...
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

Or change the value of the linked cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ron de Bruin" wrote in message
...
Hi Allan

You can set the value of the control

ControlName.Value = True


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message
ps.com...
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

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...


On May 2, 2:57 pm, "Jon Peltier"
wrote:
Or change the value of the linked cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Ron de Bruin" wrote in . ..

Hi Allan

You can set the value of the control

ControlName.Value = True

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

wrote in message
ups.com...
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

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

On May 2, 4:24 pm, wrote:
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...

On May 2, 2:57 pm, "Jon Peltier"
wrote:

Or change the value of the linked cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Ron de Bruin" wrote in . ..

Hi Allan

You can set the value of the control

ControlName.Value = True

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

wrote in message
ups.com...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
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

On May 2, 4:24 pm, wrote:
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...

On May 2, 2:57 pm, "Jon Peltier"
wrote:

Or change the value of the linked cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Ron de Bruin" wrote in
. ..

Hi Allan

You can set the value of the control

ControlName.Value = True

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

wrote in message
ups.com...
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