deactivate macro or LostFocus
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
|