VBA How to change the name of an existing ComboBox

[BVOPP]

How can i change the name of an existing ComboBox?

I tried .. and it worked .. but not anymore... and the debugger does not work here neither...

    ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
    With Selection
      .LinkedCell = "E" & r + 2
      .ListFillRange = Range("H" & r + 2).Value
      .Name = "Box"


    End With

 

[Domenic]

Does the active sheet in fact contain an ActiveX combobox named ComboBox1 ?

 

[BVOPP]

Yes it does

and the change of the linked cell and listfillrange works fine too, only the Name I need to change in something else, so that if I create a new combobox afterwards it again gets the name ComboBox1 as by default and not ComboBox2.

Please also see my previous Thread... I had it fixed.. it worked but now it does not anymore... it is very strange.

 

[Domenic]

I'm not sure I understand. The syntax for your code is fine. If your worksheet contains a combobox named "ComboBox1" that's fine, there's shouldn't be a problem. But if you already have a combobox named "Box", then you'll get an error, since you can't have another one with the same name. Is this the case?

 

[BVOPP]

Only the name of the grafic is changed into Box (after going into Design Mode, and selecting the object you see box in the Name Box) but when you select properties then you can see that the (Name) still is ComboBox1. And this makes that when i copy or create a new ComboBox it will be named: ComboBox2

 

[Domenic]

...but when you select properties then you can see that the (Name) still is ComboBox1.

Apparently, it doesn't get updated until the workbook is closed, and re-opened. So, until then, you'll still be able to refer to ComboBox1. Once the workbook is re-opened, both the name in the Name Box and under the Properties window will be the same.

By the way, instead of referring to your combobox by name, you can refer to it by index. So, for example, to refer to the last oleobject that was added to the active sheet, which in this case is a combobox, you could do the following...

    With ActiveSheet
        With .OLEObjects(.OLEObjects.Count)
            .LinkedCell = "E" & r + 2
            .ListFillRange = Range("H" & r + 2).Value
            .Name = "Box"
        End With
    End With

Alternatively, you could assign the newly created combobox to an object variable, and refer to that variable in order to set the properties...

    Dim newOleObject As OLEObject
    
    Set newOleObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=Range("a10").Left, Top:=Range("a10").Top, Width:=48, Height:=15)
    With newOleObject
        .LinkedCell = "E" & r + 2
        .ListFillRange = Range("H" & r + 2).Value
        .Name = "Box"
    End With

 

[BVOPP]

you are correct... indead the name is changed after reopening the excel sheet.
your latest code is for creating a new ComboBox I think this will be doable but then I need to set some properties. I will be using that.
Thanks a lot !!

 

[BVOPP]

But ... properties like ColumnCount =5, ColumnHeads =True; ColumnWidths (0 for the first column); ListRows = 10 which i really need
How to set these?

 

[Domenic]

An ActiveX combobox is an object that is held in a container called an oleobject. Therefore, to set the properties for the combobox itself, you'll need to refer to the Object property of the OLEObject...

newOleObject.[COLOR=#ff0000]Object[/COLOR].ColumnCount = 5

You can set multiple properties as follows...

    Dim newOleObject As OLEObject    
    Set newOleObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=Range("a10").Left, Top:=Range("a10").Top, Width:=48, Height:=15)
    With newOleObject
        .LinkedCell = "E" & r + 2
        .ListFillRange = Range("H" & r + 2).Value
        .Name = "Box"
[COLOR=#ff0000]        With .Object[/COLOR]
[COLOR=#ff0000]            .ColumnCount = 5[/COLOR]
[COLOR=#ff0000]            .ColumnHeads = True[/COLOR]
[COLOR=#ff0000]            '.ColumnWidths ...[/COLOR]
[COLOR=#ff0000]            .ListRows = 10[/COLOR]
[COLOR=#ff0000]        End With[/COLOR]
    End With

 

[BVOPP]

Thanks a lot Domenic !!