Can I rename every combobox with same name in exce

Question

There are a lot of combobox in sheet and they are appending dynamic. But all of combobox' assignments are same. They will run a function in macro. Can I rename all of combobox with same name? Or how can I do what I want?

Sub ekranadi()
  Dim mainworkBook As Workbook
  Set mainworkBook = ActiveWorkbook
  For i = 1 To mainworkBook.Sheets.Count
    If Left(mainworkBook.Sheets(i).Name, 5) = "Ekran"
     Then ComboBoxEkranAdı.AddItem mainworkBook.Sheets(i).Name
    End If
  Next i
End Sub

Answer 1

If my understanding of your requirement is correct, the macro below will show you how to achieve the effect you seek.

A user form has a collection named Controls that contains every control on the form. Instead of MyControl.Name you can write Controls(6).Name if 6 is the index number within Controls of MyControl.

The macro below outputs the index number, type name and name of every control on a form. If the control is a ComboBox, it adds three items to it with each item value being unique to the box.

Edit

Sorry I did not read your question carefully enough. I do not use controls on worksheets because I consider controls on user forms to be more powerful and more convenient. Controls on worksheets are further complicated by having two types: those loaded from the Controls toolbox and those loaded from the Forms toolbox. Functionality depends on which type you have.

To test the new macro DemoWorksheet, I loaded worksheet "Test" with both types of control. The macro shows how to fill both type of combo box via their collections.

Option Explicit
Sub DemoUserForm()

  Dim InxCtrl As Long

  Load UserForm1

  With UserForm1

    For InxCtrl = 0 To .Controls.Count - 1
      Debug.Print Right(" " & InxCtrl, 2) & " " & _
                  Left(TypeName(.Controls(InxCtrl)) & Space(10), 15) & _
                  .Controls(InxCtrl).Name
      If TypeName(.Controls(InxCtrl)) = "ComboBox" Then
        With .Controls(InxCtrl)
          .AddItem InxCtrl & " A"
          .AddItem InxCtrl & " B"
          .AddItem InxCtrl & " C"
        End With

      End If

    Next

  End With

  UserForm1.Show

End Sub
Sub DemoWorksheet()

  Dim Inx As Long

  With Worksheets("Test")

    Debug.Print "Shapes.Count=" & .Shapes.Count
    Debug.Print "OLEObjects.Count=" & .OLEObjects.Count

   For Inx = 1 To .Shapes.Count
     With .Shapes(Inx)
       Debug.Print "S " & Right(" " & Inx, 2) & " ShapeType=" & _
                   ShapeTypeName(.Type) & " Name=" & .Name
       If .Type = msoFormControl Then
         Debug.Print "     FormControlType=" & FormControlTypeName(.FormControlType)
         If .FormControlType = xlDropDown Then
           .ControlFormat.AddItem "S " & Inx & " A"
           .ControlFormat.AddItem "S " & Inx & " B"
           .ControlFormat.AddItem "S " & Inx & " C"
           .ControlFormat.DropDownLines = 3
         End If
       End If
     End With
   Next
   For Inx = 1 To .OLEObjects.Count
     With .OLEObjects(Inx)
       Debug.Print "O " & Right(" " & Inx, 2) & " OleType=" & _
                   OLETypeName(.OLEType) & " Name=" & .Name
       If Left(.Name, 8) = "ComboBox" Then
         .Object.AddItem "O " & Inx & " A"
         .Object.AddItem "O " & Inx & " B"
         .Object.AddItem "O " & Inx & " C"
       End If
     End With
   Next

  End With
End Sub
Function FormControlTypeName(ByVal FCType As Long) As String

  Dim Inx As Long
  Dim TypeName() As Variant
  Dim TypeNumber() As Variant

  TypeName = Array("ButtonControl", "CheckBox", "DropDown", "EditBox", "GroupBox", _
                   "Label", "ListBox", "OptionButton", "ScrollBar", "Spinner")
  TypeNumber = Array(xlButtonControl, xlCheckBox, xlDropDown, xlEditBox, xlGroupBox, _
                     xlLabel, xlListBox, xlOptionButton, xlScrollBar, xlSpinner)

  For Inx = 0 To UBound(TypeNumber)
    If FCType = TypeNumber(Inx) Then
      FormControlTypeName = TypeName(Inx)
      Exit Function
    End If
  Next

  FormControlTypeName = "Unknown"

End Function
Function OLETypeName(ByVal OType As Long) As String

  If OType = xlOLELink Then
    OLETypeName = "Link"
  ElseIf OType = xlOLEEmbed Then
    OLETypeName = "Embed"
  ElseIf OType = xlOLEControl Then
    OLETypeName = "Control"
  Else
    OLETypeName = "Unknown"
  End If

End Function
Function ShapeTypeName(ByVal SType As Long) As String

  Dim Inx As Long
  Dim TypeName() As Variant
  Dim TypeNumber() As Variant

  TypeName = Array("AutoShape", "Callout", "Canvas", "Chart", "Comment", "Diagram", _
                   "EmbeddedOLEObject", "FormControl", "Freeform", "Group", "Line", _
                   "LinkedOLEObject", "LinkedPicture", "Media", "OLEControlObject", _
                   "Picture", "Placeholder", "ScriptAnchor", "ShapeTypeMixed", _
                   "Table", "TextBox", "TextEffect")
  TypeNumber = Array(msoAutoShape, msoCallout, msoCanvas, msoChart, msoComment, msoDiagram, _
                   msoEmbeddedOLEObject, msoFormControl, msoFreeform, msoGroup, msoLine, _
                   msoLinkedOLEObject, msoLinkedPicture, msoMedia, msoOLEControlObject, _
                   msoPicture, msoPlaceholder, msoScriptAnchor, msoShapeTypeMixed, _
                   msoTable, msoTextBox, msoTextEffect)


  For Inx = 0 To UBound(TypeNumber)
    If SType = TypeNumber(Inx) Then
      ShapeTypeName = TypeName(Inx)
      Exit Function
    End If
  Next

  ShapeTypeName = "Unknown"

End Function