469,270 Members | 1,153 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub FillStartTimeComboBox(Datasheet As Worksheet, ByVal FirstDataRow As Long)
  2.  
  3. Application.ScreenUpdating = False
  4.     Dim myRange As Range
  5.     Dim r As Range
  6.     Dim LastDataRow As Long
  7.  
  8.     Datasheet.Activate
  9.     LastDataRow = Cells(1000000, 1).End(xlUp).row
  10.     'Set the range of cells, whose values will be put into the combobox
  11.     Set myRange = Range(Cells(FirstDataRow, 1), Cells(LastDataRow, 1))
  12.  
  13.     'work through each cell in the range and put the value in the Spectrum combobox.
  14.     Cells(FirstDataRow, 1).Select
  15.         For Each r In myRange
  16.             'add cell value to combobox list if it is not a blank cell.
  17.             If r.Value <> "" Then
  18.                 Sheet1.ComboBoxStartTime.AddItem r.Value
  19.             End If
  20.         Next r
  21.     Application.ScreenUpdating = True
  22. End Sub
  23.  

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
Sep 28 '10 #1
7 17252
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub FillComboBoxStartTime()
  2. Dim myRange As Excel.Range
  3. Dim r As Excel.Range
  4.  
  5. Application.ScreenUpdating = False
  6.  
  7. Worksheets("Sheet1").Activate
  8.  
  9. 'Set the range of cells, whose values will be put into the combobox
  10. Set myRange = Worksheets("Sheet2").Range("A1:A10")
  11.  
  12. 'Work through each cell in the Range and put the value in the Spectrum combobox.
  13. For Each r In myRange
  14.   'Add Cell Value to combobox list if it is not a blank cell.
  15.    If r.Value <> "" Then
  16.      Worksheets("Sheet1").ComboBoxStartTime.AddItem r.Value
  17.    End If
  18. Next r
  19.  
  20. Application.ScreenUpdating = True
  21. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.   Call FillComboBoxStartTime
  3. End Sub
Passing a Worksheet Name:
Expand|Select|Wrap|Line Numbers
  1. Public Sub FillComboBoxStartTime2(strWorksheet As String)
  2. Dim myRange As Excel.Range
  3. Dim r As Excel.Range
  4.  
  5. Application.ScreenUpdating = False
  6.  
  7. Worksheets(strWorksheet).Activate
  8.  
  9. 'Set the range of cells, whose values will be put into the combobox
  10. Set myRange = Worksheets("Sheet2").Range("A1:A10")
  11.  
  12. 'Work through each cell in the range and put the value in the Spectrum combobox.
  13. For Each r In myRange
  14.   'Add Cell Value to combobox list if it is not a blank cell.
  15.    If r.Value <> "" Then
  16.      Worksheets(strWorksheet).ComboBoxStartTime.AddItem r.Value
  17.    End If
  18. Next r
  19.  
  20. Application.ScreenUpdating = True
  21. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.   Call FillComboBoxStartTime2("Sheet1")
  3. End Sub
Sep 28 '10 #2
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
Sep 28 '10 #3
ADezii
8,800 Expert 8TB
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?
Sep 28 '10 #4
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
Sep 28 '10 #5
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
Sep 28 '10 #6
ADezii
8,800 Expert 8TB
I don't how to store selected value from combobox in some other variable.
The basic Syntax is as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim strValue As String          'Local in Scope
  2.  
  3. 'Depending on Context
  4. strValue = Me.ComboBoxStartTime.Value
  5. strValue = Worksheets("Sheet1").ComboBoxStartTime
  6.  
  7. If strValue <> "" Then
  8.   MsgBox "Item Selected From Combo: " & strValue
  9. Else
  10.   MsgBox "No Item Selected!", vbExclamation, "Nothing Selected"
  11. End If
Sep 28 '10 #7
Thanks for your help
Sep 29 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dave McKie | last post: by
1 post views Thread by Paul | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.