Button For Popup Search Entry & Report

twothings

New Member
Joined
Jul 9, 2011
Messages
45
Greetings :biggrin:

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

Code:
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.

qn9375.jpg



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

2yzg6s7.jpg



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
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,009
Office Version
  1. 365
Platform
  1. Windows
Hi
The following works on Carers & sick as an example, is it what you're looking for?
Code:
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

New Member
Joined
Jul 9, 2011
Messages
45
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,009
Office Version
  1. 365
Platform
  1. Windows
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
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

New Member
Joined
Jul 9, 2011
Messages
45

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,009
Office Version
  1. 365
Platform
  1. Windows
It's all possible, but you might be better off with a userform, rather than a message box.
Code:
    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

New Member
Joined
Jul 9, 2011
Messages
45

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,009
Office Version
  1. 365
Platform
  1. Windows
Apologies, remove the existing MsgBox line & put the extra code in its place.
Alternatively use this
Code:
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

New Member
Joined
Jul 9, 2011
Messages
45
Fluff, you are a genius and I thank you for your time, skills and assistance. Your suggestion is working perfectly!! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,009
Office Version
  1. 365
Platform
  1. Windows
Fluff, you are a genius
If only:(!
Code:
I thank you for your time, skills and assistance. Your suggestion is working perfectly!!
You're welcome & Thanks for the feedback(y).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top