Checkbox problem

QuestionEdit

QUESTION: Having benefited from your expertise on several previous occasions, I have decided to seek your help again. I have an2007 spreadsheet which does calculations from user input. Virtually all the mathematical calculations are done by spreadsheet formulae, with VBA macros handling presentation and error-processing. The sheet is protected, with only the input cells accessible to the user, so when the VBA needs to alter a cell value it unprotects the sheet, enters the new value, then reprotects the sheet. In one cell there is a checkbox (CheckBox1)which the user ticks or unticks, depending on the type of input. It was linked to another cell through which VBA read its value as True or False. However, with protecion in place, the checkbox tries to update the linked cell before the VBA runs to unprotect the sheet, generating an error message. I disconnected the linked cell and tried to read the checkbox status directly in VBA. I can read or alter its Visible property by referring to it as ActiveSheet.Shapes("CheckBox1"), but it then has no .Value property for me to read. How do I address this checkbox in VBA, without a linked cell, to ascertain its status? I can get around it by putting all linked cells in a hidden column where I don't have to lock the cells - there are also several dropdown lists - but this has been a long-term problem I want to solve once and for all! Thank you for you time and hope you can help me once again. Regards Terry

ANSWER: Terry,

It sounds like you are using an activeX Checkbox. Then you can use

Activesheet.Checkbox1.Value

or ActiveSheet.OleObjects("Checkbox1").Object.Value

so demo'd from the immediate window in the VBE ? activesheet.Checkbox1.Value True ? activesheet.OleObjects("Checkbox1").Object.Value True

so activeX checkboxes are OLEObjects, so you can use the name of the control as an argument to the OLEObjects collection to identify you checkbox and then qualify that with Object to then access the properties of that object (such as .Value).

Or you can use the "codename" of the control which should be the same as the name (not necessarily true in97 or as I recall2000 - but this behavior has been altered so they remain the same)

The codename is the same concept when you use

Sheet1.Range("A1")

or Worksheets("Sheet1").Range("A1")

in that case the name Sheet1 can be something completely different from the codename of Sheet1 - I just mention this as an analogy to the Checkbox name in case the concept of a codename is not clear to you.

If you wanted to use the shapes collection to address the activeX control then

? ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Object.Value True

(so two objects)
is the format for the value property or most other properties. 

The value for the activeX checkboxes should be either True or False

There is a possibility that the checkbox could be a VBA/Excel checkbox. then it would be a member of the checkboxes collection - but these controls usually have default names like Check Box 1

if ActiveSheet.Checkboxes("Check box 1").Value = xlOn then

if Activesheet.Checkboxes("Check box 1").Value - xlOff then

Since your name is not of that form, I would guess you are using the activeX Checkbox.

If that isn't clear or if it doesn't answer your question, post a followup with clarification on the issue.

---------- FOLLOW-UP ----------

QUESTION: Thank you once again for your timely reply to my checkbox question, which solved my syntax problem of reading the checkbox value. I had anforms- not ActiveX - checkbox, whose name I had altered to make it more meaningful, and the ActiveSheet.Checkboxes("Check box 1").Value format worked perfectly. My question: How can a VBA novice, such as I, work out the correct syntax when the Object Browser does not even have the'Checkboxes' in its vocabulary and the 'ActiveSheet' options dead-end? It's like trying to find out how to spell aby looking in the dictionary, when you can't spell it in the first place - ONLY WORSE! Sometimes when writing code, a list of possibilities appears, but this never seems to happen when you really need it. Is there a hierarchy tree you can refer to, or is the syntax something you learn only from experience? Terry

AnswerEdit

Terry,

if you go to the object browser in the VBE and right click on a blank area of the dialog, you will get a pop up menu. On choice is to show hidden members. If you select that, you will be able to find checkboxes/checkbox, dropdowns/dropdown, buttons/button, spinners/spinner and so forth (and dialog sheets). These are thecontrols. For some reason, when ActiveX controls and userforms were introduced, Microsoft stopped supporting these other controls. So you don't have any information in the help files and so forth. I don't think they list them in the object model either (the hierarchy you asked about).

If you want to send me an email, I can send you adocument that provides information on some of this stuff. If you can find old documentation on5/Excel 95 you can find it there as well. I had an95 VBA programmers Reference book (A microsoft press book) which was good in this area and in general. You might be able to find one on a used book site and pick it up for a couple of bucks.