List box to populate,command button to highlight & delete

[stapuff]

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]

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]

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. However, it SHOULD do what you requested.


Regards,
Barry

[stapuff]

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]

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]

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

[stapuff]

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]

... 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. 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!