MagicMike
10-21-2008, 11:51 AM
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:
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
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.
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()
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
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:
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
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.
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()
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