none
Use cells from different sheets in VBA code RRS feed

  • Question

  • Hi Everyone,

    I found a code that does what I need but I now need to use cells from different sheets in the code and I don't know how to do it. Here is the code I use:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Application.WorksheetFunction.Sum(Range("B1,E1,G1")) > Range("A1") Then
            MsgBox "Vous avez déjà distribué tous les repas"
            Target.Value = ""
        End If
    End Sub

    What if I want to show the message box when the sum of the cell A1 from sheet2, A1 from sheet3 and so on is bigger than the value in A1 from sheet1?

    PS: the code is currently in the sheet and not in "ThisWorkbook"

    K.


    • Edited by kmoreau48 Thursday, July 17, 2014 12:13 PM
    Thursday, July 17, 2014 12:12 PM

Answers

  • Here is a version that handles multiple cells. Change the constant rng at the beginning as needed.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Const sht = "Sheet1" ' Name of first sheet
        Const rng = "A1:A10" ' Cells to be monitored
        Dim wsh As Worksheet
        Dim lngSum As Long
        Dim lngTotal As Long
        Dim cel As Range
    
        If Sh.Name <> sht Then
            If Not Intersect(Sh.Range(rng), Target) Is Nothing Then
                For Each cel In Intersect(Sh.Range(rng), Target)
                    lngTotal = Worksheets(sht).Range(cel.Address).Value
                    lngSum = 0
                    For Each wsh In Worksheets
                        If wsh.Name <> sht Then
                            lngSum = lngSum + wsh.Range(cel.Address).Value
                        End If
                    Next wsh
                    Select Case lngSum
                        Case lngTotal
                            MsgBox "Vous avez exactement distribué tous les repas", vbInformation
                        Case Is > lngTotal
                            MsgBox "Vous avez distribué trop des repas", vbInformation
                        Case Is < lngTotal
                            MsgBox "Vous n'avez pas encore distribué tous les repas", vbInformation
                    End Select
                Next cel
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kmoreau48 Saturday, July 19, 2014 5:25 PM
    Friday, July 18, 2014 2:55 PM

All replies

  • Change the line

        If Application.WorksheetFunction.Sum(Range("B1,E1,G1")) > Range("A1") Then

    to

        If Worksheets("Sheet2").Range("A1") + Worksheets("Sheet2").Range("A1") + _
                Worksheets("Sheet3").Range("A1") > Worksheets("Sheet1").Range("A1") Then

    Or, if you want to sum A1 on all sheets except Sheet1:

        Dim wsh As Worksheet
        Dim dblSum As Double
        For Each wsh In Worksheets
            If wsh.Name <> "Sheet1" Then
                dblSum = dblSum + wsh.Range("A1").Value
            End If
        Next wsh
        If dblSum > Worksheets("Sheet1").Range("A1").Value Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 17, 2014 2:23 PM
  • Thanks for the reply, but it didnt work when I changed the line. I put the code in the "ThisWorkbook" and also in a new module but no luck.
    Thursday, July 17, 2014 2:46 PM
  • Could you provide more detailed information about what you actually want to accomplish?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 17, 2014 6:02 PM
  • I have a cell in the first sheet that has the total number of meal someone ate during the week... then the user has to split these meals into different sheets and I want to make sure that the sum of the meal split by the user does not exceed the total number of meal in the first sheet.

    So if in the first sheet the total number of meals is 7 and the user can split the 7 meals in the 3 next sheet (2nd, 3rd and 4th sheet) so if the user enters 3 in the second sheet, 3 in the third sheet, then in the fourth sheet he would have to enter the value 1. If by mistake the value is more, I want a message box to let the user know the split meals do not add up to the total meals.

    PS: by writing more details about it, it would also be useful if every time the user enter a value for a meal in the 2nd, 3rd and 4th sheet, a message box would appear to let him know that he did not split all the meal just yet... until the sum of the split meals equals the total number of meals.

    For the sake of the example let's assume that the meal value has to be in cell A1 for each sheet. So I feel like the solution you gave me would work and made sense but I have no idea why the message box does not show when I enter 2 in sheet1 and 4 in sheet 2.

    Thursday, July 17, 2014 7:23 PM
  • Try this in the ThisWorkbook module. You can change the constants at the beginning of the code to suit your situation.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Const sht = "Sheet1" ' Name of first sheet
        Const cel = "A1" ' Cell to be monitored
        Dim wsh As Worksheet
        Dim lngSum As Long
        Dim lngTotal As Long
    
        If Sh.Name <> sht Then
            If Not Intersect(Sh.Range(cel), Target) Is Nothing Then
                lngTotal = Worksheets(sht).Range(cel).Value
                For Each wsh In Worksheets
                    If wsh.Name <> sht Then
                        lngSum = lngSum + wsh.Range(cel).Value
                    End If
                Next wsh
                Select Case lngSum
                    Case lngTotal
                        MsgBox "Vous avez exactement distribué tous les repas", vbInformation
                    Case Is > lngTotal
                        MsgBox "Vous avez distribué trop des repas", vbInformation
                    Case Is < lngTotal
                        MsgBox "Vous n'avez pas encore distribué tous les repas", vbInformation
                End Select
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 17, 2014 7:43 PM
  • It works, thank you for your help.

    What if I need to do this for multiple employees? I'm sure I don't have to copy and past this function for each situation. Could I specify the range of cells instead of only one cell?

    Friday, July 18, 2014 11:27 AM
  • Here is a version that handles multiple cells. Change the constant rng at the beginning as needed.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Const sht = "Sheet1" ' Name of first sheet
        Const rng = "A1:A10" ' Cells to be monitored
        Dim wsh As Worksheet
        Dim lngSum As Long
        Dim lngTotal As Long
        Dim cel As Range
    
        If Sh.Name <> sht Then
            If Not Intersect(Sh.Range(rng), Target) Is Nothing Then
                For Each cel In Intersect(Sh.Range(rng), Target)
                    lngTotal = Worksheets(sht).Range(cel.Address).Value
                    lngSum = 0
                    For Each wsh In Worksheets
                        If wsh.Name <> sht Then
                            lngSum = lngSum + wsh.Range(cel.Address).Value
                        End If
                    Next wsh
                    Select Case lngSum
                        Case lngTotal
                            MsgBox "Vous avez exactement distribué tous les repas", vbInformation
                        Case Is > lngTotal
                            MsgBox "Vous avez distribué trop des repas", vbInformation
                        Case Is < lngTotal
                            MsgBox "Vous n'avez pas encore distribué tous les repas", vbInformation
                    End Select
                Next cel
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kmoreau48 Saturday, July 19, 2014 5:25 PM
    Friday, July 18, 2014 2:55 PM
  • Thanks a lot this is exactly what I needed
    Saturday, July 19, 2014 5:25 PM