using variables in listbox
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
Harald Staff wrote:
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" wrote in message
...
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
Harald Staff wrote:
"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" wrote in message
...
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
Harald Staff wrote:
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" wrote in message
...
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
|