Change array values depending on selections in listbox on a userform

Question

I want to be able to change array values from my sub that runs a userform. How do I go about doing this?

Here is an example of what I have:

A = [25, 50, 75, 100]
UserForm1.Show

UserForm1 contains a listbox with options 1, 2 or 3.
By selecting "1", "2,", "3", or multiple combinations of them I want to change the value of A accordingly.

If "1" is selected Then
A(0) = 10
If "2" is selected Then
A(1) = 0
If "3" is selected Then
A(2) = 1000

How would I make this happen? Thanks in advance for any help.

"1" will always =True. Same for "2" and "3", etc. Do you mean if the item is selected in the listbox? — David Zemens
– David Zemens, Commented Jul 5, 2017 at 18:41

David Zemens · Accepted Answer · 2017-07-05 19:39:37Z

1

This is tested in a ListBox_AfterUpdate() event. Get a handle on the ListBox object (assigned to variable lb) then iterate the items in the lb.List, checking if each is selected. If selected, then assign a positional value to the array A.

The array A is declared as a module/user-form scoped variable here, and assigned default/initial values during the form's _Initialize event. Those values are then changed if the user makes a selection(s) in the ListBox.

Option Explicit
Dim A()
Private Sub UserForm_Initialize()
'Assigns initial values to your array:
A = Array(25, 50, 75, 100)
'Assigns the default ListBox items:
Me.ListBox1.List = Array("1", "2", "3")
End Sub
Private Sub ListBox1_AfterUpdate()
Dim lb As MSForms.ListBox
Dim i As Long, v As Long
Set lb = Me.ListBox1 '# Modify as needed
For i = 0 To lb.ListCount - 1
    If lb.Selected(i) Then
        Select Case lb.List(i)
            Case "1"
                v = 10
            Case "2"
                v = 0
            Case "3"
                v = 1000
        End Select
        'confirm prev & new values for array:
        MsgBox (A(i) & " will be changed to: " & v)
        A(i) = v
        'Confirm the value in array has changed
        MsgBox (A(i))
    End If
Next
End Sub

Depending on where/when you initialize the A array, you could modify this and invoke it from the ListBox_Change or ListBox_AfterUpdate event, or call it from another control's event procedure as needed.

edited Jul 5, 2017 at 19:39

answered Jul 5, 2017 at 18:52

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

N. Whyter Over a year ago

This looks like just what I need. Would I put this code in the sub produced from double clicking the listbox? How do I call A array so its data can be altered?

David Zemens Over a year ago

Where you would put it depends on what you're trying to do. I can't answer that for you. I don't understand your second question. Do you understand variable scope in VBA? If not, please research it and use appropriate scope. Probably you want a public variable in the UserForm module, but again, that depends on what you're trying to do.

David Zemens Over a year ago

If you want to call this code from the ListBox, I would suggest the Change or AfterUpdate event, probably not the Click event.

Collectives™ on Stack Overflow

Change array values depending on selections in listbox on a userform

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related