Hi, I don't know VBA but I recorded and copied three macros into code and
they are activated by three ActiveX Option Buttons. The buttons allow the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to deactive
the code so they can no longer click on an Option Button and thus loose
their data.

How is the best way to do this? Hopefully there is a simple way to have the
Option Buttons gray out and lose focus so they can't be clicked anymore. It
should be all or nothing, all three are active or all three are inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Hi Harold,

Try something like:

'=============
Private Sub CheckBox1_Click()
'Yourcode
CheckBox1.Enabled = False
End Sub
'<<=============

---
Regards,
Norman



"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code and
they are activated by three ActiveX Option Buttons. The buttons allow the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to deactive
the code so they can no longer click on an Option Button and thus loose
their data.

How is the best way to do this? Hopefully there is a simple way to have
the Option Buttons gray out and lose focus so they can't be clicked
anymore. It should be all or nothing, all three are active or all three
are inactive, never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Thanks Norman,

I'm a beginer with VBA and don't understand your example:
CheckBox1 - what does this represent?
Yourcode - I presume this means the macro I copied into Code.

Does this mean that after my code runs, the last line will now be
*CheckBox1.Enabled = False*?

I'd like my trigger to be one of two methods:
1. A fourth Option Button called "Lock" which, when run, would deactive the
other three Option Buttons, or
2. When text is typed into a certain cell, it would deactivate the three
Option Buttons.

Thanks if you can help me see how to fit your code into this scenario.

Harold

====================
Hi Harold,

Try something like:

'=============
Private Sub CheckBox1_Click()
'Yourcode
CheckBox1.Enabled = False
End Sub
'<<=============

---
Regards,
Norman



"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code and
they are activated by three ActiveX Option Buttons. The buttons allow the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to
deactive the code so they can no longer click on an Option Button and
thus loose their data.

How is the best way to do this? Hopefully there is a simple way to have
the Option Buttons gray out and lose focus so they can't be clicked
anymore. It should be all or nothing, all three are active or all three
are inactive, never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code and
they are activated by three ActiveX Option Buttons. The buttons allow the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to deactive
the code so they can no longer click on an Option Button and thus loose
their data.

How is the best way to do this? Hopefully there is a simple way to have
the
Option Buttons gray out and lose focus so they can't be clicked anymore.
It
should be all or nothing, all three are active or all three are inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Thanks Tom for this. Since I'm a beginner at VBA, I'm not sure how to tie my
OptionButtons to your code.

Do I just enter the names of my buttons in place of your "OptionButton"?

Do I do anything with the &i, or just type it as you have it?

I guess I need to enter these as three separate codes or whatever they are
called, just as my three macros are now three separate codes, is that
correct?

Thanks,

Harold


"Tom Ogilvy" wrote in message
...
Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code and
they are activated by three ActiveX Option Buttons. The buttons allow the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to
deactive
the code so they can no longer click on an Option Button and thus loose
their data.

How is the best way to do this? Hopefully there is a simple way to have
the
Option Buttons gray out and lose focus so they can't be clicked anymore.
It
should be all or nothing, all three are active or all three are inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

My code represents the click event for three option buttons name
OptionButton1, Optionbutton2, Optionbutton3. You would substitute Your
names for the names I have used. If your names are not made up of a base
name with a sequential number on the end, then you would not be able to use
the & i approach I used. You would have to remove the loop and expicitly
enter the names of the buttons

assume the option buttons are named Moe, Larry and Curly

so you now must have code like

Private Sub Moe_click()
' code to format the sheet
End sub

Private Sub Larry_Click()
' code to format the sheet
End Sub

Private Sub Curly_Click()
' code to format the sheet
End Sub

This would then become


Private Sub Moe_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub Larry_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub Curly_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Hopefully this will lead you to the path of enlightenment.

--
Regards,
Tom Ogilvy



"Harold Good" wrote in message
...
Thanks Tom for this. Since I'm a beginner at VBA, I'm not sure how to tie
my
OptionButtons to your code.

Do I just enter the names of my buttons in place of your "OptionButton"?

Do I do anything with the &i, or just type it as you have it?

I guess I need to enter these as three separate codes or whatever they are
called, just as my three macros are now three separate codes, is that
correct?

Thanks,

Harold


"Tom Ogilvy" wrote in message
...
Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code
and
they are activated by three ActiveX Option Buttons. The buttons allow
the
user to chose the type of project for which they will be entering data.

But once they choose the type, I'd like to use a trigger event to
deactive
the code so they can no longer click on an Option Button and thus loose
their data.

How is the best way to do this? Hopefully there is a simple way to have
the
Option Buttons gray out and lose focus so they can't be clicked
anymore.
It
should be all or nothing, all three are active or all three are
inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Thanks Tom, this did the trick!

Is there any easy way to trigger this deactivation (instead of by running
our code) by entering text into a specific cell, and then if that text is
deleted, the three OptionButtons would be reactivated?

Either that or I could insert another OptionButton called Lock, so that if
the Lock button is clicked, it would deactivate the other three
OptionButtons, and unclicking the Lock would reactivate the three Option
buttons?

Thanks so much for your good help!

Harold

====================
"Tom Ogilvy" wrote in message
...
My code represents the click event for three option buttons name
OptionButton1, Optionbutton2, Optionbutton3. You would substitute Your
names for the names I have used. If your names are not made up of a base
name with a sequential number on the end, then you would not be able to
use
the & i approach I used. You would have to remove the loop and
expicitly
enter the names of the buttons

assume the option buttons are named Moe, Larry and Curly

so you now must have code like

Private Sub Moe_click()
' code to format the sheet
End sub

Private Sub Larry_Click()
' code to format the sheet
End Sub

Private Sub Curly_Click()
' code to format the sheet
End Sub

This would then become


Private Sub Moe_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub Larry_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub Curly_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Hopefully this will lead you to the path of enlightenment.

--
Regards,
Tom Ogilvy



"Harold Good" wrote in message
...
Thanks Tom for this. Since I'm a beginner at VBA, I'm not sure how to tie
my
OptionButtons to your code.

Do I just enter the names of my buttons in place of your "OptionButton"?

Do I do anything with the &i, or just type it as you have it?

I guess I need to enter these as three separate codes or whatever they
are
called, just as my three macros are now three separate codes, is that
correct?

Thanks,

Harold


"Tom Ogilvy" wrote in message
...
Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into code
and
they are activated by three ActiveX Option Buttons. The buttons allow
the
user to chose the type of project for which they will be entering
data.

But once they choose the type, I'd like to use a trigger event to
deactive
the code so they can no longer click on an Option Button and thus
loose
their data.

How is the best way to do this? Hopefully there is a simple way to
have
the
Option Buttons gray out and lose focus so they can't be clicked
anymore.
It
should be all or nothing, all three are active or all three are
inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

If you want to trigger a macro based on the values entered or removed from a
cell, you would use the worksheet_Change event. Just use similar code

right click on the sheet tab and select view code. Assume we will use cell
B9 located on the same sheet as the option buttons. If any text is found in
B9, then enable the buttons, else disable them. (obiously by changing the
test to Target.Value < "" you can change the sense of the logic)

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count 1 then exit sub
if Target.Address = "$B$9" then
if Target.Value = "" then
me.Moe.Enabled = False
me.Larry.Enabled = False
me.Curly.Enabled = False
else
me.Moe.Enabled = True
me.Larry.Enabled = True
me.Curly.Enabled = True
end if
End if
End Sub

--
Regards,
Tom Ogilvy


"Harold Good" wrote in message
...
Thanks Tom, this did the trick!

Is there any easy way to trigger this deactivation (instead of by running
our code) by entering text into a specific cell, and then if that text is
deleted, the three OptionButtons would be reactivated?

Either that or I could insert another OptionButton called Lock, so that if
the Lock button is clicked, it would deactivate the other three
OptionButtons, and unclicking the Lock would reactivate the three Option
buttons?

Thanks so much for your good help!

Harold

====================
"Tom Ogilvy" wrote in message
...
My code represents the click event for three option buttons name
OptionButton1, Optionbutton2, Optionbutton3. You would substitute Your
names for the names I have used. If your names are not made up of a
base
name with a sequential number on the end, then you would not be able to
use
the & i approach I used. You would have to remove the loop and
expicitly
enter the names of the buttons

assume the option buttons are named Moe, Larry and Curly

so you now must have code like

Private Sub Moe_click()
' code to format the sheet
End sub

Private Sub Larry_Click()
' code to format the sheet
End Sub

Private Sub Curly_Click()
' code to format the sheet
End Sub

This would then become


Private Sub Moe_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub Larry_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub Curly_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Hopefully this will lead you to the path of enlightenment.

--
Regards,
Tom Ogilvy



"Harold Good" wrote in message
...
Thanks Tom for this. Since I'm a beginner at VBA, I'm not sure how to
tie
my
OptionButtons to your code.

Do I just enter the names of my buttons in place of your
"OptionButton"?

Do I do anything with the &i, or just type it as you have it?

I guess I need to enter these as three separate codes or whatever they
are
called, just as my three macros are now three separate codes, is that
correct?

Thanks,

Harold


"Tom Ogilvy" wrote in message
...
Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into
code
and
they are activated by three ActiveX Option Buttons. The buttons
allow
the
user to chose the type of project for which they will be entering
data.

But once they choose the type, I'd like to use a trigger event to
deactive
the code so they can no longer click on an Option Button and thus
loose
their data.

How is the best way to do this? Hopefully there is a simple way to
have
the
Option Buttons gray out and lose focus so they can't be clicked
anymore.
It
should be all or nothing, all three are active or all three are
inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold

Hi Tom,

I can't get this to work. I assume I leave my previous three codes for each
Option Button as they were in three Private SubProcedures, and that I'm
supposed to put your code into a new, fourth SubProcedure called "Private
Sub Worksheet_Change(ByVal Target As Range)". I have done this and the
little dropdown windows at the top of the VBA editor show "Worksheet" and
"Change", so that part seems to be correct.

But whether or not I have text in Q23, nothing changes regarding the Option
Buttons being Enabled. I have copied below what I presently have in the
fourth SubProcedure. I don't know how to step through this with F8 as I can
my other code, perhaps that because it's a Change event, instead of Click.

Thanks again for any suggestions you have as to what I'm doing wrong.
Harold

Private Sub Worksheet_Change(ByVal Target As Range)
'
If Target.Count 1 Then Exit Sub
If Target.Address = "q23" Then
If Target.Value < "" Then
Me.NT.Enabled = False
Me.OT.Enabled = False
Me.Custom.Enabled = False
Else
Me.NT.Enabled = True
Me.OT.Enabled = True
Me.Custom.Enabled = True
End If
End If

End Sub


"Tom Ogilvy" wrote in message
...
If you want to trigger a macro based on the values entered or removed from
a
cell, you would use the worksheet_Change event. Just use similar code

right click on the sheet tab and select view code. Assume we will use
cell
B9 located on the same sheet as the option buttons. If any text is found
in
B9, then enable the buttons, else disable them. (obiously by changing the
test to Target.Value < "" you can change the sense of the logic)

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count 1 then exit sub
if Target.Address = "$B$9" then
if Target.Value = "" then
me.Moe.Enabled = False
me.Larry.Enabled = False
me.Curly.Enabled = False
else
me.Moe.Enabled = True
me.Larry.Enabled = True
me.Curly.Enabled = True
end if
End if
End Sub

--
Regards,
Tom Ogilvy


"Harold Good" wrote in message
...
Thanks Tom, this did the trick!

Is there any easy way to trigger this deactivation (instead of by running
our code) by entering text into a specific cell, and then if that text is
deleted, the three OptionButtons would be reactivated?

Either that or I could insert another OptionButton called Lock, so that
if
the Lock button is clicked, it would deactivate the other three
OptionButtons, and unclicking the Lock would reactivate the three Option
buttons?

Thanks so much for your good help!

Harold

====================
"Tom Ogilvy" wrote in message
...
My code represents the click event for three option buttons name
OptionButton1, Optionbutton2, Optionbutton3. You would substitute Your
names for the names I have used. If your names are not made up of a
base
name with a sequential number on the end, then you would not be able to
use
the & i approach I used. You would have to remove the loop and
expicitly
enter the names of the buttons

assume the option buttons are named Moe, Larry and Curly

so you now must have code like

Private Sub Moe_click()
' code to format the sheet
End sub

Private Sub Larry_Click()
' code to format the sheet
End Sub

Private Sub Curly_Click()
' code to format the sheet
End Sub

This would then become


Private Sub Moe_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub Larry_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub Curly_Click()
Me.Moe.Enabled = False
Me.Larry.Enabled = False
Me.Curly.Enabled = False
ActiveCell.Activate
' code to format sheet
End Sub

Hopefully this will lead you to the path of enlightenment.

--
Regards,
Tom Ogilvy



"Harold Good" wrote in message
...
Thanks Tom for this. Since I'm a beginner at VBA, I'm not sure how to
tie
my
OptionButtons to your code.

Do I just enter the names of my buttons in place of your
"OptionButton"?

Do I do anything with the &i, or just type it as you have it?

I guess I need to enter these as three separate codes or whatever they
are
called, just as my three macros are now three separate codes, is that
correct?

Thanks,

Harold


"Tom Ogilvy" wrote in message
...
Private bBlockEvents As Boolean


Private Sub OptionButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub


Private Sub OptionButton2_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

Private Sub OptionButton3_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
' .Object.Value = False
.Object.Enabled = False
End With
Next
bBlockEvents = False
ActiveCell.Activate
' code to format sheet
End Sub

' temporary code you can use for testing to reenable the buttons:

Private Sub CommandButton1_Click()
bBlockEvents = True
For i = 1 To 3
With ActiveSheet.OLEObjects("OptionButton" & i)
.Object.Value = False
.Object.Enabled = True
End With
Next
bBlockEvents = False
End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi, I don't know VBA but I recorded and copied three macros into
code
and
they are activated by three ActiveX Option Buttons. The buttons
allow
the
user to chose the type of project for which they will be entering
data.

But once they choose the type, I'd like to use a trigger event to
deactive
the code so they can no longer click on an Option Button and thus
loose
their data.

How is the best way to do this? Hopefully there is a simple way to
have
the
Option Buttons gray out and lose focus so they can't be clicked
anymore.
It
should be all or nothing, all three are active or all three are
inactive,
never a combination of some on and some off.

Can someone point me in the right direction?

If it requires a bit of code, could you please help me with that?

Many thanks,

Harold