Excel Form Controls

PcMkr42

New Member
Joined
Jul 21, 2014
Messages
7
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 21, 2014
Messages
7
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

New Member
Joined
Jul 21, 2014
Messages
7
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For forms controls you can use:
Code:
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:
Code:
Activesheet.Checkboxes(Application.Caller).TopLeftCell
to get a reference to the cell under whichever checkbox triggered the macro.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,814
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

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

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
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.
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top