OK...Here goes...

  • Thread starter Thread starter Josiah
  • Start date Start date
J

Josiah

I need a macro that will do the following:

If a certain checkbox is clicked, and a certain field (elsewhere on
spreadsheet) does not contain a specific designated text (in this case
the text "4C"), I need for that checkbox to not check. I would also
LIKE for it to bring up some sort of an alert (text of my choosing)...

S'this possible?
Any takers?!

(or is it clear as mud?) :D
 
Josiah,

With a checkbox on a worksheet from the Control ToolBox
and the cell being looked at is A1 for a value of "4C".

Right click on the CheckBox, select "View Code"
and copy and paste the following:

Public DoOnce As Boolean
Private Sub CheckBox1_Click()
If DoOnce = True Then Exit Sub
DoOnce = True
If Range("A1").Value <> "4C" Then
CheckBox1.Value = False
MsgBox "Please enter text"
End If
DoOnce = False
End Sub

John
 
Do you know you may do this with Data>Validation>Settings tab>Allow>Custom>?
It does not work with checkboxes, but on the worksheet.
 
Is this facetious Josiah>?

What you want is, in the macro that is assigned to the checkbox

With ActiveSheet
If Range("A1").Value = "4C" Then
.CheckBoxes("Check Box 1").Value = False
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob said:
*Is this facetious Josiah>?

What you want is, in the macro that is assigned to the checkbox

With ActiveSheet
If Range("A1").Value = "4C" Then
.CheckBoxes("Check Box 1").Value = False
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

*
Click to expand...

These don't seem to be working.

Bob -
When I type yours in, It doesn't do anything (Checkbox checks whether
appropriate field contains "4c" or not...

any other ideas?

(Thanks!)

-----

John -
I need it to work with the Check Boxes that are already existing (over
100 of them) so replacing them is not really an option (Mine are taken
from the "Forms" pallette not the "control toolbox")
 
Josiah > said:
Bob -
When I type yours in, It doesn't do anything (Checkbox checks whether
appropriate field contains "4c" or not...

any other ideas?

(Thanks!)
Click to expand...

Josiah,

It works okay for me. I have assumed that these are Forms menu checkboxes.
Couple of thoughts,
- you have assigned the checkbox to that macro?
- you checkbox is named "Check Box 1"?

You aren't using control toolbox checkboxes by any chance? If so try this
instead

Private Sub CheckBox1_Click()
If Range("A1") = "4C" Then
Application.EnableEvents = False
CheckBox1.Value = False
Application.EnableEvents = True
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob said:
Josiah,

It works okay for me. I have assumed that these are Forms menu
checkboxes.
Couple of thoughts,
- you have assigned the checkbox to that macro?
- you checkbox is named "Check Box 1"?

You aren't using control toolbox checkboxes by any chance? If so try
this
instead

Private Sub CheckBox1_Click()
If Range("A1") = "4C" Then
Application.EnableEvents = False
CheckBox1.Value = False
Application.EnableEvents = True
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct) *
Click to expand...

No, these are Forms checkboxes...however, none of my checkboxes are
named (I purposely left the text field blank).
 
Josiah > said:
No, these are Forms checkboxes...however, none of my checkboxes are
named (I purposely left the text field blank).
Click to expand...

Josiah,

You cannot stop a checkbox being named, Excel does it. What you are
referring to is the caption with the checkbox. Just right-click on one of
the checkboxes to select it, you will see the checkbox name in the name
field (top left above the spreadsheet, below the toolbars).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top