How to Initialize ComboBox Dropdown?

[Guest]

I've got a ComboBox ActiveX dropdown control on a worksheet that needs to be initialized with a dropdown list.

What's the best way to do this?

I've tried ComboBox1_GotFocus and ComboBox1_Click without any luck. I've been able to get the list to appear on the second click, but not on the first.

Also tried Worksheet_Activate, but it doesn't seem to work either.

Any suggestions? I know this must be easy.

 

[Juan Pablo González]

This code worked for me.

Private Sub ComboBox1_DropButtonClick()
Dim i As Integer

ComboBox1.Clear

For i = 1 To 4
ComboBox1.AddItem i
Next i
End Sub

You could also put it in the Workbook_Open() event, or in the Sheet_Activate() event, so it won't have to be recreated each time the user clicks on it.

 

[Juan Pablo González]

I mean like this:

Private Sub Workbook_Open()
Dim i As Integer

Sheet1.ComboBox1.Clear

For i = 1 To 4
Sheet1.ComboBox1.AddItem i
Next i
End Sub

 

[Matanuska]

Thanks!

I'm the Anonymous that asked the original question - I forgot to login. Sorry in advance for the length of this post.

Now I'm able to get the dropdown initialized with a Workbook_Open routine, but this brings up another problem. How do I copy a workbook code module from one workbook to another? (I know how to do this for regular modules - see below).

In my app I create a new workbook with a Sheets(Array("Sheet1", "Sheet2", "Sheet3").Copy method to copy selected sheets out of the master workbook.

This brings any sheet code along with the sheets into the new workbook. I get the module code I want into the new workbook by exporting from the old & importing into the new workbooks using:

ActiveWorkbook.VBProject.VBComponents("modMyModule").Export ("temp1.bas")

Then activate the new workbook & import:

Application.VBE.ActiveVBProject.VBComponents.Import ("temp1.bas")

I tried doing this using "ThisWorkbook" in place of "modMyModule" in the import/export routines above but when I import the workbook code into the new workbook it comes in as a class module rather than as the workbook module.

How do I copy code in "ThisWorkbook" (which contains the Workbook_Open code you helped me out with)?

 

[Ivan F Moala]

Try using this code...I have adapted it to
Juans 1st code...you should adj it to suit.

'here is a routine I did a while ago to do this;
'You will have to change it to reference the correct
'event & sheet
'===================================================================
'= Procedure: Modify_Modules =
'= Type: Subprocedure =
'= =
'= Purpose: Add a Procedure to a module including Sheets and =
'= Thisworkbook =
'= Parameters: None =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |13-May-00| Ivan F Moala | Created =
'===================================================================
Sub Modify_Event()

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim sTab As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
sTab = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub Workbook_Open()" & LF

'Your Procedure
Proc = "Dim i As Integer" & LF
Proc = Proc & _
"Sheet1.ComboBox1.Clear" & LF
Proc = Proc & _
sTab & "For i = 1 To 4" & LF
Proc = Proc & _
sTab & sTab & "Sheet1.ComboBox1.AddItem i" & LF
Proc = Proc & _
sTab & "Next i" & LF

'Use ActiveWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With

Set ModEvent = Nothing

End Sub

'Ivan

 

[Matanuska]

Thanks. This works well, even if it is a bit cumbersome.

I suppose it might be possible to automate this by writing VBA to export the text of the procedure out the original workbook, parse it into the format you suggest, then insert it as a new line.

Might not even have to parse it, just import as is?