Update cell data based on Listbox selection

[MagicMike]

Hello all,

OK here is what I'm trying to do.
I have a form which has a tab for managers to approve all pending work orders. The listbox displays the prefiltered by manager list from the "All_WO" sheet.
in the userform initialize:
[vba]With ListBox_Not_Approved
.ColumnCount = 16
.ColumnWidths = "41;18;0;0;0;51;100;0;0;110;40;51;0;0;0;51"
End With
Sheets("All_WO").Select
ListBox_Not_Approved.RowSource = "A5:P" & Range("A" & Rows.Count).End(xlUp).Row[/vba]

I hide or set the column width to 0 for the columns that I do not want the manager to see.

Now here is the question.

I want the manager to select from the list and click an approve button at which point the mangers username is inserted on the sheet on the proper selction in Column 14. I also need the "date" put into column 15. The username is already established on another sheet so I can reference it there.

The problem I'm having is that the listbox.value comes up as the data in the first column. I have tried doing a Vlookup to find the slected value in the listform and find the cell that is 14 over from the first column but I need to Replace that data with the username not return that data.
I think I need something like Vlookup but to replace the data not display it.

Heres what I have that is not working thus far.
[vba]Private Sub CommandButton_Approve_Click()
'****************************************************
'Add selection to work order system.
'****************************************************
Dim Approve As String
Dim Apr_By As String
Dim Apr_Date As String
Approve = MsgBox("Pressing the OK button will add the selected" & vbNewLine & _
"work order number to the system." & vbNewLine & vbNewLine & _
" Are you sure?", vbOKCancel + vbQuestion, "Add Work Order Number")
If Approve = 2 Then
MsgBox "Request Canceled!"
Exit Sub
Else
Apr_By = Application.VLookup(ListBox_Not_Approved.Value, Worksheets("All_WO").Range("A5:O65536"), 14, False)
Apr_By = Sheets("Access").Range("E1")
End If

End Sub 'CommandButton_Approve_Click()[/vba]

You see I tried naming the lookup and changing it after but it was just a guess.

Any help would be greatly appreciated! I need to get this figured out before I go home lol

[MagicMike]

ok well I searched around for a different approach and came up with this:
[vba]Private Sub CommandButton_Approve_Click()
'****************************************************
'Add selection to work order system.
'****************************************************
Approve = MsgBox("Pressing the OK button will add the selected" & vbNewLine & _
"work order number to the system." & vbNewLine & vbNewLine & _
" Are you sure?", vbOKCancel + vbQuestion, "Add Work Order Number")
If Approve = 2 Then
MsgBox "Request Canceled!"
Exit Sub
Else
Dim FirstAddress As String
Dim MySearch As Variant
Dim User As Variant
Dim Rng As Range
Dim sh As Worksheet
MySearch = ListBox_Not_Approved.Value
User = Sheets("Access").Range("C3") 'Current user
For Each sh In ActiveWorkbook.Worksheets
With sh.Cells
Set Rng = .Find(What:=MySearch, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Offset(0, 13).Value = User
Rng.Offset(0, 14).Value = Date
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
End With
Next sh
Call Consolodate_WO 'refresh the all wo list
Call UserForm_Initialize 'refresh the form list
End If
End Sub 'CommandButton_Approve_Click()[/vba]

This actually works to put the data in the right spot in the cells. The problem that I'm having now is with the listbox showing all rows from the All_WO sheet instead of only showing the rows that I have autofiltered. Is there a way to only display autofiltered rows in the listbox?

Thanks,

Mike

[mdmackillop]

Can you post a sample workbook?

[MagicMike]

Let me rephrase this question.
I am first autofiltering a range of cells on a worksheet and then poitning the listobox to that range to popilate it.
[vba]'Populate Listbox
With ListBox_Not_Approved
.ColumnCount = 16
.ColumnWidths = "41;18;0;0;0;51;100;0;0;110;40;51;0;0;0;51"
End With
Sheets("All_WO").Select
ListBox_Not_Approved.RowSource = "A5:P" & Range("A" & Rows.Count).End(xlUp).Row[/vba]

I have the above problem resolved for updating the data in the list but now the problem is that the list in the listbox is displaying everything in the sheet and not the autofiltered items only. I think what I need to do is modify the red part to display the autofiltered results.
[vba]ListBox_Not_Approved.RowSource = "A5:P" & Range("A" & Rows.Count).End(xlUp).Row[/vba]
(I turned the text red for illustration, the code is ok)
I am thinking I need the autofilter command in here. It would be helpful if someone were to explain exactly what the red part means. If I understood that I might be able to get something together.

I will try to slap a file together with all this junk. Thanks

[mdmackillop]

This find the last filled cell in Column A. It's like going to the last cell in Col A and pressing Ctrl + Up arrow.
Range("A" & Rows.Count).End(xlUp)

.Row returns the row number of that cell

Putting them together with "A5:P" will result in eg "A5:P2000"
You can see this result using the immediate window and some debug code
eg
[vba]
Dim txt as string
txt = "A5:P" & Range("A" & Rows.Count).End(xlUp).Row
debug.print txt
[/vba]

[MagicMike]

OK I think I understand. So let me ask you this. I am autofiltering the sheet before I have the listbox populate with the code that I just previously posted.
[vba] With Sheet3
.AutoFilterMode = False
With .Range("A4:R4")
.AutoFilter
.AutoFilter Field:=13, Criteria1:=Sheets("Access").Range("C3") 'Current user
.AutoFilter Field:=14, Criteria1:=""
End With
End With[/vba]

Do you think I could use a form of this code in the rowsource function of the listbox? That way the listbox would only display the rows in the sheet that are filtered.

[mdmackillop]

No time just now but check out SpecialCells xlCellTypeVisible.

[MagicMike]

No time just now but check out SpecialCells xlCellTypeVisible.

I did a bunch of searching on this yesterday and could not figure out how to use this property in the display of the listbox.

I did however find some related searches that copy the filtered range using the specialcells xlCellTypeVisible property and then paste them to a new sheet and then you reference the new sheet. I may go this route since I think I dont want my managers to see the "full" sheet with all the managers work. I'll be working on this today and I'll let you know what I come up with. Thanks for the suggestion.

-Mike

[mdmackillop]

Try AddItem

[MagicMike]

OK I'm trying to get your code to work with my example. I'm affraid I have stuff all over the place and I'm trying to get a workbook together with a better example of what I'm trying to do so you can see. I think the add item will work better but I have 2 questions here:
[vba]For Each cel In Range("Data").SpecialCells(xlCellTypeVisible)
ListBox1.AddItem cel
Next[/vba]

1. The "Data" would be the common text in the filtered field that i want in the list right? so if I'm reffereing to a user name I could replace Range("Data") with Sheets("Access").Range("C3") and it should look for whats in that cell correct?

2. I have the listbox displaying about 16 columns the filtered data being in column 14. Can I change the "For each cel" to "For each row"?

I just tried to insert this in my project and change a few things and it crashed excel when I tried to run it lol. I'm going to keep cracking away at this. Thanks again for your time here. I'm a bigger noob than I thought!

-Mike