CODEWITHSUNDEEP
excelvbalistboxuserform
Simo Keng
Simo Keng

Reputation: 67

Set Listbox selection as variable

I want to create something like a "Patch" file which I can send to users and they use it to patch their existing templates.

What I'm trying to do is to have a userform with a listbox that shows all currently opened Excel files, users then select the file they want to patch and click a button to run the patch script.

Am very new to userforms and vba as a whole, and am having difficulty trying to set the 'Listbox1.Selection' as a variable that the subsequent patch code can refer to. My code currently for the userform/listbox is as below (It just allows selection of item:

Private Sub UserForm_Activate()

Dim wb As Workbook

For Each wb In Workbooks
    If Windows(wb.Name).Visible Then _
      ListBox1.AddItem wb.Name
Next

End Sub

Once users select the file, how do I go about setting that as a variable?

Upvotes: 0

Views: 2862

Answers (2)

T.M.
T.M.

Reputation: 9948

How do I go about setting that as a variable?

Private Sub doPatch()
With Me.ListBox1
    Dim currIndex&
    currIndex = .ListIndex             ' assign zerobased index number to variable

    ' how do I go about setting that as a variable?
      Dim currWB
      currWB = .List(currIndex, 0)     ' get chosen list element in column zero based on current index

    ' 'or simply:
    ' currWB = .Value                  ' sufficient in your case as only one column listed

    ' display both variables in immediate window of your VB Editor
      Debug.Print "zerobased Listindex#: " & currIndex & " ~> " & currWB

    ' do patch stuff...

End With
End Sub

Eventually ou could call the above procedure either by a command button and/or by doubleclick, e.g. via

Private Sub CommandButton1_Click()
    doPatch
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    doPatch
End Sub

Upvotes: 1

Mikku
Mikku

Reputation: 6654

Add a Command button to your UserForm and add the following code:

Private Sub CommandButton1_Click()

ActiveSheet.Range("A1").Value = ListBox1.Text


End Sub

That will print the Selected option to the A1. You can save it to variable to anything further you want.

Basically ListBox1.Text will give you the selected option.

Upvotes: 1

Related Questions