ActiveX checkbox that changes color

O

Olov

New Member
Joined
Jan 17, 2024
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi,
I want to have an ActiveX checkbox in each sheet that depending on whether the value in cell A1 is empty should be hidden and otherwise it should be red and not selected. When I'm done with the sheet, I should be able to click on the checkbox and it should turn green. If the color of the tab follows the color of the button, that would be great too. Then the color could be white when cell A1 is empty. I've made code that almost works. What goes wrong is when code Show_Notes is run, the checkbox and tab color sometimes turn red and when I change a value in the sheet, the checkbox and tab change color to red.
Sincerely, Olov

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("$A$1").Value <> "" Then
Me.Tab.ColorIndex = 3 'red
CheckBox1.BackColor = &HFF& 'red
CheckBox1.Visible = True
Else
Me.Tab.ColorIndex = 2 'white
CheckBox1.Visible = False
End If
End Sub

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then Me.Tab.ColorIndex = 4 'green
If CheckBox1.Value = True Then CheckBox1.BackColor = &HFF00& 'green
If CheckBox1.Value = False Then
CheckBox1.BackColor = &HFF& 'red
If Range("$A$1").Value <> "" Then
Me.Tab.ColorIndex = 3 'red
Else
Me.Tab.ColorIndex = 2 'white
End If
End If
End Sub


Sub Show_Notes()
Dim Ws As Worksheet
Dim Cmnt As Comment
Dim Count As Long
Application.ScreenUpdating = False
Sheets("Notes").Select
Sheets("Notes").Range("B4:D23").ClearContents
Count = 0
For Each Ws In ActiveWorkbook.Worksheets
For Each Cmnt In Ws.Comments
Worksheets("Notes").Range("B3").Offset(Count, 0).Parent.Hyperlinks.Add _
Anchor:=Worksheets("Notes").Range("B4").Offset(Count, 0), _
Address:="", _
SubAddress:="'" & Ws.Name & "'!" & Cmnt.Parent.Address, _
TextToDisplay:="'" & Ws.Name & "'!" & Cmnt.Parent.Address
Worksheets("Notes").Range("C4").Offset(Count, 0).Value = Cmnt.Author
Worksheets("Notes").Range("D4").Offset(Count, 0).Value = Cmnt.Text
Count = Count + 1
Next Cmnt
Next Ws
Rows("4:23").RowHeight = 25.5
Range("A2").Select
Application.ScreenUpdating = True
End Sub
 
Sort by date Sort by votes
Hello again. I wrote wrong, this is right.
I added
If Not .Tab.Color = vbGreen Then
after
With Sh
and
End If
before
End With
Now they no longer turn red. The function that makes them red if something changes in the tab would be good to have.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Olov said:
The function that makes them red if something changes in the tab would be good to have.
Click to expand...

I'm not sure what you mean by that because so far you've said only cell A1 on each sheet affects whether the checkbox is visible and red (A1 <> "") or hidden (A1 = "").

Try this code, which puts all the logic of the checkbox colour & visible/hidden and sheet tab colour in a single routine, Update_CheckBox, which is called from Workbook_SheetCalculate and each CheckBox1_Click.

ThisWorkbook module:
VBA Code: 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Dim cb1 As OLEObject

    On Error Resume Next
    Set cb1 = Sh.OLEObjects("CheckBox1")
    On Error GoTo 0
    If Not cb1 Is Nothing Then
        Update_CheckBox Sh, cb1
    End If
    
End Sub

Sheet module of each sheet which has a CheckBox1 control:
VBA Code: 
Private Sub CheckBox1_Click()
    Update_CheckBox Me, Me.CheckBox1
End Sub

Standard module:
VBA Code: 
Public Sub Update_CheckBox(Sh As Object, cb As Object)
    With Sh
        If .Range("A1").Value <> "" Then
            'Cell A1 is not blank so set colour of checkbox and sheet tab
            If cb.Object.Value = True Then
                'Checkbox is ticked - green
                cb.Object.BackColor = vbGreen
                cb.Visible = True
                .Tab.Color = vbGreen
            Else
                'Checkbox is not ticked - red
                cb.Object.BackColor = vbRed
                cb.Visible = True
                .Tab.Color = vbRed
            End If
        Else
            'Cell A1 is blank so hide checkbox and set colour of sheet tab to white
            cb.Visible = False
            .Tab.Color = vbWhite
        End If
    End With
End Sub
 
Upvote 0
John_w said:
I'm not sure what you mean by that because so far you've said only cell A1 on each sheet affects whether the checkbox is visible and red (A1 <> "") or hidden (A1 = "").

Try this code, which puts all the logic of the checkbox colour & visible/hidden and sheet tab colour in a single routine, Update_CheckBox, which is called from Workbook_SheetCalculate and each CheckBox1_Click.

ThisWorkbook module:
VBA Code: 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Dim cb1 As OLEObject

    On Error Resume Next
    Set cb1 = Sh.OLEObjects("CheckBox1")
    On Error GoTo 0
    If Not cb1 Is Nothing Then
        Update_CheckBox Sh, cb1
    End If
   
End Sub

Sheet module of each sheet which has a CheckBox1 control:
VBA Code: 
Private Sub CheckBox1_Click()
    Update_CheckBox Me, Me.CheckBox1
End Sub

Standard module:
VBA Code: 
Public Sub Update_CheckBox(Sh As Object, cb As Object)
    With Sh
        If .Range("A1").Value <> "" Then
            'Cell A1 is not blank so set colour of checkbox and sheet tab
            If cb.Object.Value = True Then
                'Checkbox is ticked - green
                cb.Object.BackColor = vbGreen
                cb.Visible = True
                .Tab.Color = vbGreen
            Else
                'Checkbox is not ticked - red
                cb.Object.BackColor = vbRed
                cb.Visible = True
                .Tab.Color = vbRed
            End If
        Else
            'Cell A1 is blank so hide checkbox and set colour of sheet tab to white
            cb.Visible = False
            .Tab.Color = vbWhite
        End If
    End With
End Sub
Click to expand...
Thank you for the three codes. Now everything works except the last function described below.

Sorry for not explaining everything at once. I am working with a material specification that retrieves values in hidden sheets (and changes calculations) depending on what I select.

Cell A1 is controlled by what I select in different steps. If the sheet is not relevant in the project, it should be white (A1 = ""). If the sheet is in the project, cell A1 will have the same value as the sheet and the color red (A1 <> ""). When I am done with the sheet, I press CheckBox1 to make it green. If something changes in the workbook that changes the calculated values in the sheet, I want CheckBox1 and the sheet to be red.

Last function:
For example: if I change something in Sheet1 that changes the calculated values in Sheet1, Sheet3 and Sheet9, I would like CheckBox1 and the tab to be red in Sheet1, Sheet3 and Sheet9. Then I get self-checking in each sheet by having to press CheckBox1 again.

I am Swedish and use DeepL Translate... Maybe the translation is not so good.
 
Upvote 0
Olov said:
If something changes in the workbook that changes the calculated values in the sheet, I want CheckBox1 and the sheet to be red.
Click to expand...

Maybe something in Workbook_SheetChange, but I don't understand enough about your workbook to know what.

So far, my understanding is that only cell A1 in each sheet affects the checkbox and tab colour, and the code looks at the one cell. I can't really help further without seeing your workbook, so perhaps upload your workbook to a file sharing site and post the link here.
 
Upvote 0
John_w said:
Maybe something in Workbook_SheetChange, but I don't understand enough about your workbook to know what.

So far, my understanding is that only cell A1 in each sheet affects the checkbox and tab colour, and the code looks at the one cell. I can't really help further without seeing your workbook, so perhaps upload your workbook to a file sharing site and post the link here.
Click to expand...
Ok. I don't want anyone but you, whom I trust 100%, to see what I'm doing. I work at a company and they don't want anyone else to see because their name and address is in the workbook. Can I email you the file directly?
 
Upvote 0
Settled on this solution and confirmed as working exactly as required by Olov.

ThisWorkbook module:
VBA Code: 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Dim cb1 As OLEObject

    'Get CheckBox1 ActiveX control on this sheet, if it exists

    On Error Resume Next
    Set cb1 = Sh.OLEObjects("CheckBox1")
    On Error GoTo 0
    If Not cb1 Is Nothing Then
        Update_CheckBox Sh, cb1
    End If
    
End Sub

Sheet module of each sheet which contains a CheckBox1 ActiveX check box control:
VBA Code: 
Private Sub CheckBox1_Click()
    Update_CheckBox Me, Me.CheckBox1
End Sub

Standard module:
VBA Code: 
Public Sub Update_CheckBox(Sh As Object, cb As Object)

    If Application.EnableEvents = True Then
        With Sh
            If cb.Object.Value = True And cb.Object.BackColor = vbGreen Then
                'CheckBox is currently ticked and green, so change CheckBox to not ticked red and sheet tab to red
                'Changing the CheckBox's Value property (True=Ticked, False=Unticked) causes the CheckBox's Click event handler to be called.  Use the global EnableEvents flag to
                'tell the Click event handler to effectively ignore this change.
                'The line cb.Object.Value = False below causes the following call sequence: sheet's CheckBox1_Click  --> Update_CheckBox --> Do nothing, because Application.EnableEvents = False
                Application.EnableEvents = False
                cb.Object.Value = False
                Application.EnableEvents = True
                cb.Object.BackColor = vbRed
                cb.Visible = True
                .Tab.Color = vbRed
            Else
                If Not IsError(.Range("A1").Value) Then
                    If .Range("A1").Value <> "" Then
                        'Cell A1 is not blank so set colour of checkbox and sheet tab
                        If cb.Object.Value = True Then
                            'Checkbox is ticked - green
                            cb.Object.BackColor = vbGreen
                            cb.Visible = True
                            .Tab.Color = vbGreen
                        Else
                            'Checkbox is not ticked - red
                            cb.Object.BackColor = vbRed
                            cb.Visible = True
                            .Tab.Color = vbRed
                        End If
                    Else
                        'Cell A1 is blank so hide checkbox and set colour of sheet tab to white
                        cb.Visible = False
                        .Tab.Color = vbWhite
                    End If
                End If
            End If
        End With
    End If

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Solved
Passing a checkbox object into a private sub doesn't work
Replies
2
Views
351
Arcadian
A
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
228
bobsan42
bobsan42
E
  • Solved
Checkbox to ActiveX checkbox change in VBA
Replies
3
Views
291
Domenic
D
E
  • Solved
Macro to run anytime a ActiveX checkbox is used (no linked cells)
Replies
8
Views
463
John_w
J
G
  • Question
Activex Check Box VBA Code not working automatically
Replies
6
Views
340
ganeshadu
G

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top