Current checkbox label update

[Eljefegeneo]

CheckBox Backcolor
I have the following module which displays the backcolor of a checkbox lable if it is true or false (written by supermoderator Galaxiom)
Private Sub FormatLabel(chk As Control)
With chk.Controls(0)
If chk Then
.BackColor = vbYellow
.BackStyle = 1
.ForeColor = vbRed
Else
.BackColor = vbWhite
.ForeColor = vbBlack
End If
End With
End Sub

And on each forms OnCurrent event I have:
Dim itm As Control
For Each itm In Me.Controls
If itm.ControlType = 106 Then FormatLabel itm
Next

Which works great! But I cannot figure out how to make it work in the control AfterUpdate Event. I tried using it in the current control's (checkbox) after update event
Dim itm As Control
If itm.ControlType = 106 Then FormatLabel itm

but nothing happens (no change, no error message).
I have tried creating a new module:
Public Sub CheckBoxFormat(chk As Control)
If chk = -1 Then
chk.BackColor = vbYellow
chk.BackStyle = 1
chk.ForeColor = vbRed
Else
chk.BackColor = vbWhite
chk.ForeColor = vbBlack
End If
End Sub

But when I try to call it in the AfterUpdate event for the particular checkbox
Private Sub CheckBoxA_AfterUpdate()
Dim itm As Control
If itm.ControlType = 106 Then
CheckBoxFormat itm
End Sub

I get the error message "Expected Variable or Procedure not module"
So, (1) is my module all wrong or (2) am I calling it incorrectly or (3) wrong on both items?

 

[Fran Lombard]

The after update event does not fire for check boxes - try putting your code in the on_click event

 

[Eljefegeneo]

That is not true.
I have been using the following code and it works just fine. However, I have many check boxes in about six forms and it makes the code difficult to maintain to change it for every checkbox. What I wanted is a module that I could call on the afterupdate event.

Private Sub CHECKBOXA_AfterUpdate()
If Me.CHECKBOXA = True Then
Me.lblCHECKBOXA.BackColor = vbYellow
Me.lblCHECKBOXA.BackStyle = 1
Me.lblCHECKBOXA.ForeColor = vbRed
Else
Me.lblCHECKBOXA.BackColor = vbWhite
Me.lblCHECKBOXA.ForeColor = vbBlack
End If

End Sub

P.S. I did try using the on click event and it gives me the same error message. "Expected Variable or Procedure not module"

 

[Eljefegeneo]

Make a sample DB to post and tested it and it works! On either AfterUpdate or on Click. Now trying to figure out why it doesn't work in my original DB. That is, with the module as described previously "FormatLabel" in my first post and the AfterUpdate (or OnClick) sub as
Dim itm As Control
For Each itm In Me.Controls
If itm.ControlType = 106 Then FormatLabel itm
Next

 

[JHB]

Did you also name the module as "CheckBoxFormat", so that you both have a module and a Sub/procedure with the same name, (if yes rename the module)?

 

[Fran Lombard]

Sorry about that misinformation. I misread the help on the check box. Upon re-reading it it indicated the event doesn't apply to checkboxes within an option group.

 

[Eljefegeneo]

OK, I now have figured out why there were a few glitches. All is OK, the AfterUpdate event calling the module works fine.
Dim Itm As Control
For Each Itm In Me.Controls
If Itm.ControlType = 106 Then FormatLabel Itm
Next

The module it is calling is:
Private Sub FormatLabel(chk As Control) as shown above.

But, it seems to me that I am making the code step through each control on the form, checking to see if it is a text box and then checking to see if it is True or False. All I think it should really do is check the current control which is a check box. That is what I cannot figure out, how to amend the above code to only check if the current check box is true or false.

 

[BlueIshDan]

Use a naming convention on your check boxes of chkName
Or continue with your type check.

      Dim msg As String
      For Each var_control In Me.Controls
            If Left(var_control).Name = "chk" Then: _
                  msg = msg & var_control.name & ": " & CStr(CBool(var_control.Value)) & vbNewLine
      Next
      MsgBox msg

 

[Eljefegeneo]

Thank you for your reply. I must have 50 or more check boxes strewn across six or seven forms. So if I renamed them chk... I would have to go through all my queries and reports to update all the fields.

And I do not understand how your code would solve the problem. I just want to update the checkbox control that has focus so that if I check/uncheck it, the module executes. I thought it should be something simple like

Private Sub After Update CheckBoxA
Call FormatLabel

But I keep getting the error message: "Argument Not Optional".
I can used the code that loops through all the controls, but I do not think this would be good practice. Surely there must be some way of calling the module or does it have to be re-written to only take into account if the code is for one particular checkbox?

 

[BlueIshDan]

Do the click event

Sorry, I misunderstood the problem

In the click event of the checkbox

If CBool(chkBox.Value) Then
... true code ...
Else: ... false code ...
End If

 

[Eljefegeneo]

I appreciate your prompt reply, but I do not understand what you are trying to tell me. Once again, I know how to to the background color of the checkbox label by VBA, that is:
If CheckBox! = True then
some code
Else
Some other code

But the code that I have to use is shown in my second post of this thread. If I have 50 different check boxes then I have to amend this code 50 times. Which, as I have read many times in the forum, is not good practice.
I was trying to use a module similar to the FormatLabel one in the first post. I can use the loop method as indicated above in my third post, but if I call the module FormatLabel, it says "argument not optional" which I assume means I cannot use the module or am calling it in an improper manner.

So, I repeat my question in the previous post, is there a way of calling the module FormatLabel for one particular checkbox without going through the loop of all controls, or must I come up with another module. And if so, how do I amend FormatLabel to accommodate this?

 

[Fran Lombard]

But when I try to call it in the AfterUpdate event for the particular checkbox

I get the error message "Expected Variable or Procedure not module"
So, (1) is my module all wrong or (2) am I calling it incorrectly or (3) wrong on both items?

Your Code

Private Sub CheckBoxA_AfterUpdate()
Dim itm As Control
If itm.ControlType = 106 Then
   CheckBoxFormat itm
End Sub

Try this - you were missing the initialization of the variable itm

Private Sub CheckBoxA_AfterUpdate()
Dim itm As Control


set itm = CheckBoxA

If itm.ControlType = 106 Then
    CheckBoxFormat itm
End If
End Sub

 

[Eljefegeneo]

Thank you. That is what I couldn't figure out but you knew. Just curious, is there way of calling the current control (the checkbox) without actually naming it? Now going to change the code in all my forms and not worry about them again! On to the next item.

 

[Fran Lombard]

You could create a "Global Event Handler" class to do what you want. I have not tried creating one of them so I can not guide you; however if you Google it you will find examples. I think there may be examples on this site as well.

Glad to hear you got your problem solved.