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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename combobox with a macro
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).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 Sub -------------------- -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=560656 |
Reply |
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 |