Excel Form Controls

[PcMkr42]

When I put form controls onto a spread sheet is there anywhere I can easily see and access its properties? Format Control is lacking many values and I do not see them in the vba project toolbar. I am trying to get checkbox locations and checkbox numbers if possible. If there isn't an easy place to see these is it still possible to get the checkbox locations in VBA?

 

[Rick Rothstein]

When I put form controls onto a spread sheet is there anywhere I can easily see and access its properties? Format Control is lacking many values and I do not see them in the vba project toolbar. I am trying to get checkbox locations and checkbox numbers if possible. If there isn't an easy place to see these is it still possible to get the checkbox locations in VBA?

What exactly do you mean by "checkbox locations" (Top/Left, cell it's over, something else) and checkbox numbers? Perhaps if you tell us what you are trying to do with the Checkbox(es), it might be clearer how to answer you.

 

[PcMkr42]

I have muliple checkboxes on each row for categorizing data entered by the user. If one or multiple are checked, the user's input will get added on that row, in another column. The column it is added to is specified by which check boxes are checked. Every time I add a form control checkbox directly to a spreadsheet it gets assigned a number that I usually use in ActiveSheet.Shapes("Check Box 1"). I cannot find a way to see this information for each checkbox or any other information about that checkbox(like location). Ideally I don't want to use the checkbox number, because as you add lines in the middle of a spreadsheet it can become unwieldy. If location could be used (either cell or top/left) then it would be much simpler to code. Thank you for the help.

 

[PcMkr42]

I think I got what I needed to make it work

ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Left

ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Top

I just grab the information from any checkbox if it is checked an write on the line accordingly.

It would still be nice to be able to see each property associated with each cell. Currently I just output the values I want in a msg box, but it is not as convenient as just seeing it directly in excel somewhere.

 

[Rick Rothstein]

I think I got what I needed to make it work

ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Left

ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Top

I just grab the information from any checkbox if it is checked an write on the line accordingly.

It would still be nice to be able to see each property associated with each cell. Currently I just output the values I want in a msg box, but it is not as convenient as just seeing it directly in excel somewhere.

Yes, a list of the properties would be nice. And OLEFormat.Object is a way to get to all of the properties of a Form control (assuming you know that property exists), but OLEFormat.Object is not needed for every property as some of them are exposed directly through the Shape objet... Left and Top are two of them... the above references can be replaced with these and your code should still work...

ActiveSheet.Shapes("Check Box 1").Left

ActiveSheet.Shapes("Check Box 1").Top

 

[RoryA]

For forms controls you can use:

Activesheet.Checkboxes("Check Box 1").TopLeftCell

to get a reference to the cell under its top left corner. If you're assigning a macro to the checkboxes, you can use:

Activesheet.Checkboxes(Application.Caller).TopLeftCell

to get a reference to the cell under whichever checkbox triggered the macro.

 

[mikerickson]

When using Forms controls, the .ControlFormat of the shape is used to probe its value. Checkboxes do not return True or False, they return xlOn or xlOff

MsgBox (ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn)

 

[Kyle123]

Slightly off topic here (ish). These sorts of questions seem to get asked a lot as people do not seem to be aware that forms controls can be accessed directly (as Rory demonstrates) as they are hidden by default. Out of interest does anyone know why these are hidden members? I'm sure there is some reason, but it seems an odd decision considering how commonly they are used.