Cell Change as a Trigger Macro

[kaken6]

Hello all,
I posted my question in the programming board here:
http://groups.google.ca/group/micro...lnk=st&q=kaken6&rnum=3&hl=en#69530d8a32d69d85
but I’m not sure if it was the correct location nor gotten any
responses, so I hope this is alright!

Essentially, what I’m wanting to do seems similar to the question
here:
http://groups.google.ca/group/micro...stion+1+to+20+trigger&rnum=1#c00908a17e2edc07

Norman Jones’ post seems to work for Neil, but I haven’t had such
luck.
What I’ve done (from the first link):

“This is what I have so far. It's all on the main module Right now
nothing is working.
I've decided to just check if the month has changed, it will serve my
purpose fine.
I named the cell containing the month "Month" and also the
subprogram.
------------------------------------------------
Sub Workbook_open()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Month") ‘I’m not sure what this
means, the workbook isn’t called month, its called Month Information
Form, or is this referencing the cell?
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="Month", _
RefersTo:=" "
End If
End Sub
Sub Month()
Dim rng As Range
Dim NME As Name
Set rng = Range("C4") '<--- C4 contains the check cell which
(=Month)
Set NME = ThisWorkbook.Names("Month")
If rng.Value <> Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") '<-- name of one of the sheets
I want updated. What do I do for the others?
Set pt = ws.PivotTable3 '<-- name of the pivot table when I
right click and go to properties
pt.PivotCache.Refresh
End If
End Sub

 

[Trevor Shuttleworth]

Not sure what you mean by the "main module". A WorkBook_Open event macro
needs to sit in the WorkBook Class Module ... the code page "behind" the
Workbook object.

Generally speaking, I would use a WorkSheet_Change event to monitor changes
to a cell. This would sit in the WorkSheet Class module for the sheet where
the cell exists.

Regards

Trevor


Hello all,
I posted my question in the programming board here:
http://groups.google.ca/group/micro...lnk=st&q=kaken6&rnum=3&hl=en#69530d8a32d69d85
but I'm not sure if it was the correct location nor gotten any
responses, so I hope this is alright!

Essentially, what I'm wanting to do seems similar to the question
here:
http://groups.google.ca/group/micro...stion+1+to+20+trigger&rnum=1#c00908a17e2edc07

Norman Jones' post seems to work for Neil, but I haven't had such
luck.
What I've done (from the first link):

"This is what I have so far. It's all on the main module Right now
nothing is working.
I've decided to just check if the month has changed, it will serve my
purpose fine.
I named the cell containing the month "Month" and also the
subprogram.
------------------------------------------------
Sub Workbook_open()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Month") '?I'm not sure what this
means, the workbook isn't called month, its called Month Information
Form, or is this referencing the cell?
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="Month", _
RefersTo:=" "
End If
End Sub
Sub Month()
Dim rng As Range
Dim NME As Name
Set rng = Range("C4") '<--- C4 contains the check cell which
(=Month)
Set NME = ThisWorkbook.Names("Month")
If rng.Value <> Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") '<-- name of one of the sheets
I want updated. What do I do for the others?
Set pt = ws.PivotTable3 '<-- name of the pivot table when I
right click and go to properties
pt.PivotCache.Refresh
End If
End Sub

 

[kaken6]

Not sure what you mean by the "main module". A WorkBook_Open event macro
needs to sit in the WorkBook Class Module ... the code page "behind" the
Workbook object.

Generally speaking, I would use a WorkSheet_Change event to monitor changes
to a cell. This would sit in the WorkSheet Class module for the sheet where
the cell exists.

Regards

Trevor


Hello all,
I posted my question in the programming board here:http://groups.google.ca/group/microsoft.public.excel.programming/brow...
but I'm not sure if it was the correct location nor gotten any
responses, so I hope this is alright!

Essentially, what I'm wanting to do seems similar to the question
here:http://groups.google.ca/group/microsoft.public.excel.programming/brow...

Norman Jones' post seems to work for Neil, but I haven't had such
luck.
What I've done (from the first link):

"This is what I have so far. It's all on the main module Right now
nothing is working.
I've decided to just check if the month has changed, it will serve my
purpose fine.
I named the cell containing the month "Month" and also the
subprogram.
------------------------------------------------
Sub Workbook_open()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Month") '?I'm not sure what this
means, the workbook isn't called month, its called Month Information
Form, or is this referencing the cell?
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="Month", _
RefersTo:=" "
End If
End Sub
Sub Month()
Dim rng As Range
Dim NME As Name
Set rng = Range("C4") '<--- C4 contains the check cell which
(=Month)
Set NME = ThisWorkbook.Names("Month")
If rng.Value <> Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") '<-- name of one of the sheets
I want updated. What do I do for the others?
Set pt = ws.PivotTable3 '<-- name of the pivot table when I
right click and go to properties
pt.PivotCache.Refresh
End If
End Sub

Thanks Trevor,
I put the Workbook_Open event in a class module.
However, when I go to Sheet 1 I get an error saying that it can't find
my Pivot Table and highlights:
Set pt = ws.PivotTable3 (even though thats what its called when I go
to Table Options.
Could someone tell me if I am referencing my cells/sheets correctly?

Thanks