On 9 Jun., 16:15, Rod Urand <urand...@gmail.com> wrote:
> I have a client who has created a rather large Excel workbook with
> checkbox form controls.
>
> Unfortunately, each form control was not set to have a cell link.
>
> The client would like to now tabulate the results of the checkboxes
> (to determine how many are checked and how many are unchecked).
>
> Rather than go into each cell and modify the form control property to
> then add a cell link, I am looking to automate this process through
> the use of a macro. Essentially speaking, the macro will locate each
> checkbox in a workbook, modify the control properties, add a cell link
> to the cell adjacent to the checkbox.
This should get you started (copy it into the module of the worksheet
you want to convert and call it as macro).
Option Explicit
' Links all checkboxes of the current workbook with
' the cells they belong to (assuming that the checkbox
' is not a free floating checkbox).
Sub CreateCellLinks()
Dim i As Integer
For i = 1 To Me.Shapes.Count
' Skip any shapes that are no checkboxes.
If TypeName(Me.Shapes(i).DrawingObject) = "CheckBox" Then
Dim cb As CheckBox
Set cb = Me.Shapes(i).DrawingObject
' The following only works if the checkbox is not a floating
' checkbox (it must be placed inside a cell).
Debug.Assert cb.Placement <> xlFreeFloating
cb.LinkedCell = ColumnLetter(cb.TopLeftCell.Column) &
cb.TopLeftCell.Row
End If
Next
End Sub
' Downloaded from http://www.freevbcode.com/ShowCode.asp?ID=9264
Function ColumnLetter(ColumnNumber As Integer) As String
'
'example usage:
'
'Dim temp As Integer
'temp = Sheets(1).Range("B2").End(xlToRight).Column
'MsgBox "The last column of this region is " & _
' ColumnLetter(temp)
'
If ColumnNumber <= 0 Then
'negative column number
ColumnLetter = ""
ElseIf ColumnNumber > 16384 Then
'column not supported (too big) in Excel 2007
ColumnLetter = ""
ElseIf ColumnNumber > 702 Then
' triple letter columns
ColumnLetter = _
Chr((Int((ColumnNumber - 1 - 26 - 676) / 676)) Mod 676 + 65) & _
Chr((Int((ColumnNumber - 1 - 26) / 26) Mod 26) + 65) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
ElseIf ColumnNumber > 26 Then
' double letter columns
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' single letter columns
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function
> A sample worksheet is available but I do not know how to attach it.
Me neither :-)
Regards,
Stuart
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
