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.
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