Rename combobox with a macro

[leonidas]

Hi,

In column L rows 12 to 242 I have ComboBox1 to ComboBox231. Th
comboboxes come from the "Control Toolbox" toolbar.
I have a macro which inserts a row underneath the activecell an
inserts a new combobox on this new row. The code is below.
Problem is that when you select a renamed combobox and look in th
formulabar it shows the correct name, but when you look in th
properties of this combobox the name is still the old name.
Is it possible to also rename the name in the properties of th
combobox?
Thanks in advance for helping me!



Code
-------------------
Sub test()

Set tgt = ActiveCell
Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
Selection.Offset(1, 0).EntireRow.SpecialCells(xlConstants).ClearContents
tgt.Select
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
ActiveSheet.Shapes("ComboBox1").Select
Selection.Copy
ActiveCell.EntireRow.Select
Intersect(Selection, Columns("L:L")).Select
ActiveSheet.Paste
Selection.Name = "ComboBox" & 410
On Error Resume Next
For i = 400 To myrow - 10 Step -1
ActiveSheet.OLEObjects("ComboBox" & i).name = "ComboBox" & i + 1
Next i
myname1 = Sheets(ActiveSheet.Index + 1).Name
LinkedCell = "'" & myname1 & "'!D" & (myrow - 10) * 3
ListFillRange = "'" & myname1 & "'!C" & (myrow - 10) * 3 & ":C" & ((myrow - 10) * 3) + 2
With ActiveSheet.OLEObjects("ComboBox410")
.LinkedCell = LinkedCell
.ListFillRange = ListFillRange
.Name = "ComboBox" & myrow - 10
End With
ActiveCell.Offset(0, -2).Select
End If
Application.ScreenUpdating = True

End Su

 

[Tom Ogilvy]

What version of Excel are you using?

This should only be necessary in xl97.

' this declaration is important
Dim cbx as MsForms.Combobox
.. . .
For i = 400 To myrow - 10 Step -1
With ActiveSheet.OLEObjects("ComboBox" & i)
.name = "ComboBox" & i + 1
set cbx = .Object
cbx.Name = "ComboBox" & i + 1
end with
Next i