Initialization a VBA combox in worksheet?

  • I have a combox in worksheet, not userform. I want to initialize it when I open the workbook.
    Maybe I can do it on the workbook open event. but the problem is the number of worksheets and the combox in each worksheets may vary.
    So I think the best way to initialize is do it per sheet.
    I tried the worksheet_active events, but this event is not triggered when the workbook is open.


    Sorry for my poor english. In brief, my question is:
    How to initialized variables and controls in a worksheet? in which event?

  • Re: Initialization a VBA combox in worksheet?


    Hi zsfeng,


    If you put the code in the worksheet_activate then you will need code to ensure it doesn't run the code every time you change sheets.
    That said even with the code in the worksheet_activate you must know the dimensions of your problem, what comboboxes to fill and with what. So why not do it in the workbook open event.


    Maybe you could post an example to help explain.

  • Re: Initialization a VBA combox in worksheet?


    Thank you for your reply.
    My situation is:
    there are several different types of worksheet in the workbook. I want to write the initial code for each type, so that all the functions will retain there when I dupilicate the sheets.
    Now my solution is : i put them in the worksheet_active event, but I check if the controls are initialized before the real initialization. And i force to activate all the sheets one by one at workbook_open event.

  • Re: Initialization a VBA combox in worksheet?


    If you put the code that populates the controls in a standard module then you can call that from the worksheet activate events
    This bit of code will tell which sheet is the active one when opened.

    Code
    Private Sub Workbook_Open()
    
    
        MsgBox "Activesheet on open is" & ActiveSheet.Name
        ' call appropriate sheets initialize routine
    
    
    End Sub
  • Re: Initialization a VBA combox in worksheet?


    I have the same situation but I'm still stumped. I have a ComboBox in Sheet1. The VBA code in Sheet1 includes the following subs:


    Worksheet_Activate()
    ComboBox1_Change()


    Since Worksheet_Activate() doesn't fire on file open I put a sub into ThisWorkbook: Workbook_Open()


    I haven't been able to call Worksheet_Activate() from Workbook_Open(). I get an error message that the sub is not defined. The only way I've been able to get this to work is to put code into Workbook_Open() that activates Sheet2 then activates Sheet1. However, that seems like a crutch rather than the correct way to initialize the ComboBox.

  • Re: Initialization a VBA combox in worksheet?


    Hi,


    How about this, with a listbox on sheet1 and sheet2.


    Thisworkbook code module.

    Code
    Private Sub Workbook_Open()
        ActiveSheet.Trigger
    End Sub


    Sheet1 code module


    Sheet2 code module


    The public routine Trigger is run when the workbook opens.
    Thre routine the runs the local worksheet_active routine to populate the listbox.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!