Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename combobox with a macro
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).ClearConten ts 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 ------------------- -- leonida ----------------------------------------------------------------------- leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=56065 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename a Worksheet within a Macro | Excel Programming | |||
rename a workbook using a macro | Excel Programming | |||
Rename Macro | Excel Discussion (Misc queries) | |||
rename macro | Excel Programming | |||
Can I rename a directory using a macro | Excel Programming |