using variables in listbox

[John]

My first attempts at using listboxes.

have listbox in a user form with multiple selections.

Is there a way to pass the selections out of the listbox? Right now when
I close the listbox all the variables holding the selections disappear too.

Or to do more with the selections do I just hide the listbox?

Thanks

JOhn

 

[Harald Staff]

Hi John

Sure. Question is, pass them from the listbox and to what ?

General way to scan the listbox for selections:

Private Sub CommandButton1_Click()
Dim L As Long
For L = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(L) = True Then
MsgBox Me.ListBox1.List(L)
End If
Next
End Sub

HTH. Best wishes Harald

 

[John]

Here's my problem. The program runs until "UserForm1.Show" which opens
the listbox and everything works great. I run code similar to what you
offered below and have my data in an array.

But now what? I want to close the listbox and use the data to do other
things. If I do "Unload UserForm1" all the variables are wiped out.

I have a command button "OK" on the userform but the manual I used
(Excel VBA Programming for Dummies) didn't say what to use for it. The
only thing I know is "unload userform1."

Thanks

 

[Dave Peterson]

You could hide the userform instead of unloading it.

Or you could declare your array (is that what you're using?) in a General module
and make it public:

In a general module:
Public myArr() as string

In the Ok button of the userform (or where you want it):

Private Sub CommandButton1_Click()
Dim L As Long
Dim iCtr as long

redim myArr(0 to me.listbox1.listcount - 1)

ictr = -1

For L = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(L) = True Then
ictr = ictr + 1
myarr(ictr) = Me.ListBox1.List(L)
End If
Next L

if ictr = -1 then
'nothing selected, what should happen here?
'stop the process and warn the user???
else
redim preserve myArr(0 to iCtr)
end if

unload me

End Sub

When the userform is unloaded, then the array will still exist.

 

[John]

I tried the public declaration in the general form
It won't let me do the redim... says it's already declared
My variable is Chosen(100). Public Chosen(100) as String

After unloading the userform the array data is still gone. Before
unloading the data is in there.

What is the command to hide the form? If nothing else works.

John

 

[Harald Staff]

"Other things" is essential here.

My crystal ball has its new year cleaning, so I am at the moment depending
on someone telling me details. If you know someone that can do that, please
post here.

Also, it would help to know what the listbox/userform contains and is meant
to do.

Best wishes Harald

 

[John]

Business is mental health clinic

The workbook holds client records. 1 worksheet per client. Each
worksheet holds record of interactions/durations for billing to
client/insurance/state or federal programs.

Data (date of session, time, type) is entered directly on the worksheets
right now. That's ok for individual sessions but is tedious for groups
because yo are entering the same data over and over for 10-15 clients.
Which clients are in group changes from day to day.

So: Want to present a list of clients eligible for group therapy: Office
manager clicks which were in a group, the date, and the duration and all
those records are automatically added to each client's record saving a
lot of time.

I set up a list box in a userform and got it populated with the right
names. Everything works just dandy except it looks crappy. I have to
take the entered data, do a check with the user that it is correct and
enter it into the worksheets while the listbox is still open
superimposed on a worksheet.

I want to either hide the userform or unload it. I can't seem to do either.

I've tried setting the variables as public in general and I've tried
..hide userform1 in the listbox routine. Nothing works

It's really a problem of making things look tidy and professional. The
mechanics work fine.

Hope this explains it.
John

 

[Dave Peterson]

Try
Public Chosen() as string

I tried the public declaration in the general form
It won't let me do the redim... says it's already declared
My variable is Chosen(100). Public Chosen(100) as String

After unloading the userform the array data is still gone. Before
unloading the data is in there.

What is the command to hide the form? If nothing else works.

John

 

[Harald Staff]

Hi John

See if this get you started:

Code in Module1 (or some other, a standard module from the Insert - Module
menu):

Option Explicit '****** top of module

Public Selections() As String

Sub MyProgram()
Dim i As Long
UserForm1.Show
For i = LBound(Selections) To UBound(Selections)
MsgBox Selections(i) & " was chosen"
'write stuff to the right sheet here instead of message
Next
End Sub

Code for the OK button in the userform module:

Private Sub CommandButtonOK_Click()
ReDim Selections(0)
Dim L As Long, i As Long
i = -1
For L = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(L) = True Then
i = i + 1
ReDim Preserve Selections(i)
Selections(i) = Me.ListBox1.List(L)
End If
Next
Unload Me
End Sub

HTH. Best wishes Harald

 

[John]

Yup... that helped. It's all working pretty well now. My only complaint
is that the listbox has a title of userform1. Up in the top blue part.
Is there a way to change that? Or do I have to start over and anme it
what I want from the start?

Thanks

John