Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Checking all checkboxes

0

Hello,

It seems i can't selected or deselect all the checkboxes we have added for all the data

Code of checkbox will be in module Misc

The idea is when we selected the Default checkbox on cell D5 to selected them all

And also want to color the rows when selected.

And when deselect D5 to deselected all checkboxes and decolor the rows once again.

The seperated selections seems to be working. and i keep the selected rows in B5 as seen in the sample.

Hope someone can also help with this part. thanks

Answer
Discuss

Answers

0
Selected Answer

Making good progress. Nice! 

The procedure below should be assigned to your check box "Chkall 5" in place of the existing.

Sub CheckAll_Click()
    ' 192 - 15 Mar 2021

    Dim Shp         As Shape
    Dim Chk5        As Long

    If StrComp(Application.Caller, "chkall 5", vbTextCompare) = 0 Then
        Chk5 = Sheet1.Shapes("ChkAll 5").OLEFormat.Object.Value
        For Each Shp In Sheet1.Shapes
            With Shp
                If .Type = msoFormControl Then
                    If .FormControlType = xlCheckBox Then
                        If InStr(1, .Name, "check box ", vbTextCompare) = 1 Then
                            .OLEFormat.Object.Value = Chk5
                        End If
                    End If
                End If
            End With
        Next Shp
    End If
End Sub

For further improvement of your project I urge you to add "Option Explicit" at the top of every code sheet. You can add this by default to all sheets you create in the future: VB Editor > Tools >Options > Editor tab > Check "Require Variable Declaration"

Also, learn proper indenting. This is essential for structuring code. If you don't learn it you will have pulled out all your hair before this project is finished.

Discuss

Discussion

Hahaha yeah i don't wanna do that ;)

Awesome code Variatus as always
Still don't seems to get the selected rows
because only .Name works to get the check box and row number
Any idea?

like to select the rows as well when they are selected
But i use this code for make them selected row color :)

using the confitional formatting
=iserror(find(""&row()&"";$B$5;1)) =false


so i get then the rows in B5 like
5(5)6(6) then i know wich are selected

Thanks.

Thanks for the nice feedback ;)
I'm trying to do my best
Still lots to do.
GhostofWanted (rep: 46) Mar 15, '21 at 2:27 pm
Ghost, you are way ahead of me! You asked to set or clear all check boxes with the "master". I think that is done and this thread should be closed.
Now you talk of "selected" rows. This was never explained and I don't understand, nor can it be made part of this thread.
I think, what you mean is that it should be possible to select individual rows by placing the check mark next to them, like I first select all using the master, then deselect one by removing it's checkmark, or the other way around. Let me put it this way: we've created the functionality of the master; now we have to create the functionality of each sub-check box.
Setting a colour for selected rows could be part of that. However, CF will be too complicated when considering changing customers. Aim for Checkmark/On = one colour, Checkmark/Off = another colour.
Do you have an idea how to implement that when the code needs to respond to checkmarks created on the fly? Once you have familiarized yourself with the problem, go ahead and ask a question about it.
Variatus (rep: 4889) Mar 15, '21 at 10:12 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login