Solved

Use VBA to click a checkbox

Posted on 2012-03-31
22
560 Views
Last Modified: 2012-04-01
I have a worksheet called "Data". When I Tab through cell "D4" and the cell is empty, I want a  checkbox called "CBX_P3" which is in Module 3 of the workbook to be clicked. If I type something in the cell and then Tab out, I want a checkbox called "CBX_Q3" to be clicked.

I can set these checkboxes to true or false but when I physically click them, they trigger a bunch of other events whereas by just setting them to true or false, they appear checked but the other events are not triggered. That's my problem.

They are formfield checkboxes in Excel 2010 but just in a worksheet, not on a form.

This is what I've tried but it gives me an error message:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D4"), Target) Is Nothing Then
        CBX_PB3.Value = True
        MsgBox "D4 has changed"
        
    End If
End Sub

Open in new window

Thanks for any suggestions!
0
Comment
Question by:calbais
  • 10
  • 8
  • 4
22 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37791079
Which worksheet are the checkboxes on?

Do they have macros assigned to them?

What code are they triggering?

If it's event code have you tried disabling events?
0
 

Author Comment

by:calbais
ID: 37791206
The checkboxes are also on the "Data" worksheet.
They have macros assigned to them (See code below). Both checkboxes check or uncheck 20 checkboxes below them so they are either all checked or all unchecked. The macros make them mutually exclusive so if I check one the other is unchecked. The checkboxes I want to click with VBA, are the 2 top ones - cells P3 and Q3. When I manually click P3, cells P3:P23 are checked and cells Q3:Q23 are unchecked and vice versa for Cell Q3. I want to be able to click either of these checkboxes as if I did it manually so the macros are triggered.

I don't know what you mean by event codes and disabling them. I believe "Worksheet_Change" is an event code?

 It's the value in Cell D4 that determines which checkbox to click. If D4 is empty, click the CBX_P3 checkbox which should then select cells P3:P23 and uncheck cells Q3:Q23. If there is a value in D4 then the other checkbox should be clicked.
Here is the macros in the 2 checkboxes:
Sub CBX_P3_Click()                  'Toggles "Mail" column between "True" and "False"
If Range("AG3").Value = True Then
    Range("AG4:AG23") = True
    Range("AF3:AF23") = False
    Range("AH3:AH23") = False
    Else: Range("AG3:AG23") = False
End If
End Sub
Sub CBX_Q3_Click()                  'Toggles "Online" column between "True" and "False"
If Range("AH3").Value = True Then
    Range("AH4:AH23") = True
    Range("AF3:AF23") = False
    Range("AG3:AG23") = False
    Else: Range("AH3:AH23") = False
End If
End Sub

Open in new window


I hope I answered your questions.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37791237
Still not 100% clear, but this code will call the Click events of the checkboxes.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D4"), Target) Is Nothing Then

        If Target.Value = "" Then
            Me.CheckBoxes("CBX_P3").Value = True
        Else

            Me.CheckBoxes("CBX_Q3").Value = True
        End If

    End If

End Sub

Open in new window

0
 

Author Comment

by:calbais
ID: 37791683
That checks the checkbox but it doesn't run the macro attached to the checkbox.For example; if I click the "CBX_P3" checkbox by hand, the macro attached will run which unchecks the checkbox in all checkboxes from "CBX_Q3" to "CBX_Q23" and it checks all the checkboxes from "CBX_P3" to "CBX_P23".
I can understand why you are not clear yet because I'm not a good programmer!! The checkboxes use an offset of 17 so in the code above CBX_P3 is actually set by changing the value of cell "AG3" to true or false. If I set that value to true or false, it doesn't run the macro either.
But I need to have the macro triggered when the checkbox changes to true just as it would if I clicked it manually.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37791688
It works for me, though I did use checkboxes that weren't linked to cells.

Could you attach a sample workbook?
0
 

Author Comment

by:calbais
ID: 37791782
Sure. I appreciate your help.

I attached the file but it doesn't show that it is actually attached so I found that I can also send it by www.e-stuff.com so I'll also send it to you via that website.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37791930
There is no file attached I'm afraid, and I can't quite see how you can send it via that site.:)
0
 

Author Comment

by:calbais
ID: 37791968
I don't know why they won't attach. I had the same thing happen not long ago but they got the file from the ee-stuff.com website. If you search help for 'attaching'... that's how I found it.
Here's a url to the spot I was at. You'll need the URL to my question to access the file.
http://www.ee-stuff.com/Expert/

Good luck!!
0
 
LVL 33

Expert Comment

by:Norie
ID: 37791980
Sorry, no luck.

Tried the question ID and the URL and it couldn't find the file.

Why couldn't you attach the file here?

Was it too big? Wrong file extension?

PS The previous URL was to some other site - think there was an e missing.
0
 

Author Comment

by:calbais
ID: 37792037
I don't know why I couldn't attach it... it's only about 400kb... not large.. it's xlsm format which is permitted.
I'll try uploading again on the www.ee-stuff.com site.
0
 

Author Comment

by:calbais
ID: 37792062
Ok, I don't think I uploaded correctly before. Here's a direct link!

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/8241-Control-Sheet-Final.xlsm
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Expert Comment

by:Norie
ID: 37792166
OK, got the file but I won't be able to have a proper look until tomorrow.
0
 
LVL 80

Expert Comment

by:byundt
ID: 37792414
I put the following code in the code pane for worksheet Data. It both checks the checkbox and calls the associated Click event sub. As written, the code is watching just cell D4--but it is easily expanded to watch a column of cells should you wish.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("D4")      'Watch cells in this range
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub
If targ.Cells.Count > 1 Then Set targ = targ.Cells(1)   'If more than one cell is selected, just work with the first one

For Each cel In targ.Cells
    If cel.Value = "" Then
        ActiveSheet.Shapes("CBX_P3").ControlFormat.Value = True
        CBX_P3_Click
    End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("D4")      'Watch cells in this range
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub
If targ.Cells.Count > 1 Then Set targ = targ.Cells(1)   'If more than one cell is selected, just work with the first one

For Each cel In targ.Cells
    If cel.Value <> "" Then
        ActiveSheet.Shapes("CBX_Q3").ControlFormat.Value = True
        CBX_Q3_Click
    End If
Next
End Sub

Open in new window

Control-Sheet-Final-Q27656920.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 37792420
calbais,
I'm trying to guess where you may have gone astray when trying to post your file. Did you both click the Attach button and add a description text? If you don't do both steps, the file won't be attached to the Comment.

Brad
0
 

Author Comment

by:calbais
ID: 37792977
I didn't add the description text the first time. When I did,  it posted ok. Thanks.
I still don't understand why I couldn't attach the file right in the bottom of the reply box. I clicked 'Attach File", browsed to the file so it showed in the space beside the "Browse" button, clicked "Attach" but nothing happened. I had to go to the www.ee-stuff.com website to upload.

The code you posted works fine. It does exactly what I want. Thank you very much!!
The real test will come tomorrow when I give it to my co-workers to use. They always find a way to break my programs!!  lol... probably because I'm not a good code writer. Anyway, it works and will save us a lot of time everyday. Thanks so much to you and Imnorie who stuck by it all day yesterday. I'm sure he would have solved this also but we both went to bed!! lol

Where you have "CBX_P3_Click" on a line by itself, is that all it takes to simulate clicking the checkbox manually?

Who gets the points in this situation? Imnorie spent all day trying to help me but you gave me the final solutioin. What is the proper way to award the 500 points? Can I give you both 500 points?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37793059
I originally had the calls to the click events in the code but after testing it didn't seem required.

Anyway, I kept the code and here it is.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D4"), Target) Is Nothing Then

        If Target.Value = "" Then
            Me.CheckBoxes("CBX_P3").Value = True
            Call CBX_P3_Click
        Else

            Me.CheckBoxes("CBX_Q3").Value = True
            Call CBX_Q3_Click
        End If

    End If

End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 37793387
<<Where you have "CBX_P3_Click" on a line by itself, is that all it takes to simulate clicking the checkbox manually?>>  Actually, that runs the sub named CBX_P3_Click. Your Forms toolbar checkbox will run that routine when it is checked by the user. It doesn't run it when the box is checked by a macro, as I learned by stepping through the code.


Regarding the points, have you tested imnorie's suggestion? It's not quite what you asked for (because CBX_P3_Click isn't triggered by tabbing into cell D4, but rather by clearing the value previously in the cell), but it may do what you need. If so, he has the better solution and deserves the points.

It isn't unusual for one person to start working with you and another to finish the task. You may also choose to use parts of several suggestions when implementing the solution in your real workbook. In such cases, you weigh the contributions to the solution you actually use, and split the points accordingly. For example, imnorie has a more elegant method of changing the checkbox value (although both methods work).

Brad

It isn't permitted to award more than 500 points to a question. And as Zone Advisor, I'm the cop on this beat responsible for enforcing that rule.
0
 

Author Comment

by:calbais
ID: 37793807
Ok, I've tried both versions. Imnorie's version is very concise which I liked but when I first run it and type a number under SIN (C4 column) and tab to the Name (D4 column) and tab out of it, nothing happens. PBX_C3 should be executing checking the "Mail" checkboxes. I can go back and tab through as often as I want but nothing happens until I put a value in D4 and then delete it... Then the "Mail column" checkboxes are checked.
Also when I click the button "Clear Pasted Data" at the top of the "Data" sheet, I get a "runtime error 13 - Type Mismatch" and the following line is highlighted:
If Target.Value = "" Then

Open in new window


For Byundt's code: It works fine and there is no error messages but I would rather it would check the "Mail" or "Online" checkboxes when it leaves the "D4" column. As it is, it checks the "Mail" column (CBX_P3) as soon as I tab into it, before I get a chance to type anything here. If I type something here and tab out it does uncheck the "Mail" column and checks the "Online" column which is the correct thing to do so that is not a big deal. But, if I type something in the "D4" column, tab out and realize I should have left the "D4" column blank and go back and delete it, tab out again, the "Online" column stays checked but the "Mail" column should be checked when "D4" is blank.
If "D4" is blank, "Mail" column should be checked and "Online" unchecked
If"D4" is not blank, "Online" column should be checked and "Mail" unchecked
So could you make a slight adjustment to make it do this.

Thanks
0
 
LVL 80

Accepted Solution

by:
byundt earned 400 total points
ID: 37793832
There really isn't an event that occurs when you leave a cell. There is one when you enter (Select) a cell. There is one when you change the value of a cell. So let me suggest two alternatives:


Alternative A. Responds only if you change the value in D4. There is no Worksheet_SelectionChange sub.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("D4")      'Watch cells in this range
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub
If targ.Cells.Count > 1 Then Set targ = targ.Cells(1)   'If more than one cell is selected, just work with the first one

For Each cel In targ.Cells
    If cel.Value = "" Then
        ActiveSheet.Shapes("CBX_P3").ControlFormat.Value = True
        CBX_P3_Click
    Else
        ActiveSheet.Shapes("CBX_Q3").ControlFormat.Value = True
        CBX_Q3_Click
    End If
Next
End Sub

Open in new window



Alternative B. Responds when you enter cell E4. Does not check if the value in D4 was changed, but does check whether it is empty or has a value. There is no Worksheet_Change sub.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("E4")      'Watch cells in this range
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub
If targ.Cells.Count > 1 Then Set targ = targ.Cells(1)   'If more than one cell is selected, just work with the first one

For Each cel In targ.Offset(0, -1).Cells    'Look at value in column D
    If cel.Value = "" Then
        ActiveSheet.Shapes("CBX_P3").ControlFormat.Value = True
        CBX_P3_Click
    Else
        ActiveSheet.Shapes("CBX_Q3").ControlFormat.Value = True
        CBX_Q3_Click
    End If
Next
End Sub

Open in new window



When testing, you may want to install both subs and turn one off and then the other. The easy way to turn off one of these subs is to put an "x" in front of its name:
Private Sub xWorksheet_SelectionChange(ByVal Target As Range)
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 100 total points
ID: 37793848
I'm not bothered at all who you award the points to.

The code I posted originally was a 'best guess' as I hadn't seen the workbook, so I wasn't even sure of the layout or if there were any other buttons/code involved.

One quick thought though, why not just use buttons(or button) instead of check boxes for CBX_P3 and CBX_Q3.

Or even in cell dropdowns creating using Data>Validation.

Just a thought of course.:)
0
 

Author Comment

by:calbais
ID: 37793969
Byundt:
Your Alternative B works perfectly. The Alternative A gave me errors. So I'm going to use the B option. Thank you very much for helping me!!

Imnorie:
You stuck with me all day yesterday and again today and I greatly appreciate your help.

Thanks so much to both of you!!! I truly appreciate that you share your skills with others!
0
 

Author Closing Comment

by:calbais
ID: 37793972
Both of you did an excellent job!!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now