Need help with Button command

[anik18]

Hi Guys,

I have made an example of Staff Roster. See attached file. Is it possible to make button command if i click any of employee's name. I was thinking how to make button command so that it should get dialog box what to do with this employee like how many absences he/she made or how many sick leave or presents or holidays leave or annual leave. Then finally they should answer again in dialog box or as Pop up box like 12 absences he made. What is other way how to check how many absences he/she made blah blah!!! It is hard to count with your eyes if there are multi sheets then what we will do????

[Bob Phillips]

How about this

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    Const WS_RANGE As String = "A:A"     '<== change to suit
    Dim RowNum As Long
    Dim RecLeave As Long
    Dim PHLeave As Long
    ' and the others
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, sh.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Row > 10 Then
                RecLeave = 0
                PHLeave = 0
                ' and the others
                For Each sh In ThisWorkbook.Worksheets
                    RowNum = 0
                    On Error Resume Next
                    RowNum = Application.Match(.Value, sh.Columns(1), 0)
                    On Error GoTo 0
                    If RowNum > 0 Then
                        RecLeave = RecLeave + Application.CountIf(sh.Rows(RowNum), "R")
                        PHLeave = PHLeave + Application.CountIf(sh.Rows(RowNum), "PH")
                        ' and the others
                    End If
                Next sh
            End If
            MsgBox "Tallies for " & .Value & ":" & vbNewLine & _
            vbTab & "Recreation leave: " & RecLeave & vbNewLine & _
            vbTab & "Public holiday: " & PHLeave & vbNewLine
            ' and the others
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[anik18]

Thanks xlD for replying. What i am suppose to get from your codes. I copied this in worksheet1 and then compile it. It said nothing. No function and just only macros dialog box with no macro name. Could you please tell me what is this codes for. I am finding VBA difficult because i am a beginner to this.

[Bob Phillips]

Select name on any sheet and see what happens. Then select another, and so on.

[anik18]

I should select name in excel or VBA. I don't really understand what to do. Do i have to put code in sheets or modules. After that how to run it.

[nst1107]

Here's what to do:
1.) Open your workbook.
2.) Open the Visual Basic Editor. (Right-click any sheet's tab and select "View Code".)
3.) On the left-hand side you should see the project explorer window. Double-click "ThisWorkbook" in that window. This will show you the code for the workbook.
4.) Paste xld's code in the declarations area (to the right of the project explorer--the big white space).
5.) Close the VBE.
6.) On any spreadsheet in your workbook, click a cell containing the name of one of the employees. (If such a cell is already selected, click a different employee's name.) The code will run then.

[anik18]

Thanks man!!! I understood what you said. Great work.

[anik18]

Hi, what about if i choose particular sheets like sheet1 and sheet2 only NOT sheet3. See attached file for example. How you will do that? I thought i will do like this. But doesn't seem to be right.

msgbox "Sick leave" &Range ("A1")

At this moment we made code on whole sheets see above xld helped us. Now i want code on particular sheets.

Thanking You

[Bob Phillips]

What do you mean by choosing sheet1 or 2 but not 3. The example workbook expalins nothing.

[anik18]

I mean how to make pop up dialog box for particular sheets like sheet1 only or sheet1 and sheet2. The code which you made above is only for whole sheets like total employee's duties, sick leaves,etc. Can you made code for particular sheets i like to choose.

[mdmackillop]

You can use the sheet name to select different routines to be followed

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Dim RowNum As Long
    Dim RecLeave As Long
    Dim PHLeave As Long
    ' and the others
    Select Case sh.Name
        Case "Sheet1"
            ' do sheet 1 stuff
        Case "Sheet2"
            ' do sheet 2 stuff
        Case Else
            ' do other stuff
    End Select
    ' etc.

[anik18]

Hey, it is not working after compiling. No pop up dialog box. Can you attach the workbook again so that i will see what you did. Did you get anything after compiling?

[Bob Phillips]

Option Explicit
 
Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Dim RowNum As Long
    Dim RecLeave As Long
    Dim PHLeave As Long
    ' and the others
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If sh.Name <> "Sheet3" Then
        If Not Intersect(Target, sh.Range(WS_RANGE)) Is Nothing Then
            With Target
                If .Row > 10 Then
                    RecLeave = 0
                    PHLeave = 0
                    ' and the others
                    For Each sh In ThisWorkbook.Worksheets
                        RowNum = 0
                        On Error Resume Next
                        RowNum = Application.Match(.Value, sh.Columns(1), 0)
                        On Error GoTo 0
                        If RowNum > 0 Then
                            RecLeave = RecLeave + Application.CountIf(sh.Rows(RowNum), "R")
                            PHLeave = PHLeave + Application.CountIf(sh.Rows(RowNum), "PH")
                            ' and the others
                        End If
                    Next sh
                End If
                MsgBox "Tallies for " & .Value & ":" & vbNewLine & _
                vbTab & "Recreation leave: " & RecLeave & vbNewLine & _
                vbTab & "Public holiday: " & PHLeave & vbNewLine
                ' and the others
            End With
        End If
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

[anik18]

Hi xld, Thanks for help. The code is same as previous one above. If you run this code it will show pop dialog box for all worksheets info. But my another aim is to get pop up dialog box only for sheet1 information not all sheets OR pop dialog box for sheet1 and sheet2, etc whenever i like to choose. Is it possible to make like this? I tried myself, didn't work.

[Bob Phillips]

That code is for all sheets except 3, and it shows the value for the sheet being changed, so no, I don't understand.

[anik18]

The code is same as previous one above. If you run this code it will show pop dialog box for all worksheets info. But my another aim is to get pop up dialog box only for sheet1 information not all sheets OR pop dialog box for sheet1 and sheet2, etc whenever i like to choose. Is it possible to make like this? Is it possible to make everything together like finding for all sheets or finding for particular sheets. Like an option. I tried myself, didn't work. Sorry for changing comment.

[anik18]

Ok! if you have made code for all except Sheet3 then why public holidays given are total of 4 which also include in sheet3. Otherwise it should be 3 not 4. Check it. Plz read my last comment above to know my aim.

[Bob Phillips]

Perhaps this is what you want

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Dim RowNum As Long
    Dim RecLeave As Long
    Dim PHLeave As Long
    ' and the others
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, sh.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Row > 10 Then
                RecLeave = 0
                PHLeave = 0
                ' and the others
                For Each sh In ThisWorkbook.Worksheets
                    If sh.Name <> "Sheet3" Then
                        RowNum = 0
                        On Error Resume Next
                        RowNum = Application.Match(.Value, sh.Columns(1), 0)
                        On Error GoTo 0
                        If RowNum > 0 Then
                            RecLeave = RecLeave + Application.CountIf(sh.Rows(RowNum), "R")
                            PHLeave = PHLeave + Application.CountIf(sh.Rows(RowNum), "PH")
                            ' and the others
                        End If
                    End If
                Next sh
                MsgBox "Tallies for " & .Value & ":" & vbNewLine & _
                vbTab & "Recreation leave: " & RecLeave & vbNewLine & _
                vbTab & "Public holiday: " & PHLeave & vbNewLine
                ' and the others
            End If
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

[anik18]

I think you are finding hard to understand what i am saying. I will explain you in simple meaning steps .

1) If I click any of employee name (see attached file for e.g.) and then a dialog box should say what you want to do.

2) On that dialog box there should be 3 options command. For e.g. Option 1command should say "Find employee's details for whole sheets" (e.g. of details are employee's number of duties, sick leave, public holidays,etc) for whole sheets" AND Option 2 should say "Find employee's details from Sheet of your own choice". Maybe i will click Sheet1 or Sheet2 seperate not together.

3) And Option 3 should say "Find employee's details from more than one sheet". For e.g. I will click Sheet1 and Sheet2 same time.

4) So there should be about 3 options in dialog box.

5) For Option 1 (e.g. "Find employee's details for whole sheets") i have already done see code above and try them from attached file and you will understand what i want.

6) I am looking forward to option 2 and option 3 codes.

This is what i want.

[mikerickson]

If I could make one suggestion.
Selecting is integral to the normal working of a spreadsheet. Any routine triggered by the SelectionChange event will be called frequently, even if it is not desired.
The DoubleClick event offers finer control of when a routine is run.