CheckBox naming

[Bill D.]

I need to rename checkboxes on a worksheet IN THE ORDER IN WHICH THE
APPEAR ON THE WORKSHEET. That is the first box at the top will be number
1 the second number 2 and so on.

As I have developed the worksheet layout I have added and removed shape
objects (checkboxes and optionbuttons) so that now the internal
numbering of the objects is no longer sequential from the top down.

I presently use the following two procedures to determine the number of
objects on the worksheet and then rename them based on the type. This
will yield, for example checkbox1, etc.

Sub GetShapeName()
On Error Resume Next
For i = 1 To ActiveSheet.Shapes.Count
With ActiveSheet
ShNm = .Shapes(i).Name
End With
Next i
End Sub

Sub NameTBs()'<=== Change form type and name as needed
x = 0
On Error Resume Next
For i = 1 To ActiveSheet.Shapes.Count
With ActiveSheet
If .Shapes(i).Type = msoFormControl Then
If .Shapes(i).FormControlType = xlCheckBox Then
x = x + 1
.Shapes(i).Name = "checkbox" & x
End If
End If
End With
Next i
End Sub

This works well except the renaming is based on the internal numbering
of the object which is not the same as the physcial location of the
object on the sheet. So, based on physical location, I end up with
something like:

checkbox1
checkbox2
checkbox38
checkbox3
checkbox24

Is there a way to rename the objects based on position from the top of
the sheet? is zorder what I need to use?

I hope this explanation is clear.

Thanks

Bill

There are 10 types of people in the world. Those that understand binary
and those that don't.

 

[Jake Marx]

Hi Bill,

I think you'll want to run through the collection, find the checkboxes, and
figure out the rows they appear in. Once you have that information, you can
simply sort the list by row # and assign the checkboxes the names that way.
Here's how you can get the row numbers:

Sub test()
Dim ctl As Object

For Each ctl In Sheet1.OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
Debug.Print "Name: " & ctl.Name & _
", Row: " & ctl.TopLeftCell.Row
End If
Next ctl
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bill D.]

Thanks for your reply.

When running your suggested code I get a Compile error: User-defined
type not defined. The highlighted portion of the code is: TypeOf
ctl.Object Is MSForms.CheckBox

Bill

There are 10 types of people in the world. Those that understand binary
and those that don't.

 

[Jake Marx]

Hi Bill,

Sorry - I mistakenly thought you were using ActiveX CheckBoxes (from the
Controls toolbar). If you're using CheckBoxes from the Forms toolbar, you
can modify the code to this:

Sub test()
Dim ctl As Shape

For Each ctl In Sheet1.Shapes
If TypeOf ctl.OLEFormat.Object Is CheckBox Then
Debug.Print "Name: " & ctl.Name & _
", Row: " & ctl.TopLeftCell.Row
End If
Next ctl
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bill D.]

Sorry for being so dense but I assume the output should appear in the
immediate window. I no longer get an error with the modified code but
nothing is printed in the immediate window.

Am I again misunderstanding something?

Thanks for your patience.

Bill

There are 10 types of people in the world. Those that understand binary
and those that don't.

 

[Jake Marx]

Hi Bill,

Yes, it should appear in the debug window if it gets to the Debug.Print
statement. Are your CheckBoxes from the Forms toolbar? It works for me in
XL2002. Try this and let me know what you get:

Sub test()
Dim ctl As Shape

Debug.Print Sheet1.Shapes.Count

For Each ctl In Sheet1.Shapes
Debug.Print TypeName$(ctl.OLEFormat.Object)
Next ctl
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bill D.]

First, yes I am getting the checkboxes from the Forms toolbar.

My test sheet has three checkboxes(78, 79 ,80). The goal is to rename
these as checkbox1, checkbox2 and checkbox3 to use in a select case
procedure.

The new code prints the following in the Immediate window:

3
CheckBox
CheckBox
CheckBox

I am using Excel 2003
Bill

There are 10 types of people in the world. Those that understand binary
and those that don't.

 

[Bill D.]

I modified:

If TypeOf ctl.OLEFormat Is CheckBox

to:

If TypeOf ctl.OLEFormat.Object Is CheckBox

and it works.

Thanks for your help


Bill

There are 10 types of people in the world. Those that understand binary
and those that don't.

 

[Jake Marx]

Hi Bill,

Strange. The code I provided works for me. Anyway, you can just use the
FormControlType property like you were using in your original post:

Sub test()
Dim ctl As Shape

For Each ctl In Sheet1.Shapes
If ctl.FormControlType = xlCheckBox Then
Debug.Print "Name: " & ctl.Name & _
", Row: " & ctl.TopLeftCell.Row
End If
Next ctl
End Sub

Once you have the name and row number, you could put those into a range,
sort the range by row number, then assign the new names to the checkboxes
from lowest to highest row. Let us know if you get stuck with that part.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bill D.]

Hi Jake,

Other work came up and I am just now getting back to this project.

I modified your code a bit so that it generated an new Excel worksheet:

Sub RowNumList()
Dim ctl As Shape
i = 0
For Each ctl In Sheet1.Shapes
If TypeOf ctl.OLEFormat.Object Is CheckBox Then
Sheets("NewSheet").Range("A1").Offset(i, 0) = _
ctl.Name
Sheets("NewSheet").Range("A1").Offset(i, 1) = _
ctl.TopLeftCell.Row
End If
i = i + 1
Next
End Sub

This created a two column list(checkbox number, row number) which I then
sorted on the row number. As follows:

Check Box 86 4
Check Box 81 7
Check Box 84 8
Check Box 83 10
Check Box 82 11
Check Box 85 13

Now, what I need to do is rename the checkboxes so that:

Check Box 86 becomes checkbox1
Check Box 81 becomes checkbox2
..
..
..
Check Box 85 becomes checkbox6

I know I need a loop that will assign the row number in the list to
ctl.TopLeftCell.Row and then designate the ctl.Nmae value but I just
can't seem to see how. The worksheet with the boxes is "Sheet1" and the
generated list is on "NewSheet"


Bill

There are 10 types of people; those that understand binary and those
that don't.

 

[Bill D.]

Additional information to above.

I added a third column to the list which shows the ZOrder. Then, sorting
on row number gives me the index number for the corresponding checkbox.
I wrote the following code to rename the checkboxes:

Sub ReName()
For i = 1 To 6
x = ActiveSheet.Cells(i, 3) '<=== page with list (NewSheet)
Sheets("Sheet1").Shapes(x).Name = "checkbox" & i
Next
End Sub

This is run with the NewSheet page active and I get an Run-time error
'70': Permission denied. This doesn't make sense to me.

If I use only the line:

Sheets("Sheet1").Shapes(x).Name = "checkbox" & i

and substitute numbers for x and i and run it alone it works correctly.
It onl;y fails when run in the For Next loop.

Bill

There are 10 types of people; those that understand binary and those
that don't.

 

[Dave Peterson]

You can use the fact that these are textboxes from the Forms toolbar and access
them directly.

Try this against a copy of your workbook--or don't save if it didn't work.

(I didn't put any checks to check to see if there's already a checkbox with a
name that matches one of the new names!):

Option Explicit
Sub RowNumList()
Dim iCtr As Long
Dim myCBX As CheckBox
Dim newWks As Worksheet
Dim curWks As Worksheet

Set curWks = sheet1
Set newWks = Worksheets.Add

With newWks
iCtr = 1
For Each myCBX In curWks.CheckBoxes
.Cells(iCtr, 1).Value = myCBX.Name
.Cells(iCtr, 2).Value = myCBX.TopLeftCell.Row
iCtr = iCtr + 1
Next myCBX

'sort that list
.Range("a1").CurrentRegion.Sort _
key1:=.Range("b1"), order1:=xlAscending, header:=xlNo

For iCtr = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
curWks.CheckBoxes(.Cells(iCtr, "A").Value).Name = "Checkbox" & iCtr
Next iCtr

End With

' Uncomment the .delete line if you don't want to see the list
application.displayalerts = false
' newwks.delete
application.displayalerts = true

End Sub

It builds that list on a new worksheet, sorts by the column with the
..topleftcell.row in it. Then goes back and renames stuff based on the row that
it's in.