advanced macros

[Guest]

Hi there,

I was just wondering if someone could help me with advanced macros. I am
working in excel for departmental budgeting and have currently 8 - 10 budgets
set up at any given time for monitoring, but what i would like to do is
create a macro that will monitor all of the worksheets at once and prompt me
if any of my margins reach 15% or less immediately upon excel startup. I
want excel to actually state which worksheet and cell has actually reached
this margin. Is this possible?

Thanks in advance.

 

[Guest]

Hi,

A couple of assumptions:-
1. The worksheet tab name is the budget name.
2. The value of 15% is in a cell somewhere (I've assumed A1)
3. the maximum permissable value is in a cell somewhere (I've assumed b1)

Try this:-

Private Sub Workbook_Open()
maxvalue = Worksheets("Sheet1").Range("b1").Value
For Each sh In ThisWorkbook.Worksheets
sh.Select
If ActiveSheet.Range("A1").Value > maxvalue _
And IsNumeric(ActiveSheet.Range("A1").Value) Then
MsgBox (ActiveSheet.Name & " is now at " &
ActiveSheet.Range("A1").Value)
End If
Next
End Sub

Mike

 

[Guest]

Hi Mike,

Thank you very much for your quick response. I will try your macro and let
you know. Your assumptions are workable.

Thanks,