To exceute a Macro when worksheet is deactivted and data is changed

[Hari]

Hi,

I have asked this problem before and I wanted guidance on which site would
be having the info I need.

Dave P directed me to Chip and David M's site but Im lost .....

I have a macro by the name Macro1.

This macro basically takes data from a sheet called "Raw Data" and does some
consolidation of it and enters its results in othe worksheets.

I have pasted the code for Macro1 at the end of my signature.

I want that if any data in the range B:AG changes in "Raw Data" worksheet
changes due to actual data typing or due to pasting from another workbook
then when raw data worksheet is decativated Macro 1 may run.

In "my version" of VBA (crude) language I have put it below.

Private Sub Worksheet_Deactivate()

If Worksheet_Change = True then
Call Macro1
End if

End Sub

I understand that syntax is fully wrong but I dont know how to put it
correctly, please guide me.

Regards,
Hari
India



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/20/2004 by Hari P
'

'
Dim rowcount As Integer
Dim ok As Integer
Dim p As Integer



Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Consolidation of Raw data").Select
Range("b3:f65536").Select
Selection.ClearContents
Range("b365536").Select
Selection.ClearContents
Range("d3").Select

Sheets("Raw Data").Select
Range("e3").Select
ok = 0
rowcount = 0
Do While ok = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ok = 1
Else
rowcount = rowcount + 1
End If
Loop

rowcount = rowcount + 3

Range("j3").Formula = "=k3&TEXT(l3,"" dd-mmm-yy"")&TEXT(m3,"" dd-mmm-yy"")"
Range("j3").Select
Selection.AutoFill Destination:=Range("j3:j" & rowcount)
Columns("j:j").EntireColumn.AutoFit

Range("t3").Formula = "=u3&TEXT(v3,"" dd-mmm-yy"")&TEXT(w3,"" dd-mmm-yy"")"
Range("t3").Select
Selection.AutoFill Destination:=Range("t3:t" & rowcount)
Columns("T:T").EntireColumn.AutoFit

Range("B3:w" & rowcount).Select
Selection.Copy
Range("a3").Select
Sheets("Intermediate 1").Visible = True
Sheets("Intermediate 1").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("j2:j" & rowcount - 1).Select
Application.CutCopyMode = False
Selection.Cut
Range("e2").Select
Selection.Insert Shift:=xlToRight




Range("T2:T" & rowcount - 1).Select
Application.CutCopyMode = False
Selection.Cut
Range("f2").Select
Selection.Insert Shift:=xlToRight

Range("AZ2").Formula = "=MAX(L2,M2,v2,w2)"
Range("AZ2").Select
Selection.AutoFill Destination:=Range("AZ2:AZ" & rowcount - 1)

Rows("2:" & rowcount - 1).Select
Selection.Sort Key1:=Range("v2"), Order1:=xlDescending,
Key2:=Range("n2") _
, Order2:=xlDescending, Key3:=Range("m2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

Selection.Sort Key1:=Range("AZ2"), Order1:=xlDescending,
Key2:=Range("w2" _
), Order2:=xlDescending, Key3:=Range("v2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

Range("e2").Select
Range("e1:f" & rowcount - 1).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True

Range("B2:f" & rowcount - 1).Select
Selection.Copy


Sheets("Consolidation of Raw data").Select


Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("AT2").Value = rowcount

Sheets("Intermediate 1").Select
ActiveSheet.ShowAllData
Rows("2:65536").Select
Selection.Clear
Range("B2").Select
Sheets("Intermediate 1").Visible = xlVeryHidden
Sheets("Consolidation of Raw data").Select

Range("f3").Select
ok = 0
rowcount = 0
Do While ok = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then

If ActiveCell.Offset(0, -1).Value = "" Then

ok = 1

End If

End If

rowcount = rowcount + 1

Loop


rowcount = rowcount + 3

Range("aX2").Value = rowcount - 1


Range("aT3:aU65536").Select
Selection.ClearContents

Range("AT3").Formula = "=IF(b3<>"""",b3,"""")"

Range("g3").Formula = "=IF(ISERROR(VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$l$""&$AT$2),3,FALSE)),"""",VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$l$""&$AT$2),3,FALSE))"
Range("h3").Formula = "=IF(ISERROR(VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$m$""&$AT$2),4,FALSE)),"""",VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$m$""&$AT$2),4,FALSE))"
Range("i3").Formula = "=IF(ISERROR(VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$v$""&$AT$2),3,FALSE)),"""",VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$v$""&$AT$2),3,FALSE))"
Range("j3").Formula = "=IF(ISERROR(VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$w$""&$AT$2),4,FALSE)),"""",VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$w$""&$AT$2),4,FALSE))"
Range("k3").Formula = "=IF(COUNTIF(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2),E3)=0,"""",COUNTIF(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2),E3))"
Range("l3").Formula = "=IF(K3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2)=E3),--(INDIRECT(""'Raw
Data'!$n$3:$n$""&$AT$2)=""Yes"")))"
Range("m3").Formula = "=IF(K3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2)=E3),--(INDIRECT(""'Raw
Data'!$s$3:$s$""&$AT$2)=""Pass"")))"
Range("n3").Formula = "=IF(COUNTIF(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2),F3)=0,"""",COUNTIF(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2),F3))"
Range("o3").Formula = "=IF(N3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2)=F3),--(INDIRECT(""'Raw
Data'!$X$3:$X$""&$AT$2)=""Yes"")))"
Range("p3").Formula = "=IF(N3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2)=F3),--(INDIRECT(""'Raw
Data'!$Ag$3:$Ag$""&$AT$2)=""Pass"")))"


If rowcount <> 3 Then
Range("g33").Select
Selection.AutoFill Destination:=Range("g3" & rowcount)
Range("AT4").Formula =
"=IF(COUNTIF($AT$3:$AT3,b4)=0,IF(b4="""","""",b4),"""")"

If rowcount >= 5 Then
Range("AT4").Select
Selection.AutoFill Destination:=Range("AT4:AT" & rowcount - 1)
End If

End If

Range("ay2").Formula = "=COUNTIF(INDIRECT(""AU3:AU""&AX2),"""")"
Range("aw2").Formula = "=ax2-ay2"

Range("at3:at" & rowcount - 1).Select
Selection.Copy
Range("au3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Selection.Sort Key1:=Range("AU3"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("a3").Select

p = Sheets("Consolidation of Raw data").Cells(2, 49)
Sheets("Discrete data").Select

Range("b3:b65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("B3:b" & p).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=jobreqn.no."
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



Range("A3").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Ron de Bruin]

Hi Hari

Try something like this

In a normal module
******************************

Public Csheet As Boolean

Sub test()
MsgBox "Hi"
End Sub


In the worksheet module
********************************

Private Sub Worksheet_Activate()
Csheet = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Csheet = True
End Sub

Private Sub Worksheet_Deactivate()
If Csheet = True Then
test ' run the macro
End If
End Sub

 

[Hari]

Hi Ron,

Thanx a TON.

I understood what kind of manipulations you were doing by introducing a
dummy variable like Csheet. Too GOOD.

I modified it slightly in order to meet my requirements.

Just to tell you that in my case the Macro1 which I wanted to call was
itself modifying the contents of the sheet for which deactivation and data
changed was being tracked so it became something like a Infinite loop as
each time the macro code was changing the contents by copying and pasting in
to another sheet then macro code itself may be started running and I think
that was going adinfinitum. To fix that I introduced another dummy boolean
code like yours and somehow managed to pull it off.( I hope so!!!)

A minor help needed. Actually this Macro1 which Im calling does lot of data
manipulation/summary and at the end of it all it goes to a worksheet called
"Discrete data".

Now continuing on to the present thread I asked u that I want the macro to
run each time the sheet is deactivated and if the data is changed.

Now suppose I change the data and then deactivate the sheet and move to a
sheet called "Enhance. to be done" then macro1 would run and since at the
end of the Macro1 code I go to worksheet "Discrete data" I end up at sheet
"Discrete data" .

My doubt is it possible to remember (somewhere store the name of the newly
activated sheet) that deactivation was done by going to sheet "Enhance. to
be done" and Macro1 also goes to "Enhance. to be done" instead of always
ending up at "Discrete data" worksheet.

Regards,
Hari
India

Hi Hari

Try something like this

In a normal module
******************************

Public Csheet As Boolean

Sub test()
MsgBox "Hi"
End Sub


In the worksheet module
********************************

Private Sub Worksheet_Activate()
Csheet = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Csheet = True
End Sub

Private Sub Worksheet_Deactivate()
If Csheet = True Then
test ' run the macro
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" <[email protected]> wrote in message

news:[email protected]...

 

[Hari]

Hi Ron,

Thanx I could figure it out.

Actually I dint know the correct syntax for referring to the presently
activated sheet and going thru my previously pinched codes it turned out to
be "Activesheet.name" and with that I got what I wanted.

Thanx again for all your help..

Private Sub Worksheet_Deactivate()

Dim qwert As Variant
If Csheet = True Then
qwert = ActiveSheet.Name
Call Macro1 ' run the macro
Sheets(qwert).Activate
End If

End Sub


Regards,
Hari
India