Button For Popup Search Entry & Report

[twothings]

Greetings

I have created a popup running off a commandbutton as per follows:

Private Sub CommandButton2_Click()
    
    Dim myReport As Variant
        myReport = InputBox("Enter ID Number:", "Generate Leave Report")
    
End Sub

Leveraging off this, is it possible to have a user enter a staff id number which would then go to the worksheet, search and add up all 6 leave types and then provide another popup with the results? I have attempted to come up with a solution and tried to write the formula to replicate results in a table using SUMIF/SUMIFS and encountered problems due to a test in column B followed by multiple tests in Column E, to then add up data in Column H of qualifying matches to get a total.

I hope this image makes more sense if I have confused you.

So when the user clicks "Member Report", this popup shows:

What I am hoping to achieve?
When a staff ID is entered, for example '4444' as per the data above and 'OK' is clicked, I get another popup showing Total Hours of all leave types even if zero:

Sick: 8
Carers: 0
Family: 0
Injury: 0
Urgent: 8
Other: 0

Would this require a form design or can it be achieved in a standard popup similar to that above?

I am still very much at basic level in Excel so hope someone can help me out. Your assistance is greatly appreciated!!

Scott

I have uploaded a working copy of the file
http://s000.tinyupload.com/?file_id=90446930089702635196

 

[twothings]

Sorry, Fluff...just found this...

I get an error if if you don't enter an ID number and/or select 'Ok' or 'Cancel'.

The result says:

Run Time Error '91':

Object variable or With block variable not set

Could it prompt to say, enter an ID if found and/or ID number not located if one doesn't match. Or just one message saying "A valid ID number must be entered, if you continue to receive this error, please confirm the ID exists on the members tab".

If 'Cancel' is selected, the process will End.

Thanks Fluff!

 

[Fluff]

How about like this?

Private Sub CommandButton2_Click()

    Dim myReport As Variant
    Dim UsdRws As Long
    Dim SiTot As Long
    Dim CaTot As Long
    Dim MemName As Range
    Dim i As Long

InputID:
    myReport = Application.InputBox("Enter ID Number:", "Generate Leave Report")

    If myReport = False Then
        Exit Sub
    ElseIf myReport = "" Then
        MsgBox "No ID number entered"
        GoTo InputID
    End If
    With Sheet9
        Set MemName = .Range("B2", .Range("B2").End(xlDown)).Find(what:=myReport, lookat:=xlWhole)
    End With
    If MemName Is Nothing Then
        MsgBox myReport & " is an invalid ID number"
        GoTo InputID
    End If
    MemName = MemName.Offset(, 1)
        
    UsdRws = Sheet6.Range("B" & Rows.Count).End(xlUp).Row

    For i = 8 To UsdRws
        Select Case Sheet6.Range("B" & i) & Sheet6.Range("E" & i)
            Case myReport & "SICK"
                SiTot = SiTot + Sheet6.Range("H" & i)
            Case myReport & "CARERS"
                CaTot = CaTot + Sheet6.Range("H" & i)
        End Select
    Next i

    MsgBox MemName _
            & vbLf & "Total Hours Since  " & Sheet8.Range("C4").Value _
            & vbLf & "CARERS = " & CaTot _
            & vbLf & "SICK = " & SiTot _
            , Title:="Member Leave Report"


End Sub

 

[twothings]

That was quick. Yes that works but the the 'Cancel' button doesn't end the query, it loops back and the only way out of it is to enter a valid id number. Can cancel cause the argument to End? thanks

 

[Fluff]

It works for me, can you check that you have

Application.InputBox

rather than just

InputBox

otherwise it wont work.
Also can you please change the following code-
Remove

    MemName.Value = MemName.Offset(, 1)

& change the message box to read

MsgBox MemName.Offset(, 1) _

otherwise it will overwrite your ID codes with the persons name

 

[twothings]

Thanks for pointing out the Application.InputBox issue...perfect now.

I can't find:

[COLOR=#333333]MemName.Value = MemName.Offset(, 1)[/COLOR]

But assume you mean:

MemName = MemName.Offset(, 1)

If I change the message box code to read as suggested, a second intermediary box shows up with just the name of the person with an 'Ok' button. The data then shows when 'Ok' is clicked. Did you get the same? I have tested it to see if details are being overwritten and can't see any changes on the Members sheet.

Current code prior to the above change is:

Private Sub CommandButton2_Click()

    Dim myReport As Variant
    Dim UsdRws As Long
    Dim SiTot As Long
    Dim CaTot As Long
    Dim FaTot As Long
    Dim UrTot As Long
    Dim WkTot As Long
    Dim OtTot As Long
    Dim i As Long
InputID:
    myReport = Application.InputBox("Enter ID Number:", "Report on Leave")
    
    If myReport = False Then
        Exit Sub
    ElseIf myReport = "" Then
        MsgBox "No ID number entered"
        GoTo InputID
    End If
    With Sheet9
        Set MemName = .Range("B2", .Range("B2").End(xlDown)).Find(what:=myReport, lookat:=xlWhole)
    End With
    If MemName Is Nothing Then
        MsgBox myReport & " is an invalid ID number"
        GoTo InputID
    End If
    MemName = MemName.Offset(, 1)
    
    UsdRws = Sheet6.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 8 To UsdRws
        Select Case Sheet6.Range("B" & i) & Sheet6.Range("E" & i)
            Case myReport & "SICK"
                SiTot = SiTot + Range("H" & i)
            Case myReport & "CARERS"
                CaTot = CaTot + Range("H" & i)
            Case myReport & "FAMILY"
                FaTot = FaTot + Range("H" & i)
            Case myReport & "URGENT"
                UrTot = UrTot + Range("H" & i)
            Case myReport & "INJURY"
                WkTot = WkTot + Range("H" & i)
            Case myReport & "OTHER"
                OtTot = OtTot + Range("H" & i)
        End Select
    Next i


    MsgBox MemName _
            & vbLf & vbLf & "Total Hrs Since " & Sheet8.Range("C4").Value _
            & vbLf & vbLf & "Carer:  " & CaTot _
            & vbLf & "Sick:  " & SiTot _
            & vbLf & "Family:  " & FaTot _
            & vbLf & "Urgent:  " & UrTot _
            & vbLf & "Injury:  " & WkTot _
            & vbLf & "Other:  " & OtTot _
            , Title:="Member Stats Report"
            
End Sub

Thanks

 

[Fluff]

You don't have a Dim statement for MemName, whereas I had it set as a Range. Hence the difference.

 

[twothings]

Fluff, thanks so much. My fault, I read what you wrote differently. It's working perfectly! Im indebted to you now. Cheers

 

[twothings]

Final code for those who may come across this post thanks to board member FLUFF...

Private Sub CommandButton2_Click()

    Dim myReport As Variant
    Dim UsdRws As Long
    Dim SiTot As Long
    Dim CaTot As Long
    Dim FaTot As Long
    Dim UrTot As Long
    Dim WkTot As Long
    Dim OtTot As Long
    Dim MemName As Range
    Dim i As Long
InputID:
    myReport = Application.InputBox("Enter ID Number (exclude PD):", "Report on Member Leave")
    
    If myReport = False Then
        Exit Sub
    ElseIf myReport = "" Then
        MsgBox "No ID number entered"
        GoTo InputID
    End If
    With Sheet9
        Set MemName = .Range("B2", .Range("B2").End(xlDown)).Find(what:=myReport, lookat:=xlWhole)
    End With
    If MemName Is Nothing Then
        MsgBox myReport & " is an invalid ID number"
        GoTo InputID
    End If
    
    UsdRws = Sheet6.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 8 To UsdRws
        Select Case Sheet6.Range("B" & i) & Sheet6.Range("E" & i)
            Case myReport & "SICK"
                SiTot = SiTot + Range("H" & i)
            Case myReport & "CARERS"
                CaTot = CaTot + Range("H" & i)
            Case myReport & "FAMILY"
                FaTot = FaTot + Range("H" & i)
            Case myReport & "URGENT"
                UrTot = UrTot + Range("H" & i)
            Case myReport & "INJURY"
                WkTot = WkTot + Range("H" & i)
            Case myReport & "OTHER"
                OtTot = OtTot + Range("H" & i)
        End Select
    Next i


    MsgBox MemName.Offset(, 1) _
            & vbLf & vbLf & "Total Hrs Since " & Sheet8.Range("C4").Value _
            & vbLf & vbLf & "Carer:  " & CaTot _
            & vbLf & "Sick:  " & SiTot _
            & vbLf & "Family:  " & FaTot _
            & vbLf & "Urgent:  " & UrTot _
            & vbLf & "Injury:  " & WkTot _
            & vbLf & "Other:  " & OtTot _
            , Title:="Member Stats Report"
            
End Sub