Userform to change values from hidden sheet

[dguillen]

Hey everybody,

I've been working on this project for months now and I'm getting towards the end of my timeline. The idea was to have the end user fill out the "Request" form, which is all recorded and stored in the "DataLog" sheet (hidden). Then once the request is satisfied, the end user will find their request (via UserForm) then update the request as "Complete"....

Where i need help......
I've built a UserForm with VLookup to find previous requests, but i can't get the CBO to mark the recorded request as "Complete" in the "DataLog" sheet.
Here's what i have so far...

Private Sub ID_AfterUpdate()
'Vlookup to find the "Request#"
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ID.Value) = 0 Then
MsgBox "This is an incorrect ID"
Me.ID.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
'.ID1 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 2, 0)
.ID2 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 3, 0)
.ID3 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 4, 0)
.ID4 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 5, 0)
.ID5 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 6, 0)
.ID6 = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 7, 0)
.cboStatus = Application.WorksheetFunction.VLookup(CLng(Me.ID), Sheet2.Range("Lookup"), 10, 0)

End With
End Sub


I know this is way wrong, but this is the closest i've gotten to my goal!


Private Sub cmdUpdate_Click()


Dim Status As String
Status = cboStatus.Value




Cells(Application.ActiveCell.Row, 10).Value = Status


'Close the userform
Unload Me


End Sub

ANY HELP WILL BE GREATLY APPRECIATED!

 

[sericom]

Try this

Private Sub cmdUpdate_Click()

    Dim IDrow As Integer
    Dim Status As String
    Status = cboStatus.Value

    With Sheet2
        IDrow = .Range("A:A").Find(Me.ID.Value, LookIn:=xlValues, lookat:=xlWhole).Row
        .Cells(IDrow, 10).Value = Status
    End With

    'Close the userform
    Unload Me

End Sub

You could also use this method of finding the ID row to simplify your ID_AfterUpdate sub

eg

.ID2 = Sheet2.Cells(IDrow,3).Value

 

[dguillen]

Try this

Private Sub cmdUpdate_Click()

    Dim IDrow As Integer
    Dim Status As String
    Status = cboStatus.Value

    With Sheet2
        IDrow = .Range("A:A").Find(Me.ID.Value, LookIn:=xlValues, lookat:=xlWhole).Row
        .Cells(IDrow, 10).Value = Status
    End With

    'Close the userform
    Unload Me

End Sub

You could also use this method of finding the ID row to simplify your ID_AfterUpdate sub

eg

.ID2 = Sheet2.Cells(IDrow,3).Value

sericom! You're amazing... i've spend hours and hours and hours watching YouTube videos and researching hundreds of webpages with ZERO luck! But, on Mr.Excel.com, it took a little over an hour for a highly skill excel guru like yourself to fix my problem... i can't thank you enough with your help. Greatly, Greatly appreciated. THANK YOU!

 

[dguillen]

sericom! You're amazing... i've spend hours and hours and hours watching YouTube videos and researching hundreds of webpages with ZERO luck! But, on Mr.Excel.com, it took a little over an hour for a highly skill excel guru like yourself to fix my problem... i can't thank you enough with your help. Greatly, Greatly appreciated. THANK YOU!

Thank you!