how to load the combobox and then how to use the s

Question

a question is : how to load a or give value to a combo box list and then call it in my worksheet and get the selected value from it ?i have a module that i want to call my userform1 which include the combobox in it . but when i debug the program it is just a show of the combo box . i think it doesn't do anything ... thanks for your time ..this is the code for user form:

Private Sub UserForm_Initialize()

With ComboBox1

    .AddItem "weibull"
    .AddItem "log-normal"
    .AddItem "gambel"
    .Style = fmStyleDropDownList

End With
End Sub

and this is how i ask in my sub to show the combobox:

UserForm1.Show
If ComboBox1.ListIndex = -1 Then
MsgBox "There is no item currently selected.", _
vbInformation, _
"Combo Box Demo"
Exit Sub
End If
 MsgBox "You have selected " & ComboBox1.List(ComboBox1.ListIndex) & "." & vbNewLine _
& "It has " & ComboBox1.ItemData(ComboBox1.ListIndex) ", _
vbInformation, _
"Combo Box Demo"

the second part is what i found in net , but it made the program at least to show the combo box !

Answer 1

You are trying to access a control when the userform is already closed. And I say closed becuase you are not using vbmodeless to show the form. So the only way the next line after that can run is when the form is closed. Here is what I recommend.

Declare public variables in a module which will hold the relevant values when the useform closes and then use that later. For example

Paste this code in the userform

Option Explicit

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "weibull"
        .AddItem "log-normal"
        .AddItem "gambel"
        .Style = fmStyleDropDownList
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If ComboBox1.ListIndex <> -1 Then
        SelectItem = ComboBox1.List(ComboBox1.ListIndex)
        pos = ComboBox1.ListIndex + 1
    End If
End Sub

And paste this in a module

Option Explicit

Public SelectItem As String, pos As Long

Sub Sample()
    '
    '~~> Rest of your code
    '

    SelectItem = "": pos = 0
    UserForm1.Show

    If pos = 0 Then
        MsgBox "There is no item currently selected.", _
        vbInformation, "Combo Box Demo"
        Exit Sub
    End If

    MsgBox "You have selected " & SelectItem & "." & vbNewLine & _
    "It is at position " & pos, vbInformation, "Combo Box Demo"

    '
    '~~> Rest of your code
    '
End Sub

Also

There is no .Itemdata property of the Combobox. It is available in VB6 but not in VBA. With .Itemdata property of the Combobox, I guess you were trying to get the position?

Answer 2

I thought Siddharth's answer was pretty clear particularly as it was posted from a mobile! However, I had an email from the OP saying he did not understand the answer. I provided the following background which was apparently sufficient to allow him to understand Siddharth's answer and solve his problem. I post it here for the benefit of any other visitor who needs more background on forms than Siddharth provides.

If you select VBA Help and type “userform show” you will get a description of the Show command.

If your user form is named “UserForm1”, you can have statements:

1. UserForm1.Show
2. UserForm1.Show vbModal
3. UserForm1.Show vbModeless

Statements 1 and 2 are equivalent.

The choice of VbModal or vbModeless completely changes the way the user form is controlled.

If a form is shown modeless, the user can see it but cannot access it. If I have a macro that takes a long time, I will use a modeless form to show progress. If I am working down the rows of a large worksheet I might have a form containing:

I am working on row nnnn of mmmm

Each of the boxes is a label. I set the value of the label containing “mmmm” to the number of rows when I start the macro. I set the value of the label containing “nnnn” to the row number at the start of each repeat of my loop. The user sees:

     I am working on row    1 of 5123
then I am working on row    2 of 5123
then I am working on row    3 of 5123
and so on.

If it takes the macro five minutes to process every row, this tells the user that something is happening. Without the form, the user might think the macro had failed. With the form, the user knows the macro is busy and they have time to get a fresh cup of coffee.

On the other hand, if the form is shown modal, the macro stops until the user does something that closes or unloads the form with a statement such as:

Unload Me

The positioning of this statement depends on your form. I normally have a Submit button that saves information before ending with this statement.

Once the Unload Me statement is executed, the macro restarts at the statement after the Show statement. When the macro restarts, the form has gone. This is why the form must save anything the macro needs in global variables.