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

 

[Fluff]

Hi
The following works on Carers & sick as an example, is it what you're looking for?

Private Sub CommandButton2_Click()
    
    Dim myReport As Variant
    Dim UsdRws As Long
    Dim SiTot As Long
    Dim CaTot As Long
    Dim i As Long
        myReport = InputBox("Enter ID Number:", "Generate Leave Report")

    UsdRws = Sheet6.Range("B" & Rows.Count).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)
                
        End Select
    Next i
    MsgBox "CARERS = " & CaTot & vbLf & "SICK = " & SiTot
    
End Sub

 

[twothings]

Thanks for your time Fluff. Yes that is on the right path to what I was thinking about. My computer is relatively new and with 250 lines of data it takes nearly 30 seconds to calculate a result with the carers/sick data only.

Thinking out loud, if I had the initial staff data entry place the result into a cell on the sheet and then had all calculations on the sheet within series of cells for the information I need (these will be hidden).

Can the same results in this table then be referenced and replicated on the second result prompt, whether it is a form that I build or a standard vba generated prompt? Hope that is clear. apologies for any confusion.

 

[Fluff]

My computer is relatively new and with 250 lines of data it takes nearly 30 seconds to calculate a result

This is my mistake not your computer, forgot to finish a line of code

UsdRws = Sheet6.Range("B" & Rows.Count).Row
UsdRws = Sheet6.Range("B" & Rows.Count)[COLOR=#ff0000].End(xlUp)[/COLOR].Row

As for

Thinking out loud, if I had the initial staff data entry place the result into a cell on the sheet and then had all calculations on the sheet within series of cells for the information I need (these will be hidden).

Can the same results in this table then be referenced and replicated on the second result prompt, whether it is a form that I build or a standard vba generated prompt? Hope that is clear. apologies for any confusion.

I'm afraid you've lost me

 

[twothings]

The results are instant now thank you! Disregard the second part of my query above, merely a brainstorm in case cpu load was too high and I needed an alternative.

I can adapt the code to include the other reasons for absence so I thank you for your help!

Working on from your solution, is it possible to:

1. Title the results box "Member Leave Report"
2. Change the results box size
3. Include the following text above the results:

Name of the person, referenced from the adjacent staff ID from column C on Members sheet.
"Total hours since " and a date reference in "Admin!C4" where cell reference is a date in format dd/mm/yyyy:

so it looks like:

DARWIN, CHARLES

Total hours since 01/07/2014:

CARERS: 0
SICK: 8

etc...

Cheers

 

[Fluff]

It's all possible, but you might be better off with a userform, rather than a message box.

    With Sheet9
        Set MemName = .Range("B2", .Range("B2").End(xlDown)).Find(what:=myReport, lookat:=xlWhole).Offset(, 1)
    End With

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

This will get the Members name & modifies the message box as requested.
As far as I know you cannot set the size of a message box, other than be changing what you put into it.

 

[twothings]

Ok, sounds good. But where should that code fit in... I keep getting errors in all the places I place it

 

[Fluff]

Apologies, remove the existing MsgBox line & put the extra code in its place.
Alternatively use 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


    myReport = InputBox("Enter ID Number:", "Generate Leave Report")
    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

    With Sheet9
        Set MemName = .Range("B2", .Range("B2").End(xlDown)).Find(what:=myReport, lookat:=xlWhole).Offset(, 1)
    End With

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


End Sub

 

[twothings]

Fluff, you are a genius and I thank you for your time, skills and assistance. Your suggestion is working perfectly!!

 

[Fluff]

Fluff, you are a genius

If only!

I thank you for your time, skills and assistance. Your suggestion is working perfectly!!

You're welcome & Thanks for the feedback.