PDA

View Full Version : [SOLVED] List box to populate,command button to highlight & delete



stapuff
10-12-2004, 11:18 AM
I has posted this request for help on Mr. Excel. As of yet no help.

Any help would be greatly appreciated.

Thanks,

Kurt

http://www.mrexcel.com/board2/viewtopic.php?t=109994

Zack Barresse
10-12-2004, 01:58 PM
To go to a specified sheet from a list box, I use a double click event ...



Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Sheets(ListBox1.Text).Activate
Unload shtNav
End Sub

Just make sure you use the .Text instead of .ListIndex and such. Then you should just be able to add your population to listbox 2 after that. If you need additional, just be sure and specify exactly what ListBox2 is (Controls/ActiveX, Forms, UF, etc). You can post zipped files here also Kurt, if that'd help us any. :)

An example of such a useform (to navigate to sheets) can be found here ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=130

You can add the above code to it and get that much more functionality out of it.

iwrk4dedpr
10-12-2004, 07:27 PM
Your request sounds easy enough. However, having read your post on Mr.Excel I'm a bit baffled as to what you want to accomplish.

Ok from your post on the other forum:


Private Sub UserForm_Initialize()
Dim Ws As Worksheet
Me.ListBox1.Clear
Workbooks.Open ("F:\Customer Service\Expedites.xls")
For Each Ws In ActiveWorkbook.Worksheets
Me.ListBox1.AddItem Ws.Name
Next Ws
End Sub


Ok so when the user form initializes it opens the expedite.xls file and then loads the worksheet names into the form.

Now when the user clicks on a sheet name you want ListBox2 to be populated with the data in the Range("C2:C__") of the selected sheet.



Private Sub ListBox1_Click(ByVal Cancel As MSForms.ReturnBoolean)
' Local Variables
' Step 1 : Read the data from C2:C__ of selected sheet
For each cell in Worksheets(Me.ListBox1.Value).Range("C2:C65536")
IF cell = "" then exit for
me.ListBox2.AddItem Cell.Value
Next Cell
End Sub


Then after filling listbox2 and the user selects a value you want to find that corresponding value in the the Column M. Once it finds that value you want it to highlight Cells A to O of that row and clear the cell contents in cell M.



Private Sub ListBox2_Click(ByVal Cancel As MSForms.ReturnBoolean)
' Local Variables
' Step 1 : Read the data from C2:C__ of selected sheet
For each cell in Worksheets(Me.ListBox1.Value).Range("C2:C65536")
IF cell = "" then exit for
if cell.Offset(0,10) = me.ListBox2 then
cell.Offset(0,-2).Range("A1:O1").interior.Colorindex = 3
cell.offset(0,10).Clearcontents
exit for
end if
Next Cell
End Sub


Now this code was written entirely here. It's not tested nor can I vouch for it's ability to run without errors. :dunno However, it SHOULD do what you requested.


Regards,
Barry

stapuff
10-13-2004, 05:36 AM
Barry -

You almost have what I am looking for.

Userform_Initialize works as needed.

ListBox1 now works (thank you), however, I had to change the first line to Private Sub ListBox1_Click(). Was getting errors.

ListBox2 - Does not work as needed.

"Then after filling listbox2 and the user selects a value you want to find that corresponding value in the the Column M. Once it finds that value you want it to highlight Cells A to O of that row and clear the cell contents in cell M."

What it should be is - after filling listbox2 and the user selects a value you want to on CommandButton1_Click go to that value in the the Column C. Once it finds that value you want it to highlight Cells A to O of that row and clear the cell contents in cell M."

Thanks Barry - I truly appreciate your time. I will go back to Mr. Excel post and look over what was written.

firefytr - the zipping of files sounds like a good idea. I will need to look at doing that in the future. I will also need to start making examples. I was informed(warned) by my company not to post anything that reflects actual business information on the forum's for security issues so that is why I have not been zipping or Colo posting.

Again I thank both of you for your support and patience,


Kurt

stapuff
10-13-2004, 06:18 AM
I changed the code to the following:



Private Sub ListBox2_Click()
' Local Variables
' Step 1 : Read the data from C2:C__ of selected sheet
For Each cell In Worksheets(Me.ListBox1.Value).Range("C2:C65536")
If cell = "" Then exit For
If cell = me.ListBox2 Then
cell.Range("A:O").interior.Colorindex = 3
cell.offset(0,10).Clearcontents
exit For
End If
Next Cell
End Sub

When I step through the code - everything appears to be fine, however, the cell does not get selected.

In listbox2 I select "5" when stepping through I can see the cell #'s counting (C2,3,4,5) C4="5". C4 doesn't get selected. Code just goes to end sub


Thanks,


Kurt

iwrk4dedpr
10-14-2004, 02:49 PM
Well it doesn't work. However, I'm not totally clear as to what exactly you want to be done. Ok so you can't post actual information can't you just make up data to enter and then post a sample with an explanation of what you want the code to do.



I tried as best I could to perform the actions that I thought you described. So if it's not doing as you request I'll need more information or an example.


Or, if you know how to step through code, step through and then as it goes you know what should happen as each item loops. Then you should be able to alter the code to do EXACTLY what you want.


Regards,
Barry:006:

stapuff
10-18-2004, 06:03 AM
Barry -

I got the code to work w/ the following:


For Each Cell In Worksheets(Me.ListBox1.Value).Range("C2:C65536")
If Cell = "" Then Exit For
If Cell.Text = Me.ListBox2.Value Then
Cell.Offset(0, -2).Range("A1:O1").Interior.ColorIndex = 6
Cell.Offset(0, 10).ClearContents
Cell.Offset(0, 8).Value = "CNX"
Exit For
End If
Next Cell



Thank You for your help,

Kurt

Zack Barresse
10-18-2004, 08:07 AM
... firefytr - ... I have not been zipping or Colo posting.

Btw Kurt, HTML is disabled on this forum, so Colo's HTML Maker (and Juan's VBHTML Maker) do not work here. That's why we allow zipped files and use the VBA tags for posting code.

And if this is solved for you, mark it as such. :yes You can do so by clicking Thread Tools at the top of this thread, selecting Mark Solved and clicking Perform Action. Glad you got it working!