Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help
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 "Todd Huttenstine" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox help
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 "Chip Pearson" wrote in message ... 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 "Todd Huttenstine" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Listbox B if LIstbox A equals | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |