How to Initialize ComboBox Dropdown?
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How to Initialize ComboBox Dropdown?

  1. #1
    Guest

    Default

     
    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code worked for me.

    Private Sub ComboBox1_DropButt*******()
    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)?


  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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?


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com