If cell above value popup window

[Blue_Wings]

Hi ppl, i have this code, that when for example the cell U50 in Sheet1 is above the number difined in Sheet2 cell P15 it pops up a window saying that is above limit, it works well in an workbook but now i want to do the same to another workbook but i dont remember how does the "Val2" receives the value from Sheet1 U50 to test it, and do some code needs to be inside a function like a Private Sub Worksheet_Change(ByVal Target As Range) ?

Can anyone help me to refresh my memory? Thanks

Public Function MaximumExcededCPT2(Val2) As Boolean
    
    Dim MaxCompt2Weight As Range
    Set MaxCompt2Weight = Sheet2.Range("P15")
    
    If Val2 > MaxCompt2Weight Then
        MsgBox "Maximum Weight for compartment 2 is " & MaxCompt2Weight
        MaximumExcededCPT2 = True
    End If
    
End Function

[vlady]

Hello
How about this?
Using Macro...

Sub cpt()
    
   MaxCompt2Weight = Sheets(2).Range("P15").Value
    Val2 = Sheets(1).Range("A1").Value
    If Val2 > MaxCompt2Weight Then
        MsgBox "Maximum Weight for compartment 2 is " & MaxCompt2Weight
        MaximumExcededCPT2 = True
    End If


End Sub

[Blue_Wings]

I puted that inside the function:

Private Sub Worksheet_Change(ByVal Target As Range)

And it didn´t work...

But i changed to this:

 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim MaxCompt2Weight As Range
Set MaxCompt2Weight = Sheets(2).Range("P15")

Val2 = Sheets(1).Range("X38").Value
    If Val2 > MaxCompt2Weight Then
    MsgBox "Maximum Weight for Compartment 2 is " & MaxCompt2Weight
    End If

End Sub

And now it works Thanks

[Blue_Wings]

After all problem not solved... Because even when the cell is empty the popup box appears how can i avoid that ?

[Blue_Wings]

Now iy works with this:

 Public Function MaximumCPT5(Val5) As Boolean
    
    Dim MaxCompt5Weight As Range
    Set MaxCompt5Weight = Sheet(2).Range("V15")
    
    If Val5 > MaxCompt5Weight Then
        MsgBox "Maximum Weight for compartment 5 is " & MaxCompt5Weight
        MaximoExcedidoCPT5 = True
    End If
    
End Function

And receiving the "Val5" value true this instruction on a cell: =MaximumCPT5(LOADSHEET!U51)

But i have also a button to clean values with this:

Private Sub Btn_Clear_All_Fields_Click()
Sheet(1).Range("U47").Value = ""
End Sub

When i press the button to clean the values, even if the values are already cleaned, the popup box appears anyway anyone knows why?

Thanks.

[vlady]

Hello

I'm Using excel 2010 it works fine here doesn't display message when below the maximumCPTload
can you try to issue a ELSE before the ENDIF just to bypass the first IF. If the value is below the maximum.Just for experiment..

[Blue_Wings]

vlady thanks, i tried done that with the "else" but it didn´t work, imagine that the cells are already all clean (without values) and if you press the button "Clean Values" that calls the function ClearAllFields, all the pop up boxes with MsgBox will appear even more than one time (the MsgBox should only appear when i have a value imagine... superior to 9000), why do they keep on poping maybe 10 time each). and that is strange...

Note: Sheet1 in portugal Folha1.

Where is the code of Sheet1:

Private Sub Btn_Clear_All_Fields_Click()
Call ClearAllFields
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Definition of variables
        
    Dim MaxPaxComp0A As Range
    Dim MaxPaxComp0B As Range
    Dim MaxPaxComp0C As Range
    
    Dim MaxTakeOffWeight As Range
      
    'Check maximum values for all 5 compartments
    Dim MaxCompt1Weight As Range
    Dim MaxCompt2Weight As Range
    Dim MaxCompt3Weight As Range
    Dim MaxCompt4Weight As Range
    Dim MaxCompt5Weight As Range
    
    Dim ActualCompt1Weight As Range
    Dim ActualCompt2Weight As Range
    Dim ActualCompt3Weight As Range
    Dim ActualCompt4Weight As Range
    Dim ActualCompt5Weight As Range
    
    Set MaxPaxComp0A = Folha2.Range("E15")
    Set MaxPaxComp0B = Folha2.Range("G15")
    Set MaxPaxComp0C = Folha2.Range("I15")
    Set MaxTakeOffWeight = Folha6.Range("AD34")
    
    Set MaxCompt1Weight = Folha2.Range("N15")
    Set MaxCompt2Weight = Folha2.Range("P15")
    Set MaxCompt3Weight = Folha2.Range("R15")
    Set MaxCompt4Weight = Folha2.Range("T15")
    Set MaxCompt5Weight = Folha2.Range("V15")
    
    Set ActualCompt1Weight = Folha1.Range("U51")
    Set ActualCompt2Weight = Folha1.Range("X51")
    Set ActualCompt3Weight = Folha1.Range("AA51")
    Set ActualCompt4Weight = Folha1.Range("AD51")
    Set ActualCompt5Weight = Folha1.Range("AG51")
    
    
    'Check cell values if correspond to maximum cell values
    
    'Check if maximum weight for take-off is valid
    If Folha1.Range("AQ19").Value > Folha6.Range("AD34").Value Then
        MsgBox "Maximum Take-Off Weight allowed: " & MaxTakeOffWeight
    End If
    
    ' Check if Landing Weight is superior to Take-off fuel
    If Folha1.Range("AJ17").Value < Folha1.Range("AX17").Value Then
        MsgBox "Maximum Landing Weight superior to Take-Off Fuel!"
    End If

    'Check maximum PAX for compartment 0A
    If Folha1.Range("AY66").Value > Folha2.Range("E15").Value Then
    MsgBox "Maximum PAX for compartment 0A is " & MaxPaxComp0A
    End If
    
    'Check maximum PAX for compartment 0B
    If Folha1.Range("AY67").Value > Folha2.Range("G15").Value Then
    MsgBox "Maximum PAX for compartment 0B is " & MaxPaxComp0B
    End If
    
    'Check maximum PAX for compartment 0C
    If Folha1.Range("AY68").Value > Folha2.Range("I15").Value Then
    MsgBox "Maximum PAX for compartment 0C is " & MaxPaxComp0C
    End If
    

    'Check total weight of LMS if more than 999
    If Folha1.Range("AH70").Value > 999 Then
        MsgBox "Maximum Weight for LMC allowed: 999 Kg  "
        Else
    End If
    
   


   

End Sub

And this is the code in my Module1: (that have the code to clean all the cells values)

 'Check for compartment 1 maximum weight
Public Function MaximoExcedidoCPT1(Val1) As Boolean
    
    Dim MaxCompt1Weight As Range
    Set MaxCompt1Weight = Folha2.Range("N15")
    
    If Val1 > MaxCompt1Weight Then
        MsgBox "Maximum Weight for compartment 1 is " & MaxCompt1Weight
        MaximoExcedidoCPT1 = True
    End If
    
End Function

'Check for compartment 2 maximum weight
Public Function MaximoExcedidoCPT2(Val2) As Boolean
    
    Dim MaxCompt2Weight As Range
    Set MaxCompt2Weight = Folha2.Range("P15")
    
    If Val2 > MaxCompt2Weight Then
        MsgBox "Maximum Weight for compartment 2 is " & MaxCompt2Weight
        MaximoExcedidoCPT2 = True
    End If
    
End Function

'Check for compartment 3 maximum weight
Public Function MaximoExcedidoCPT3(Val3) As Boolean
    
    Dim MaxCompt3Weight As Range
    Set MaxCompt3Weight = Folha2.Range("R15")
    
    If Val3 > MaxCompt3Weight Then
        MsgBox "Maximum Weight for compartment 3 is " & MaxCompt3Weight
        MaximoExcedidoCPT3 = True
    End If
    
End Function

'Check for compartment 4 maximum weight
Public Function MaximoExcedidoCPT4(Val4) As Boolean
    
    Dim MaxCompt4Weight As Range
    Set MaxCompt4Weight = Folha2.Range("T15")
    
    If Val4 > MaxCompt4Weight Then
        MsgBox "Maximum Weight for compartment 4 is " & MaxCompt4Weight
        MaximoExcedidoCPT4 = True
    End If
    
End Function

'Check for compartment 5 maximum weight
Public Function MaximoExcedidoCPT5(Val5) As Boolean
    
    Dim MaxCompt5Weight As Range
    Set MaxCompt5Weight = Folha2.Range("V15")
    
    If Val5 > MaxCompt5Weight Then
        MsgBox "Maximum Weight for compartment 5 is " & MaxCompt5Weight
        MaximoExcedidoCPT5 = True
    End If
    
End Function

Public Function ClearAllFields()

'Priority Adress

Folha1.Range("A5").Value = ""
Folha1.Range("D5").Value = ""
Folha1.Range("L5").Value = ""
Folha1.Range("T5").Value = ""
Folha1.Range("AB5").Value = ""

'Recharge / Date / Time Initials
Folha1.Range("B9").Value = ""
Folha1.Range("J9").Value = ""
Folha1.Range("M9").Value = ""
Folha1.Range("T9").Value = ""

'Flight
Folha1.Range("A13").Value = ""

'Crew
Folha1.Range("AI13").Value = "2"
Folha1.Range("AK13").Value = "7"
Folha1.Range("AN13").Value = ""

'Date
Folha1.Range("AT13").Value = ""
Folha1.Range("AW13").Value = ""
Folha1.Range("AZ13").Value = ""

'Zero Fuel
'Folha1.Range("AI16").Value = "168000"

'Landing Weight
'Folha1.Range("AW15").Value = "180000"

'Trip Fuel
Folha1.Range("AX17").Value = "0"

'Take-off-Fuel
Folha1.Range("AJ17").Value = "0"

'Manual Dry Operating Weight
Folha1.Range("M19").Value = ""

'Manual Dry Operating Index
Folha1.Range("Q19").Value = ""

'Allowed Weight for Take-Off
Folha1.Range("AQ19").Value = "233000"

'======== Pantry Figures ========

'Pantry Code
Folha1.Range("AF28").Value = "A"


'======================= LEG 1 ====================

'(Leg1) Number of passengers in transit M F CH INF Cab. Baggadge
Folha1.Range("C34").Value = ""
Folha1.Range("E34").Value = ""
Folha1.Range("G34").Value = ""
Folha1.Range("I34").Value = ""
Folha1.Range("K34").Value = ""

'(Leg 1) Destination / Nº of Passengers M F CH INF Cab. Baggadge
Folha1.Range("A35").Value = ""
Folha1.Range("C35").Value = ""
Folha1.Range("E35").Value = ""
Folha1.Range("G35").Value = ""
Folha1.Range("I35").Value = ""
Folha1.Range("K35").Value = ""
' (Leg 1) Pax Weight
Folha1.Range("C37").Value = ""
Folha1.Range("E37").Value = ""
Folha1.Range("G37").Value = ""
Folha1.Range("I37").Value = ""
Folha1.Range("K37").Value = ""

'(Leg 1) (Transit) Weight Destribution:  1 2 3 4 5 / Remarks PAX  PAD
Folha1.Range("U34").Value = ""
Folha1.Range("X34").Value = ""
Folha1.Range("AA34").Value = ""
Folha1.Range("AD34").Value = ""
Folha1.Range("AG34").Value = ""

Folha1.Range("AP34").Value = ""
Folha1.Range("AR34").Value = ""

Folha1.Range("AV34").Value = ""
Folha1.Range("AX34").Value = ""

'(Leg 1) (Bulk) Weight Destribution: 1 2 3 4 5 / Remarks PAX  PAD
Folha1.Range("U35").Value = ""
Folha1.Range("X35").Value = ""
Folha1.Range("AA35").Value = ""
Folha1.Range("AD35").Value = ""
Folha1.Range("AG35").Value = ""

Folha1.Range("AP35").Value = ""
Folha1.Range("AR35").Value = ""

Folha1.Range("AV35").Value = ""
Folha1.Range("AX35").Value = ""

'(Leg 1) (Cargo) Distribution weight 1 2 3 4 5
Folha1.Range("U36").Value = ""
Folha1.Range("X36").Value = ""
Folha1.Range("AA36").Value = ""
Folha1.Range("AD36").Value = ""
Folha1.Range("AG36").Value = ""

'(Leg 1) (Mail) Distribution weight 1 2 3 4 5
Folha1.Range("U37:W37").ClearContents
Folha1.Range("X37").Value = ""
Folha1.Range("AA37").Value = ""
Folha1.Range("AD37").Value = ""
Folha1.Range("AG37").Value = ""



'SI Information
Folha1.Range("AP53").Value = ""

'NOTOC Information
Folha1.Range("AY56").Value = "NO"

'Notes
Folha1.Range("AO58").Value = ""

'Captain
Folha1.Range("AU61").Value = ""

'Alternate
Folha1.Range("AU62").Value = ""

'Flight Time
Folha1.Range("AU63").Value = ""

'======== Last Minute Changes ========

'Dest
Folha1.Range("U63").Value = ""
Folha1.Range("U64").Value = ""
Folha1.Range("U65").Value = ""
Folha1.Range("U66").Value = ""
Folha1.Range("U67").Value = ""
Folha1.Range("U68").Value = ""
Folha1.Range("U69").Value = ""

'Specification
Folha1.Range("X63").Value = ""
Folha1.Range("X64").Value = ""
Folha1.Range("X65").Value = ""
Folha1.Range("X66").Value = ""
Folha1.Range("X67").Value = ""
Folha1.Range("X68").Value = ""
Folha1.Range("X69").Value = ""

'CI Cpt
Folha1.Range("AE63").Value = ""
Folha1.Range("AE64").Value = ""
Folha1.Range("AE65").Value = ""
Folha1.Range("AE66").Value = ""
Folha1.Range("AE67").Value = ""
Folha1.Range("AE68").Value = ""
Folha1.Range("AE69").Value = ""

'PAX
Folha1.Range("AG63").Value = ""
Folha1.Range("AG64").Value = ""
Folha1.Range("AG65").Value = ""
Folha1.Range("AG66").Value = ""
Folha1.Range("AG67").Value = ""
Folha1.Range("AG68").Value = ""
Folha1.Range("AG69").Value = ""

'Weight
Folha1.Range("AH63").Value = ""
Folha1.Range("AH64").Value = ""
Folha1.Range("AH65").Value = ""
Folha1.Range("AH66").Value = ""
Folha1.Range("AH67").Value = ""
Folha1.Range("AH68").Value = ""
Folha1.Range("AH69").Value = ""


'================== Seating Condition ============

Folha1.Range("AY66").Value = ""
Folha1.Range("AY67").Value = ""
Folha1.Range("AY68").Value = ""


End Function

Thanks once again.

[Blue_Wings]

I dont know but... i was thinking, maybe because while it cleans the other cells values is allways checking the the cells with the MsgBox´s as well? and makes a scan in the function "Worksheet_Change(ByVal Target As Range)" ?

Is there any way to clean the cells without passing the check on the cell contents in "Worksheet_Change(ByVal Target As Range)" ?

[vlady]

I think so, because it always check the cell if empty and the loop is the Worksheet_Change you can use a module for this also instead of the SUb-Endsub procedures.

Like it will only check if you will run the macro(display the messages).

[Blue_Wings]

I think so, because it always check the cell if empty and the loop is the Worksheet_Change you can use a module for this also instead of the SUb-Endsub procedures.

Like it will only check if you will run the macro(display the messages).

Can you be more specific? I dont have that much knowledge.
Thanks

[Blue_Wings]

Does anyone have more ideas?
Because when i clean the cells if for example a msgbox had already appeared it will appear more 30 times when i press the button that calls the function that cleans all the cell, how can o avoid this?

Thanks