excelvbacomboboxactivex

Excel VBA Creating Comboboxes second time is throwing Unregonisable Error


In my project, I have a need to create ActiveX comboboxes and position them on worksheets at specified Range(). The First run of the Code works fine till second sheet and then throws error when Assigning the .Name to the Combobox. Here is the Code

Public Sub RakyComboboxes()

    Set cmb_Master_Data_Tech_Technology = GetOrCreateComboBox("Adm_Tech_Master_CRUD", "E4:H4", "cmb_Master_Data_Tech_Technology")
    Set cmb_Master_Data_Tech_Property_Technology = GetOrCreateComboBox("Adm_Tech_Property_Master_CRUD", "E4:H4", "cmb_Master_Data_Tech_Property_Technology")
    Set cmb_Master_Data_Org_HQ = GetOrCreateComboBox("Adm_Master_Data_Org_CRUD", "E4:H4", "cmb_Master_Data_Org_HQ")
    Set cmb_Master_Data_Org_Region = GetOrCreateComboBox("Adm_Master_Data_Org_CRUD", "E6:H6", "cmb_Master_Data_Org_Region")
    Set cmb_Master_Data_Org_Locality = GetOrCreateComboBox("Adm_Master_Data_Org_CRUD", "E8:H8", "cmb_Master_Data_Org_Locality")
    Set cmb_MasterTechDataForMappingTechnology = GetOrCreateComboBox("Adm_Tech_Locality_Mapping_CRUD", "E4:H4", "cmb_MasterTechDataForMappingTechnology")
    Set cmb_MasterDataForTechMappingHQ = GetOrCreateComboBox("Adm_Tech_Locality_Mapping_CRUD", "E5:H5", "cmb_MasterDataForTechMappingHQ")
    Set cmb_MasterDataForTechMappingRegion = GetOrCreateComboBox("Adm_Tech_Locality_Mapping_CRUD", "E6:H6", "cmb_MasterDataForTechMappingRegion")
    Set cmb_UserTechnologyTechnology = GetOrCreateComboBox("User_Interface_Technology_Data", "E4:H4", "cmb_UserTechnologyTechnology")
    Set cmb_UserTechnologyHQ = GetOrCreateComboBox("User_Interface_Technology_Data", "E6:H6", "cmb_UserTechnologyHQ")
    Set cmb_UserTechnologyRegion = GetOrCreateComboBox("User_Interface_Technology_Data", "E8:H8", "cmb_UserTechnologyRegion")
    Set cmb_UserTechnologyLocality = GetOrCreateComboBox("User_Interface_Technology_Data", "E10:H10", "cmb_UserTechnologyLocality")
       
End Sub

Public Function GetOrCreateComboBox(wsName As String, rngAddress As String, cmbName As String) As OLEObject
    Dim ws As Worksheet
    Dim cmb As OLEObject
    Dim cmbExists As Boolean
    
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(wsName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        ' Worksheet does not exist, return Nothing
        Exit Function
    End If
    ws.Activate
    ' Check if ActiveX combobox already exists on the worksheet
    On Error Resume Next
    Set cmb = ws.OLEObjects(cmbName)
    On Error GoTo 0
    
    cmbExists = Not cmb Is Nothing
    
    If Not cmbExists Then
        ' Create a new ActiveX combobox
        Set cmb = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                                   Left:=ws.Range(rngAddress).Left, _
                                   Top:=ws.Range(rngAddress).Top, _
                                   width:=ws.Range(rngAddress).width, _
                                   height:=ws.Range(rngAddress).height)
        cmb.name = cmbName
    End If
    
    ' Return the handle of the ActiveX combobox
    Set GetOrCreateComboBox = cmb
End Function

Attempting to Run RakyComboboxes() after deleting all ComboBoxes also does not help.


Solution

  • At least one of your control names is too long. Max is 32 I think.

    Sub Tester()
    
        Dim ws As Worksheet, o As Object, s As String, i As Long
        
        Set ws = ActiveSheet
        Set o = ws.OLEObjects(1)
        
        s = "cmb_MasterTechDataForMappingTechnology"
        
        For i = 20 To Len(s)
            Debug.Print i, Left(s, i)
            o.Name = Left(s, i)         'error at i=33
        Next i
    
    End Sub