Automatically naming and linking controls

[Blue Max]

We are developing worksheets where we have a tall column of checkboxes with
the checkbox linked to the cell beneath each control. We also want to
sequentially name each control, starting at the top of the column, with a
standard prefix concatenated to a sequential number. Next, we want to link
each of those controls with the cell directly beneath it.

Understandably, manually typing the '(Name)' and 'LinkedCell' property for
each control is a bit frustrating. Unlike, copying formulas down a column,
the linked cell reference for the control does not automatically adjust the
cell link as a new control is pasted over a new cell. Does anybody have any
thoughts on simplifying or automating this process?

 

[Dylan @ UAFC]

You might be in a territory were you would be better
using some type of SQL server code.

Im as familiar with VBA in excel, but I really don't think
you can achieve this with traditional formula or functions

 

[Chip Pearson]

Assuming that you are using checkboxes from the Controls command bar,
not the Forms command bar, you can use code like the following. Change
the test for column 3 to whatever column(s) your checkbox reside in.
Change the value of PREFIX to whatever prefix you want in the name.

Sub AAA()

Dim WS As Worksheet
Dim Ckh As MSForms.CheckBox
Dim OleObj As OLEObject
Dim N As Long
Const PREFIX = "MyCheckBox"
N = 0
Set WS = Worksheets("Sheet1")
For Each OleObj In WS.OLEObjects
With OleObj
If TypeOf .Object Is MSForms.CheckBox Then
If .TopLeftCell.Column = 3 Then
N = N + 1
.Name = PREFIX & "_" & CStr(N)
.LinkedCell = OleObj.TopLeftCell.Address
End If
End With
End If
Next OleObj

End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Blue Max]

Thank you very much, Chip. I especially appreciate the code you posted as
an example. I think we are talking about the same check box control, if I
am not mistaken. If not, I assume other controls also have names that could
be manipulated in similar fashion, correct? Just for clarification, I
selected this check box from the Excel 2007 Developer Tab > Control Section

Insert Button > ActiveX Controls group. Furthermore, I think I follow

your code logic, but do have a few basic questions:

FIRST, I assume if I am using other than "Sheet 1" that I will have to
change that argument also, correct? Or perhaps even simply address the
active sheet?

SECOND, what does .TopLeftCell do? It sounds like it identifies the cell
underlying the top-left corner of an object, correct? Will this method
still work if the 'Check Box' object was drawn using the 'ALT' key to snap
the object corners to the cell corners (presumably the object borders now
exactly match the borders between cells)? What if some of the copies of the
original (pasted after selecting a new cell) have some slight unexpected
overlap, will that identify the wrong cell?

THIRD, I also assume that I can assign the 'LinkedCell' property by
specifying a column letter, concatenated with an incrementing number, seeded
with the proper beginning row number, correct? This might overcome the
problem of any mis-aligned objects identifying the wrong linked cell.

FOURTH, when coding these commands in Visual Basic, is there an assistant
(much like the Excel formula bar) that will show the novice user what
options each command possesses? I have played around a little with the
Object Browser, but I get lost in the terminology (command, property,
argument, method, library, class, member, etcetera) and no meaningful
descriptions for mega-lists of items. Where does a novice start so they can
understand some of the basics of coding for Excel and other MS applications?

FIFTH, is there a way to select the desired check boxes as a group and then
perform the operation on the group of selected objects?

SIXTH, I may be mistaken, but are your last two 'End With' and 'End If'
statements reversed?

Finally, we seem to experience a noticeable delay in the update of any
checkbox when it is modified on this sheet. Would about forty simple
TRUE/FALSE check boxes actually slow down this spreadsheet on a
state-of-the-art quad-core processor? I can't believe that the repetitive
use of such a simple control would generate a performance liability.

Thank you again for your extremely helpful suggestions,

Richard


************

 

[Blue Max]

Dylan,

Thank you for sharing your perspective. Chip Pearson has also given some
suggestions for coding a solution that may help automate this task. Might
be nice if Microsoft would provide some sort of a naming/renaming and
linking/re-linking utility that would allow the user to quickly select
groups of obects and provide them with sequential names or links for fields
of this nature. Especially since it looks like it might be fairly easy to
code a general purpose utility.

Thanks,
Richard

**********

 

[Chip Pearson]

Richard,

selected this check box from the Excel 2007 Developer Tab > Control Section

Those are the correct controls. Excel's own Forms control are
depracted since way back when, but people still use them.

FIRST, I assume if I am using other than "Sheet 1" that I will have to
change that argument also, correct? Or perhaps even simply address the
active sheet?

Yes, change "Sheet1" to whatever sheet contains the controls, or use
ActiveSheet to reference whatever sheet happens to be active when the
code is executed.

SECOND, what does .TopLeftCell do? It sounds like it identifies the cell
underlying the top-left corner of an object, correct?

A control can be resized to cover any number of cell. TopLeftCell is
the cell in which the top-left corner of the control is located.

still work if the 'Check Box' object was drawn using the 'ALT' key to snap
the object corners to the cell corners (presumably the object borders now
exactly match the borders between cells)? What if some of the copies of the
original (pasted after selecting a new cell) have some slight unexpected
overlap, will that identify the wrong cell?

It doesn't matter how the controls were drawn on the worksheet.
Overlapped controls shouldn't matter. But you still need to be sure
than the control's top left corner is in the correct column.

THIRD, I also assume that I can assign the 'LinkedCell' property by
specifying a column letter, concatenated with an incrementing number, seeded
with the proper beginning row number, correct? This might overcome the
problem of any mis-aligned objects identifying the wrong linked cell.

The code I used set the cell link to the top left cell, but you can
set the linked cell to any cell you want. You can build up the range
address with any text functions you want, and as long as the string is
a valid cell reference, you can use that when specifying the linked
cell address.

SIXTH, I may be mistaken, but are your last two 'End With' and 'End If'
statements reversed?

Yup, they're switched. Sorry about that.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Blue Max]

Thanks Chip, your explanations are greatly appreciated. As to the question
regarding the TopLeftCell, I think I understand your explanation, but our
question actually went to the heart of what happens when a graphic
theoretically lies on the shared border between cells? We assume that
snapping an object (ALT) during creation effectively places the object edit
box extents along a shared border, or is this incorrect?

Furthermore, what component of an irregular shaped object is assumed to
constitute the top left corner as it relates to the underlying cells? Is
this test always a function of where the top left corner of the rectangular
bounding box (marquee) lies versus the actual object?

Thanks for all your help,

Richard

********************