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

Unlocking cells if one or more of several ActiveX CheckBoxes are checked. Then locking the cell again when ALL of the checkboxes are unchecked. VBA

0

Hello,

I have several checkboxes being used in a form, let's say "CheckBox1", "CheckBox2" and "CheckBox3". I would like to have it happen when one or more of these checkboxes are checked the cell H14 goes from its default state of being greyed out and locked to being white and unlocked, but then if ALL boxes are unchecked I would like the cell H14 to revert back to its default state (grey and locked). What is happening right now is that the cell unlockes if any when one or more boxes are checked, however if any of the boxes are unchecked the cell H14 goes back to being grey and locked even if some of the other boxes are still checked.

This is a sample of the code I am using for each of the checkboxes:

If Me.CheckBox1.Value = False Then
    Range("H14").Locked = True
    Range("H14").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

Else: Me.CheckBox1.Value = True
    Range("H14").Locked = False
    Range("H14").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

I am thinking I need to use an AND in the IF statment but do not know how to do this with more than 2 conditions.

Also, a minor aesthetic thing, is it possible to change the cell to locked/grey or unlocked/white without selecting the cell first?

Thanks!

Answer
Discuss

Answers

0
Selected Answer

Hi, try this,(I'm sorry for any errors I didn't try that code)

I put "and" to check all of them and I removed the select too and added the colorindex so change the color of the cell

If CheckBox1.Value = false and  CheckBox2.Value = false and CheckBox3.Value = false then 
         Range("H14").Locked = True
    With Range("H14").Interior 
        .colorindex = 15
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True Then

          Range("H14").Locked = False

 With Range("H14").Interior

         .colorindex = 2

         .Pattern = xlSolid  

         .PatternColorIndex = xlAutomatic            

         .ThemeColor = xlThemeColorDark1     

         .TintAndShade = 0  

         .PatternTintAndShade = 0  

  End With

  End if
Discuss

Discussion

Thanks for the responce!

I had tried using the and as well but it does not seem to accomplish what I need. 

However the colorindex thing works like a charm thank you, makes the form work much more cleanly.
aorlowski (rep: 4) Jul 28, '16 at 1:37 pm
Could you re try this new code
(The update is in "and")
MRVMV (rep: 52) Jul 28, '16 at 2:22 pm
I think if you just replace the Else with this:
ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True Then

It should work.
don (rep: 1989) Jul 28, '16 at 2:25 pm
Good idea, I will update my answer for that.

Thanks.
MRVMV (rep: 52) Jul 28, '16 at 2:27 pm
Don, I found that editing answer and paste code copied from forum make big problems
MRVMV (rep: 52) Jul 28, '16 at 2:37 pm
Can you be more specific so I can replicate the issue? What device are you using and what exactly did you try to do?
don (rep: 1989) Jul 28, '16 at 3:09 pm
I'm using Samsung S6 Edge, and open site by Google Chrome, 

I will try to describe it in detail

When I copy 
Code
And paste it in any answer or discussing that code will be pasted like a code too and not like a plain text so if you paste it inside other code the, you will see everything before this code as a code and everything after it as Not, and I'd you edit the answer again and delete the code tags you will see like my answer here first lines don't have big space between them but last ones have it and the last lines will be on the same line with spaces between them.

Thanks.
MRVMV (rep: 52) Jul 28, '16 at 3:18 pm
Ok yes I got it. It is supposed to work like that for other reasons, mainly that people might mess-up inputting the CODE tags and that is supposed to try and "smooth-out" any formatting issue. But, I never tested the situation you mentioned lol. I will see if there is an easy fix for it. I was thinking to add a link to each code section that says "Select All" to select the code but I will update the members-discussion or the blog with more info when I decide what to do with that.
Thanks for bringing it to my attention!
don (rep: 1989) Jul 28, '16 at 3:42 pm
Add to Discussion


Answer the Question

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