disable check box based on other checkbox

deb

Active Member
Joined
Feb 1, 2003
Messages
392
Access 365
Within my main form f_pac,
I have 3 check boxes (USA, Canada, Combined)

I need to have them enabled/disabled.

If either USA or Canada is checked then disable Combined.
and
if Combined is checked disable USA and Canada.

How can I do this?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
Simplest would be to use a radio button group - then only one can be "on" as a rule.

--(assuming that when you say either USA or Canada you don't mean that both USA and Canada can be checked .. which is obviously what Combined is).

So just google about MSAccess radio buttons.

Otherwise I guess you start digging into the checkbox to write code on their events (such as a check event or click event, if they have one, or otherwise on an update event). The job would be to craft the IF statements to handle all the cases - each checkbox, with two possibilities of checked or unchecked, so six cases to write for). But, anyway, this is what radio buttons are for so I'd go with the radio button approach.
 
Last edited:

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
I suggest the radio buttons in a group as well, but here's the code if you can't use radio buttons for some reason

Code:
Private Sub Chk_Both_Click()
    Me.chk_usa.Value = False
    Me.Chk_Canada.Value = False
End Sub

Private Sub Chk_Canada_Click()
    Me.Chk_Both.Value = False
    Me.chk_usa.Value = False
End Sub

Private Sub chk_usa_Click()
    Me.Chk_Both.Value = False
    Me.Chk_Canada.Value = False
End Sub
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
707
+1 for radio button as well.
Post was about enable/disable?, though clearing out the values as well would be a good idea?
Look at Me.ControlName.Enabled = True and Me.ControlName.Enabled = False

I suggest the radio buttons in a group as well, but here's the code if you can't use radio buttons for some reason

Code:
Private Sub Chk_Both_Click()
    Me.chk_usa.Value = False
    Me.Chk_Canada.Value = False
End Sub

Private Sub Chk_Canada_Click()
    Me.Chk_Both.Value = False
    Me.chk_usa.Value = False
End Sub

Private Sub chk_usa_Click()
    Me.Chk_Both.Value = False
    Me.Chk_Canada.Value = False
End Sub
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
Look at Me.ControlName.Enabled = True and Me.ControlName.Enabled = False
If you use the Enabled, then how would you enable once you've set something to enabled = false?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
I know I should just test this myself but will the click work both when the checkboxes are checked (on) and unchecked (off). So we'd probably want to use the click event and also check that the box being "clicked" is checked (on). Otherwise, assume we are unchecking and do nothing.

Note that kinda agree that disable/enable is not really going to work well here - end result would be that once a choice is made it cannot be changed which assumes no mistakes ever (ah, if only...). Also still gotta say it ... radio buttons!
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
707
If you use the Enabled, then how would you enable once you've set something to enabled = false?
By the change in state of whatever drives the condition to enable/disable ?
If combined is checked, disable USA & Canada
If combined is unchecked enable USA & Canada
If either of USA or Canada are checked, disable Combined
If both USA & Canada are unchecked enable Combined.

Clearing out the values as you suggested might help in not getting into a deadly embrace.?

Radio buttons would be still the best way?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
By the change in state of whatever drives the condition to enable/disable ?
well, strictly speaking the problem is that what's driving the condition can include things that were disabled (so can longer drive anything).

i.e,
... if you click Combined and that disables USA and Canada, you're stuck.
...if you click USA and that disables Combined, you're stuck (you can still change to Canada but not Combined)
...if you click Canada and that disables Combined, you're stuck (you can still change to USA but not Combined)

The problem is not that it doesn't work but that it isn't very friendly if someone checks the wrong box by mistake.

Still gotta go with radio buttons lol.


Note: You *could* have *two* checkboxes and just allow them to both be checked for combined (so no need of a special combined checkbox). That would be more appropriate for checkboxes. Checkboxes are for multiple selection (zero, one or more of a list of choices), while radio buttons are for single selection (only zero or one of a list of choices)
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
707
Yes, not user friendly at all I agree, but my thoughts were, you untick and that enables again.
I think we all agree, radio buttons are the way to go, but it is up to the o/p at the end of the day.?
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
Yes, if a selection should preclude others, radio buttons are the way to go.
 

Forum statistics

Threads
1,084,748
Messages
5,379,613
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top