Checkbox event help

agrogers15

New Member
Joined
Sep 9, 2016
Messages
10
I am using the status of a checkbox to change the color of a rectangular box behind a field for quick status reference.
Code:
Private Sub Permit_Received_Click()
If Me.Permit_Received = Yes Then
Me.PermitHighlight.BackColor = vbRed
Else
Me.PermitHighlight.BackColor = vbGreen
End If
End Sub
First thing I notice that I don't understand, is the color changes opposite of how I think. I originally had the colors in the opposite spot so that when I check the box it turns green but it was opposite. I then changed the initial If statement to No but it still did the same thing. So I changed the color syntax locations and it worked, but it doesn't stay in the proper back color. When I close the form and reopen it defaults back to red. How do I keep the backcolor while the checkbox is checked?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
Try this:

Assuming you want it Green if it is checked.
Code:
Private Sub Permit_Received_Click()
If Permit_Received = True Then
Permit_Received.BackColor = vbGreen
Else
Permit_Received.BackColor = vbRed
End If
End Sub
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
Note: when you close and reopen the form anything done in that click event is over and done. The background color will be set according to the form design.

I've never used conditional formatting in Access but if you want that to happen I think Access has conditional formatting that you can use on a control without writing your own code for it.

If you do write your own code for it, it should run whenever the form is opened as well as when a new record is displayed - i.e., anytime you view a new or another record, as well as change an existing one, you would need to have this code check your condition.

I'm a little confused by your statement "If Me.Permit_Received = Yes Then". What is the Yes there? Is that a variable or constant you have defined?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
The form Current event and the click event of the checkbox would be needed for a code solution. The Current event is one of the events that will fire when the form is opened as well as if records are cycled, which should be OK for a single record form. Conditional formatting can't be used on a box/rectangle, which is what the OP is suggesting the control is. A textbox can be made to look like a rectangle and could accept CF. If this is a continuous form, there would be a whole different set of issues.
 

agrogers15

New Member
Joined
Sep 9, 2016
Messages
10
So why use a textbox? I don't want to actually enter anything in it. I have the rectangle positioned behind the permit received checkbox so that while the checkbox is left unchecked the rectangle is red and once checked needs to turn green. Which I have gotten to work when I FIRST check the box. However the issue is I can't get the box to stay green once I close the record and come back or reopen the database. I have tried this:
Code:
Private Sub Permit_Received_Click()
If Me.Permit_Received = True Then
Me.PermitHighlight.BackColor = vbGreen
Else
Me.PermitHighlight.BackColor = vbRed
End If
End Sub
________________

Private Sub Permit_Received_Current()
If Me.Permit_Received = True Then
Me.PermitHighlight.BackColor = vbGreen
Else
Me.PermitHighlight.BackColor = vbRed
End If
End Sub
This also doesn't keep the box green. I have to uncheck and recheck the box.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
I think the latter part for the on current event needs to be in the FORM current event (not the CONTROL Current Event).
 

agrogers15

New Member
Joined
Sep 9, 2016
Messages
10
Ok, I think I have found another possible reason. I need a subform linked to each record and put the rectangle on that. It now stays green no matter what record I am on.
 

agrogers15

New Member
Joined
Sep 9, 2016
Messages
10
Ok, So I created another table and form with those checkboxes and on the subform put in this code:
Code:
Option Compare Database
___________________
Private Sub Form_Current()
If Me.LEEP_Complete = True Then
Me.LEEPHighlight.BackColor = vbGreen
Else
Me.LEEPHighlight.BackColor = vbRed
End If
End Sub
___________________

Private Sub LEEP_Complete_Click()
If Me.LEEP_Complete = True Then
Me.LEEPHighlight.BackColor = vbGreen
Else
Me.LEEPHighlight.BackColor = vbRed
End If
End Sub
I'm getting closer as now it will save the color, but now even when I move to another record the "CF" stays. Any ideas how to make the code apply to each record specifically?
 

agrogers15

New Member
Joined
Sep 9, 2016
Messages
10
Ok so now I'm really close! I did end up changing the rectangle to a textbox bound to a field so it would be tied to an individual record. Thanks Micron! Thanks xenou for the FORM_Current idea! Now on my mainform which is a split for there is a + box next to each record, its not until I maximize the record that it will update the subform. Any ideas on why this is?
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
See first four words of post #4 :rolleyes:
I have no idea what "maximize" a record on a subform means. Don't understand what a + box is either. But I didn't say bind the text box, so I don't know if that's going to help or hinder.
You're using a split form? In that case, the record half can only be a data sheet AFAIK, so that ought to throw coloured rectangles and/or text boxes right out the window.
Subforms update the record when you click off of it after changing/adding data/records, but for a split form, the current event fires when a datasheet record is entered, but not when it is left and you don't select another record (as if you clicked on the "main" form or one of it's controls.

For me, you're going to have to recap and fill in the blanks and don't use phrases not pertinent to Access (if you can help it). I speculated in post 4 as to what type of form you have - I should have asked directly.
 
Last edited:

Forum statistics

Threads
1,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top