MsgBox from another sheet

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

I have three sheets with numbers in column H these numbers are generated
from other sheets with a link.
I am looking to have a popup msgbox whenever the last number in the column
range Row 4 through 34 falls below a given number. And display a warning the
number is at this level. It would need to popup on the active sheet when the
user is inputting data.
If the number keeps going below this number each time the user goes to the
next sheet it should popup again or Every time the number changes and is
below the given number.
I am sure there must be a way but after a day of searching the Internet I
had to come here.
 
Sorry I thought it over.
I would be nice if any of the three sheets fall below the picked number the
message box would show the number for each sheet.
Example:
Sheet 1 hits 300
Message box would say "sheet 1 at 300 Sheet 2 at 1700 sheet 3 at 1235
These by the way are inventory levels which change on a daily basis.
 
How are the numbers derived?

Formula-driven and from where do they pick up the values?

Scenario..........Sheet1 A1 is 301 and you are working on Sheet3.

How would Sheet1 A1 get changed to fall to 300?

Same for other sheets.


Gord Dibben MS Excel MVP
 
The numbers on sheets1 2 and 3 come from data that is entered in all of 31
other sheets.
These sheets are used 1 sheet per day and a calculation form these other
sheets are linked to sheets 1 2 and three.
The column of data I want to look at is a calculation from the number before
less sales in G Ex: H4 =(if(G4<>0),H3-G4,0)
I have three areas in each of the 31 sheets each area represents the number
for the 3 sheets.
If the user enters a number in cell c4 it changes J12. J12, J13, and J14 is
the number that goes to the 3 sheets G? ?= the day of the month. Row 4
starts day 1 and row 5 = day 2.
 
This might get you started.

Copy/paste the code into Sheet1 sheet module only since you want that sheet
to be the driver.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
If .Value < 300 Then
MsgBox "Sheet1 A1 value is " & Sheets("Sheet1").Range("A1").Value & vbLf & _
"Sheet2 A1 value is " & Sheets("Sheet2").Range("A1").Value & vbLf & _
"Sheet3 A1 value is " & Sheets("Sheet3").Range("A1").Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

Adjust cell references to suit.


Gord
 
This is what I have now after a couple of changes:
However I would like to be able to assign a different number for each sheet
and have the popup happen whenever any one of the three sheets fall below
their assigned number.
as an example I want to assign the following
Gasolina Value < 2000
Alcool Value < 300
Diesel Value < 500
I know I can put the code in all three sheets but that may cause up to three
Msgboxes to pop up.
The last sheet that gets the calculation by the way is Alcool so the msgbox
would not have to appear until that sheet changes.

Thank you for your help.


Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("H38")
If .Value < 5000 Then
MsgBox "Your Gasolina Liters On Hand is : " &
Sheets("Gasolina").Range("H38").Value & vbLf & _
"Your Alcool Liters On Hand is : " & Sheets("Alcool").Range("H38").Value
& vbLf & _
"Your Diesel Liters On Hand is : " & Sheets("Diesel").Range("H38").Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub
 
I'll have a look at it. Not sure how to run all from one sheet_calculate
event.

No promises.........tee-time at 7:40am so won't be back for a few hours.


Gord
 
Back
Top