[Excel/VBA]: How to populate ActiveX Combo box in Worksheet?

[prashantdixit]

Hi,

I have a worksheet name called "Import File" and there is a ActiveX combobox control "Start Time" in it.

Name of ActiveX combobox control: ComboBoxStartTime

This "Import File" sheet also contains import file utility through which i can import files in same workbook. After Importing Data file through this software, i am trying to:

** Populate "ComboBoxStartTime" ActiveX combobox control from Range of data present in recently imported sheet.

There is code in standard module as:

Public Sub FillStartTimeComboBox(Datasheet As Worksheet, ByVal FirstDataRow As Long)
 
Application.ScreenUpdating = False
    Dim myRange As Range
    Dim r As Range
    Dim LastDataRow As Long
 
    Datasheet.Activate
    LastDataRow = Cells(1000000, 1).End(xlUp).row
    'Set the range of cells, whose values will be put into the combobox
    Set myRange = Range(Cells(FirstDataRow, 1), Cells(LastDataRow, 1))
 
    'work through each cell in the range and put the value in the Spectrum combobox.
    Cells(FirstDataRow, 1).Select
        For Each r In myRange
            'add cell value to combobox list if it is not a blank cell.
            If r.Value <> "" Then
                Sheet1.ComboBoxStartTime.AddItem r.Value
            End If
        Next r
    Application.ScreenUpdating = True
End Sub
 

I am not sure in which worksheet/control event should i put this. I have called this function from Worksheet_Activate() event but in this case, code keeps running continuously. And if i put this in ComboBoxStartTime_Change(), combobox doesn't populate on selecting this worksheet and also everytime i select a value from starttime, the entire code runs and this makes process bit slower.


I'll be highly thankful if someone can help me on this.


Thanks
Prashant

[ADezii]

I think that you may be over complicating the issue. The following Code, activated from a Command Button on Sheet3, will populate a Combo Box on Sheet1 from a List in the Range A1:A10 on Sheet2. Only Cells that have a Value will be used to populate the Combo Box. The Code is placed in a Standard Code Module, has been simplified, and is Public in Scope. I see no need for Sub-Routine Arguments unless there are more than 1 Combo Boxes on multiple Worksheets.

Public Sub FillComboBoxStartTime()
Dim myRange As Excel.Range
Dim r As Excel.Range
 
Application.ScreenUpdating = False
 
Worksheets("Sheet1").Activate
 
'Set the range of cells, whose values will be put into the combobox
Set myRange = Worksheets("Sheet2").Range("A1:A10")
 
'Work through each cell in the Range and put the value in the Spectrum combobox.
For Each r In myRange
  'Add Cell Value to combobox list if it is not a blank cell.
   If r.Value <> "" Then
     Worksheets("Sheet1").ComboBoxStartTime.AddItem r.Value
   End If
Next r
 
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
  Call FillComboBoxStartTime
End Sub

Passing a Worksheet Name:

Public Sub FillComboBoxStartTime2(strWorksheet As String)
Dim myRange As Excel.Range
Dim r As Excel.Range
 
Application.ScreenUpdating = False
 
Worksheets(strWorksheet).Activate
 
'Set the range of cells, whose values will be put into the combobox
Set myRange = Worksheets("Sheet2").Range("A1:A10")
 
'Work through each cell in the range and put the value in the Spectrum combobox.
For Each r In myRange
  'Add Cell Value to combobox list if it is not a blank cell.
   If r.Value <> "" Then
     Worksheets(strWorksheet).ComboBoxStartTime.AddItem r.Value
   End If
Next r
 
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
  Call FillComboBoxStartTime2("Sheet1")
End Sub

[prashantdixit]

Hi,

Yeah this will work But it requires to have another button on sheet 3. I am trying to populate from same worksheet which contains ActiveX Combo control.

Thanks
Prashant Dixit

[ADezii]

This "Import File" sheet also contains import file utility

1. What, exactly, is the nature of this 'Import File Utility'?
2. What not use the native Combo Box Control as opposed to the ActiveX one?
3. Can the Combo Box be manually populated via a Command Button on the Import File Spreadsheet?

[prashantdixit]

Please see below answers to your questions:

1. What, exactly, is the nature of this 'Import File Utility'?
Ans: Import File sheet has a button "Import Data". Through this a form opens and user can import a TEXT/Log file. The data imported in a different sheet and in a sameworkbook. (This part has been done)
Second section in this Import File sheet has a ActiveX combobox. I need to import a value within a specific range from recenlty imported sheet. Let say A10-A555

2. What not use the native Combo Box Control as opposed to the ActiveX one?

Ans: I haven't tried this. Can you tell me how can i use native combobox to populate the required data.

3. Can the Combo Box be manually populated via a Command Button on the Import File Spreadsheet?
Ans: No. I want that as soon as data is imported then sheet refreshes and combobox is filled with required data

Thanks
Prashant

[prashantdixit]

Native combobox means the one stated as Form controls. If thats the case, then i have used and populate the values. But then i need to select any value from combobox and used this value to perform other tasks.

I don't how to store selected value from combobox in some other variable.

Thanks
Prashant Dixit

[ADezii]

I don't how to store selected value from combobox in some other variable.

The basic Syntax is as follows:

Dim strValue As String          'Local in Scope
 
'Depending on Context
strValue = Me.ComboBoxStartTime.Value
strValue = Worksheets("Sheet1").ComboBoxStartTime
 
If strValue <> "" Then
  MsgBox "Item Selected From Combo: " & strValue
Else
  MsgBox "No Item Selected!", vbExclamation, "Nothing Selected"
End If

[prashantdixit]

Thanks for your help