Rename combobox with a macro

[leonidas]

Hi,

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

Please Login or Register  to view this content.

[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

--
Regards,
Tom Ogilvy


"leonidas" wrote:

>
> Hi,
>
> In column L rows 12 to 242 I have ComboBox1 to ComboBox231. The
> comboboxes come from the "Control Toolbox" toolbar.
> I have a macro which inserts a row underneath the activecell and
> inserts a new combobox on this new row. The code is below.
> Problem is that when you select a renamed combobox and look in the
> formulabar it shows the correct name, but when you look in the
> properties of this combobox the name is still the old name.
> Is it possible to also rename the name in the properties of the
> 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 Sub
> --------------------
>
>
> --
> leonidas
> ------------------------------------------------------------------------
> leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
> View this thread: http://www.excelforum.com/showthread...hreadid=560656
>
>