Vb code for a drop list before sheet opens up.

    I'm looking for a vb drop down list message box code that will appear when opening up a sheet. I'm hoping to also have a cell a1 for example be populated with whatever the user selects from that vb dropdown list. Can this be done.

    Re: Vb code for a drop list before sheet opens up.


    Quote from Logit;781803

    .
    .
    .


    Resource here ---->>>> https://www.extendoffice.com/d…n-list-auto-populate.html


    Unfortunately the above link is not what I am trying to achieve. The link above has the user selecting from a drop down list that is on the sheet already. What I'm looking for is a message box to appear when opening the sheet. I need the user to select choices from the drop down list before being able to access the sheet. I would like cell A1 to be populated with the users selection if possible using some form of VB code to create the message box.

    Re: Vb code for a drop list before sheet opens up.


    From your description it seems you will be needing a UserForm for the dropdown box to reside on.


    #1 When the program first starts, the workbook will need to be "hidden" (Application.Visible = False) but the UserForm in view.


    #2 The user makes a selection from the dropdown on the userform.


    #3 The code connected to the dropdown brings the workbook into view (Application.Visible = True) and the userform becomes 'invisible' (Unload Me). Their selection from the drop down box
    is written to cell A1.


    Must it be a dropdown box or can it simply be a Listbox or Combobox that looks like a dropdown box ?

    Re: Vb code for a drop list before sheet opens up.


    [QUOTE=Logit;781811]From your description it seems you will be needing a UserForm for the dropdown box to reside on.


    #1 When the program first starts, the workbook will need to be "hidden" (Application.Visible = False) but the UserForm in view.



    Regarding the above. I really don't need for the entire workbook to be hidden. There are 30 sheets to the workbook. I only need the message box to appear when 1 certain sheet is being accessed. Is there a work around for that. To simplify.. the sheet in question doesn't really need to be hidden just as long as the message box appears on top of the sheet so that the user can select one of the options that will be there. And then after making that selection the user will then have access to the sheet. Remember ...I really need the user's selection to be populated in cell A1.

    Re: Vb code for a drop list before sheet opens up.


    You could use Worksheet_Activate to bring up a user form.

    Code
    Private Sub Worksheet_Activate()
    UserForm1.Show
End Sub


    Use ListBox1_Change to write to A1 and close the user form

    Code
    Private Sub ListBox1_Change()
    Range("A1").Value = ListBox1.Value
    Unload Me
End Sub


    See the attached example when activating sheet 3.

    Re: Vb code for a drop list before sheet opens up.


    The only thing I would add to NoSparks example is to include this code in the ThisWorkBook section:


    Code
    Option Explicit
Private Sub Workbook_Open()
    Worksheets("Sheet3").Activate
    UserForm1.Show
End Sub


    That way the workbook will open to the specific sheet you specify in code and automatically show the UserForm requiring the user to make a selection.


    NOTE: Always use OPTION EXPLICIT at the top of all of your Modules / Sheet code. It will help you identify errors in your code that may not be apparent at first.


    Merry Christmas !

Participate now!

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

Register Yourself Login