Solved

Excel VBA Checkbox decisions are correct but graphical checkmark does not display

Posted on 2016-11-06
5
29 Views
Last Modified: 2016-11-06
I have a multipage userform that writes values to various sheets then re-writes those values back to the controls when the userform is re-opened. One problem I have is that upon re-open and re-write, the checkbox value will correctly interpret its status (i.e, whatever decision it is controlling will function correctly) however, the graphical checkmark does not appear.  The only way I have managed to re-install the graphical checkmark is to programatically turn the checkbox  on and off  as the userform opens:


If Checkbox1.Value = true then
Checkbox1.Value = False
CheckBox1. Value = true
ElseIf  If Checkbox1.Value = False then
Checkbox1.Value = True
CheckBox1. Value = False
End If


This is problematic however, because if I use this technique, I have to gather the decision making logic of the checkbox and then
re-install it as the checkbox turns on and off. Some of these checkboxes control quite a bit of data and it just seems redundant to re-configure data after I already have done it previously.

Any advice for me on this from the experts?

Thank you
0
Comment
Question by:Member_2_7966101
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41876148
You say "graphical checkbox". What is the actual type of the control?

If it's a normal checkbox (Microsoft Forms 2.0 Checkbox) then what is it's TripleState when the form opens? It could be either True, False or Null.
0
 

Author Comment

by:Member_2_7966101
ID: 41876176
Thanks for the question: When I say "Graphical" I'm referring to the actual physical checkmark that appears in the checkbox.  I've attached an example to this reply. I'm not familiar with "triple State" and how to query it. In searching online, the general advice was to set the value to "True" to enable the checkbox. I've attached a screenshot of the ttolbox that I use to add the checkmark control if that helps. The checkbox is "True" as per the attached example but the physical checkmark is not present which is confusing to the user.
CheckBox.JPG
Control.JPG
0
 

Author Comment

by:Member_2_7966101
ID: 41876183
I just went online and you are correct, a checkbox can be set to triple state:

The Value property of a CheckBox indicates whether it is selected or not. A True value indicates that the CheckBox is selected, False indicates that it is cleared; and the Null value indicates that it is neither selected nor cleared, and the CheckBox will appear shaded in this case. The value of TripleState property should be set (can be set either in the Properties window or by using a macro or vba code) to True for the CheckBox to have a Null value.

However, Unless i"m not understanding your advice, I don't know if this pertains to my issue as my concern is the presence of the checkmark when the value of the checkbox is set to true.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41876186
CheckboxHere's a picture of a userform with some checkboxes, and at the red arrow you can see the TripleState property. If yours says True then change it to False.

You might also want to read my article on debugging.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41876214
Do you understand why your code wasn't working right?

In any case I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

15 Experts available now in Live!

Get 1:1 Help Now