Can you send me your sample worksheet? I may be able to help. I had to do something similar. I used the top/left location of the checkbox to determine the cell location to assign.
It's been a while, so I'll have to re-create it. Paul ________________________________ From: Rod Urand <urand...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Thu, June 10, 2010 8:14:44 AM Subject: $$Excel-Macros$$ Re: Form Control Checkboxes Stuart, Thank you for going to all the trouble to build this. I do have a problem. I just found out that they are indeed free floating checkboxes that are not attached to cells. Are we just out of luck here? On Jun 10, 5:08 am, Stuart Redmann <dertop...@web.de> wrote: > 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 fromhttp://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 -- ---------------------------------------------------------------------------------- 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
