Listbox help

[Todd Huttenstine]

Dim Rng As Range

Dim Ndx As Long
Set Rng = Range("Z1")
With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Rng.Value = .List(Ndx)
Set Rng = Rng(2, 1)
End If
Next Ndx
End With

Above is the code I am using... It populates with the
values found in Range Z1:Z26.

Is there a way to when I pull up the
listbox, that it can look in that range(Z1:Z26) and if it
finds a value that matches one of the list box values, it
can put a check next to it automatically in the listbox.
For instance, lets say I open the form and put a check
mark next to "Todd", then I close the form and later on
reopen it, I would like to see the check mark already next
to "Todd" in the listbox when I pull up the userform.

Also lets say later on I open the form
and try to check more values in it. If I do that now, it
just overwrites whats currently in the range. I want it
to be that when I check the checkboxes and then click ok
button, that the code not does not overwrite values that
are in the range.

Thank you

Todd Huttenstine

 

[Chip Pearson]

Todd,

To automatically select the values in the list box that appear in Z1:Z26,
use the following code:

Private Sub UserForm_Initialize()
Dim Rng As Range
Dim Ndx As Long
'
' select values in Z1:Z26
'
With Me.ListBox1
For Each Rng In Range("Z1:Z26")
For Ndx = 0 To .ListCount - 1
If Rng.Text = .List(Ndx) Then
.Selected(Ndx) = True
Exit For
End If
Next Ndx
Next Rng
End With
End Sub

To append the selected values at the end of the existing entries in column
Z, uses

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim Ndx As Long
Set Rng = Cells(Rows.Count, "Z").End(xlUp)(2, 1)
With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Rng.Value = .List(Ndx)
Set Rng = Rng(2, 1)
End If
Next Ndx
End With
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[baha]

Hi Everyone,
I just joined this group and I am very new beginner on VBA, but I want
to improve myself. I tried to read as many mail as I could related to
list box options, well I could not get what I really want. Any kind of
help will be appreciated greatly. Thanks in advance…
Now I have a spread sheet which consists of multiple entries for total
amount of 2000 crew. All crew has and id number and other details on
INFO sheet. Well, I need to key-in some merit and demerit records for
each individual crew which I managed to do on one user form. I key in
the id number it gives me the details of that individual crew and I
submit the merit-demerit record to one sheet named as DMRECORD. Until
now everything works fine but I have a big problem now. If I want to
check each individual crew record I can do by excel list box option by
using some address, index, indirect formula. But I want to view those
records by using user form and list box. Let’s call the second
user from as UserForm2. One text box for ID numbers one command button
to call all the entries. I can call all the entries except list box
and the text box5. I want list box1 to display (that can be combo box1
as well) all the merit and demerit record for that employee. On
DMRECORD sheet I created rows like this:
A (ID) B (NAME) C (M/DRECORD) D
(DETAILS)
1 32 john merit good job
he performed a good job
2 32 john demerit lateness
he was late by 2 hrs
3 334 rose merit good sales
she sold four cars today
4 333 mic demerit cust. Service
bad cust. service
5 32 john merit good job
he sold five cars
6 334 rose demerit lateness
she was late by 30 min.
….And so on. If the records are stable I can call them but every
time I key in new record and I want to check next day. What I want is
when I type 32 on text box1 list box1 to show a list
Merit good job
Demerit lateness
Merit good job
When I click to merit good job (first in the list box) text box 5
shall display the details on the record like
He performed a good job.
It is really hard for me to create a list box like that the source
every time changes coz the next day can be different entries. As I
told you we can use a list box feature in excel that works fine. But
how about VBA user forms? Is there any way to do it?
Sorry the massage is too long but I wanted to explain everything. I
swear I won’t ask anymore question such as that but I
desperately need some help.
By the way I asked to my friends they said I could only get help from
you masters
Warmest regards and thanks.

Baha