get listbox value

[Gordonik]

I wanna get a value of selected item in a listbox. (the object is named "driver").
I am able to select one of two items:
"PostgreSQL Unicode(x64)"
"PostgreSQL ODBC Driver(Unicode)"
I need to use driver in an another code so I want to test how it works. In fact it does not work and msgbox driver returns blank/nothing
Here is may code:

Option ExplicitDim driver As Variant


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Call the UserForm1 when click a cell


Dim ListBox1 As ListBox


    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("C284")) Is Nothing Then
            With UserForm1.ListBox1
                .AddItem "PostgreSQL Unicode(x64)"
                .AddItem "PostgreSQL ODBC Driver(Unicode)"
            End With
            Call ShowUserForm1
        End If
    End If
End Sub


Sub ShowUserForm1()


'show userform1 in the middle of Excel window
With UserForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With
    UserForm1.ListBox1.Value = driver
    msgbox driver
End Sub

I struggle what is wrong here?

 

[mrmmickle1]

I think you have mixed up a few things.

Put this in the worksheet code module:

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_SelectionChange([COLOR=#0000ff]ByVa[/COLOR]l Target [COLOR=#0000ff]As[/COLOR] Range)
    [COLOR=#0000ff]If[/COLOR] Selection.Count = 1 [COLOR=#0000ff]Then[/COLOR]
        If [COLOR=#0000ff]Not [/COLOR]Intersect(Target, Range("C284"))[COLOR=#0000ff] Is Nothing Then[/COLOR]
           [COLOR=#0000ff] Call [/COLOR]ShowUserForm1
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

This goes in a regular code module:

[COLOR=#0000ff]Sub[/COLOR] ShowUserForm1()
    UserForm1.Show
[COLOR=#0000ff]End Sub[/COLOR]

This goes in the UserForm Code Module:

[COLOR=#0000ff]Private Sub[/COLOR] UserForm_Initialize()

  [COLOR=#008000]  'Code that manipulates the userform typically goes in the userform code module
    'The Initialize event occurs when the userform is launched....[/COLOR]
[COLOR=#0000ff]    With [/COLOR]Me
      .StartUpPosition = 0
      .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
      .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
      .ListBox1.AddItem "PostgreSQL Unicode(x64)"
      .ListBox1.AddItem "PostgreSQL ODBC Driver(Unicode)"
[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[Gordonik]

Good to know mrmmickle1 .
But how to get a value of selected item in a listbox?

 

[mikerickson]

You could put code like this in the user form module. The user form has a listbox, ListBox1, and two command buttons, butOK and butClose

' in Userform code module

Private Sub butClose_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

Public Function ChooseFromList(ParamArray ListItems() As Variant) As String
    With Me
        .ListBox1.List = ListItems
        .Show
    End With
    With UserForm1.ListBox1
        If .ListIndex <> -1 Then
            ChooseFromList = .List(.ListIndex)
        End If
    End With
    
    Unload UserForm1
End Function

Then call it with code like this. Note that if the user cancels, or corner clicks or doesn't make a selection, then Userform1.ChooseFromList returns "".

Sub test()
    Dim userSelection As String
     
    userSelection = UserForm1.ChooseFromList("PostgreSQL Unicode(x64)", "PostgreSQL ODBC Driver(Unicode)")
    
    MsgBox userSelection
End Sub

The code in the OP doesn't show what you want to do with the selection the user made

 

[Gordonik]

@ mikerickson
I follow your suggestions and even if I select an item, MsgBox shows blank.
Here is how my user form looks like:

This is what I have now in my userform module:

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Call UploadToDatamart
End Sub

Private Sub UserForm_Initialize()
'The Initialize event occurs when the userform is launched....
    With Me
      .StartUpPosition = 0
      .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
      .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
      .ListBox1.AddItem "PostgreSQL Unicode(x64)"
      .ListBox1.AddItem "PostgreSQL ODBC Driver(Unicode)"
    End With
End Sub
 
Public Function ChooseFromList(ParamArray ListItems() As Variant) As String
    With Me
        .ListBox1.List = ListItems
        .Show
    End With
    With UserForm1.ListBox1
        If .ListIndex <> -1 Then
            ChooseFromList = .List(.ListIndex)
        End If
    End With
End Function

 

[mikerickson]

You can remove the ListBox1.AddItem lines from Initalize and change this code

Public Function ChooseFromList(ParamArray ListItems() As Variant) As String
    With Me
        .ListBox1.List = ListItems
        .Show
    End With
    With UserForm1.ListBox1
        If .ListIndex <> -1 Then
            ChooseFromList = .List(.ListIndex)
            ChooseFromList = ChooseFromList & "," & TextBox1.Text & "," & TextBox2.Text
        End If
    End With
End Function

Change TextBox1 and TextBox2 to match the names of your controls.

Use the Test sub I posted above to see what the userform returns (a comma delimited string) and then break that string down to get the results that you need.

 

[Gordonik]

You can remove the ListBox1.AddItem lines from Initalize

this left my userform without items:

 

[Gordonik]

and the test code... reopens the userform again.

Sub test()
Dim userSelection As String
     
    userSelection = UserForm1.ChooseFromList("PostgreSQL Unicode(x64)", "PostgreSQL ODBC Driver(Unicode)")
    
    MsgBox userSelection
End Sub

 

[mikerickson]

That sounds like what you want.
Run Test, the user form opens and, unless the user cancels, their selection from the listbox is returned to the calling routine and (in that example) displayed in a message box.