Hello,
I've been trying to figure this out for a couple days and I really am
unsure on what to do. I'm pretty sure its quite easy to solve, its
just that I've never used VBA code before besides copying and pasting
a few times, so I'm quite lost with how to apply other people's help
to my situation when it comes to something complex (at least for me!).
I know this question looks long, but I'm pretty sure it has the answer
in it as well, so I hope it doesn't take much of your time!

What I'm looking to do is update 3 worksheets (called Sheet 1, Sheet 2
and Sheet 3 (with the spaces between the number and "Sheet")) whenever
one or both of two cells on another worksheet is changed from drop-
down menus (I've used Data Valuation to only allow certain Years and
Months).

Worksheet name: "Month Information Form"
B4 contains a Month Name (January, February..etc)
B5 contains a year number (2001, 2002...etc)
B11 contains a formula combining the values two above cells, so that
if one of these is changed, the change will affect this cell. As
such, this seems more logical to look at for update purposes.

I know this is possible to do, as it has been addressed before in
these groups:
http://groups.google.ca/group/micros...62d6e7cc40eb49
http://groups.google.ca/group/micros...0908a17e2edc07
And something similar has been looked at on the following site:
http://www.cpearson.com/excel/events.htm

It seems that having another cell to reference if the cell's value has
been changed is the best way to approach the situation (cell C4 = B4,
cell C5 = B5, cell C11=B11 would work as my column C is blank) Norman
Jones' post (available in the second google group link) which I will
post has seemed to have success:

"Perhaps you could consider using a helper cell, monitor the value of
the
helper cell with the Worksheet_Calculate event and compare this to the
value
of a defined name.
For example:
(1) In a selected helper cell (say) D1, enter the formula:
=A1
where A1 represents the question number cell.
The helper cell could be hidden or behind (say) the spinner.
(2) In a standard module, paste the following code
'=============
Sub RunOnce()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Question_Number")
If Err.Number < 0 Then
ThisWorkbook.Names.Add Name:="Question_Number", _
RefersTo:=" "
End If
End Sub
'<<=============
The above code only needs to be run once to initialise the defined
name.
Obviously, you could equally define the name manually.
'=============
Sub QuestionNumber()
Dim rng As Range
Dim NME As Name
Set rng = Range("D1")
Set NME = ThisWorkbook.Names("Question_Number")
If rng.Value < Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
MsgBox "Neil's code runs here"
End If
End Sub
'<<=============
Replace the message box with your pre-written processing code.
In the worksheet's code module paste the following code:
'=============
Public Sub Worksheet_Calculate()
Call QuestionNumber
End Sub
'<<============= "

I hope he doesn't mind me posting his solution! Back to the issue at
hand, right now the three worksheets are updating as follows:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") <-- this name changes depending on the
worksheet
Set pt = ws.PivotTables(1)

pt.PivotCache.Refresh
End Sub

I thought this would be a great idea to have them updated when opened,
but it means the user has to access these worksheets before worksheets
that draw info from the pivot tables on these sheets will work - which
may get quite confusing as I'm not going to be the person running this
file.

I'm not sure what code goes into new modules and which goes into the
code for the worksheet, or if I have to a set of code if for either
cell - B4 and B5 or just B11)
I've tried to play around with getting the formulas to work, but when
I type "Month Information Form" to replace "ThisWorkbook.Names" I run
into problems - I don't think VBA likes the spaces in the sheet name
(should I use underscores/quotations?) Also, do I just replace
"ThisWorkbook" and leave ".Names" at the end? Under the first set of
coding, it states: "RefersTo:=" " " - should I change this to
reference some cell?

If someone could so kindly just tell me where to input each code (in
the "Month Information Form" sheet, the other sheets to be refreshed,
a module?) and if I need to keep the .Name, I would be very
appreciative. I apologize for needing such help, programming has
never been my forte - I was always one of the worst in the class when
I tried it in highschool.

Thank you so much again,
Stefan

On Mar 28, 11:02 am, wrote:
Hello,
I've been trying to figure this out for a couple days and I really am
unsure on what to do. I'm pretty sure its quite easy to solve, its
just that I've never used VBA code before besides copying and pasting
a few times, so I'm quite lost with how to apply other people's help
to my situation when it comes to something complex (at least for me!).
I know this question looks long, but I'm pretty sure it has the answer
in it as well, so I hope it doesn't take much of your time!

What I'm looking to do is update 3 worksheets (called Sheet 1, Sheet 2
and Sheet 3 (with the spaces between the number and "Sheet")) whenever
one or both of two cells on another worksheet is changed from drop-
down menus (I've used Data Valuation to only allow certain Years and
Months).

Worksheet name: "Month Information Form"
B4 contains a Month Name (January, February..etc)
B5 contains a year number (2001, 2002...etc)
B11 contains a formula combining the values two above cells, so that
if one of these is changed, the change will affect this cell. As
such, this seems more logical to look at for update purposes.

I know this is possible to do, as it has been addressed before in
these groups:http://groups.google.ca/group/micros...ogramming/brow...
And something similar has been looked at on the following site:http://www.cpearson.com/excel/events.htm

It seems that having another cell to reference if the cell's value has
been changed is the best way to approach the situation (cell C4 = B4,
cell C5 = B5, cell C11=B11 would work as my column C is blank) Norman
Jones' post (available in the second google group link) which I will
post has seemed to have success:

"Perhaps you could consider using a helper cell, monitor the value of
the
helper cell with the Worksheet_Calculate event and compare this to the
value
of a defined name.
For example:
(1) In a selected helper cell (say) D1, enter the formula:
=A1
where A1 represents the question number cell.
The helper cell could be hidden or behind (say) the spinner.
(2) In a standard module, paste the following code
'=============
Sub RunOnce()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Question_Number")
If Err.Number < 0 Then
ThisWorkbook.Names.Add Name:="Question_Number", _
RefersTo:=" "
End If
End Sub
'<<=============
The above code only needs to be run once to initialise the defined
name.
Obviously, you could equally define the name manually.
'=============
Sub QuestionNumber()
Dim rng As Range
Dim NME As Name
Set rng = Range("D1")
Set NME = ThisWorkbook.Names("Question_Number")
If rng.Value < Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
MsgBox "Neil's code runs here"
End If
End Sub
'<<=============
Replace the message box with your pre-written processing code.
In the worksheet's code module paste the following code:
'=============
Public Sub Worksheet_Calculate()
Call QuestionNumber
End Sub
'<<============= "

I hope he doesn't mind me posting his solution! Back to the issue at
hand, right now the three worksheets are updating as follows:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") <-- this name changes depending on the
worksheet
Set pt = ws.PivotTables(1)

pt.PivotCache.Refresh
End Sub

I thought this would be a great idea to have them updated when opened,
but it means the user has to access these worksheets before worksheets
that draw info from the pivot tables on these sheets will work - which
may get quite confusing as I'm not going to be the person running this
file.

I'm not sure what code goes into new modules and which goes into the
code for the worksheet, or if I have to a set of code if for either
cell - B4 and B5 or just B11)
I've tried to play around with getting the formulas to work, but when
I type "Month Information Form" to replace "ThisWorkbook.Names" I run
into problems - I don't think VBA likes the spaces in the sheet name
(should I use underscores/quotations?) Also, do I just replace
"ThisWorkbook" and leave ".Names" at the end? Under the first set of
coding, it states: "RefersTo:=" " " - should I change this to
reference some cell?

If someone could so kindly just tell me where to input each code (in
the "Month Information Form" sheet, the other sheets to be refreshed,
a module?) and if I need to keep the .Name, I would be very
appreciative. I apologize for needing such help, programming has
never been my forte - I was always one of the worst in the class when
I tried it in highschool.

Thank you so much again,
Stefan

Sorry, it appears that I misstated one thing, when I created the pivot
tables I linked them together, so when one updates they all seem to.
Thus, I only have code on one of my worksheets appearing as follows:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
worksheet
Set pt = ws.PivotTables(1)


pt.PivotCache.Refresh
End Sub

Hope that didn't add confusion

Thanks,
Stefan

On Mar 28, 11:18 am, wrote:
On Mar 28, 11:02 am, wrote:





Hello,
I've been trying to figure this out for a couple days and I really am
unsure on what to do. I'm pretty sure its quite easy to solve, its
just that I've never used VBA code before besides copying and pasting
a few times, so I'm quite lost with how to apply other people's help
to my situation when it comes to something complex (at least for me!).
I know this question looks long, but I'm pretty sure it has the answer
in it as well, so I hope it doesn't take much of your time!

What I'm looking to do is update 3 worksheets (called Sheet 1, Sheet 2
and Sheet 3 (with the spaces between the number and "Sheet")) whenever
one or both of two cells on another worksheet is changed from drop-
down menus (I've used Data Valuation to only allow certain Years and
Months).

Worksheet name: "Month Information Form"
B4 contains a Month Name (January, February..etc)
B5 contains a year number (2001, 2002...etc)
B11 contains a formula combining the values two above cells, so that
if one of these is changed, the change will affect this cell. As
such, this seems more logical to look at for update purposes.

I know this is possible to do, as it has been addressed before in
these groups:http://groups.google.ca/group/micros...ogramming/brow......
And something similar has been looked at on the following site:http://www.cpearson.com/excel/events.htm

It seems that having another cell to reference if the cell's value has
been changed is the best way to approach the situation (cell C4 = B4,
cell C5 = B5, cell C11=B11 would work as my column C is blank) Norman
Jones' post (available in the second google group link) which I will
post has seemed to have success:

"Perhaps you could consider using a helper cell, monitor the value of
the
helper cell with the Worksheet_Calculate event and compare this to the
value
of a defined name.
For example:
(1) In a selected helper cell (say) D1, enter the formula:
=A1
where A1 represents the question number cell.
The helper cell could be hidden or behind (say) the spinner.
(2) In a standard module, paste the following code
'=============
Sub RunOnce()
Dim NME As Name
On Error Resume Next
Set NME = ThisWorkbook.Names("Question_Number")
If Err.Number < 0 Then
ThisWorkbook.Names.Add Name:="Question_Number", _
RefersTo:=" "
End If
End Sub
'<<=============
The above code only needs to be run once to initialise the defined
name.
Obviously, you could equally define the name manually.
'=============
Sub QuestionNumber()
Dim rng As Range
Dim NME As Name
Set rng = Range("D1")
Set NME = ThisWorkbook.Names("Question_Number")
If rng.Value < Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
MsgBox "Neil's code runs here"
End If
End Sub
'<<=============
Replace the message box with your pre-written processing code.
In the worksheet's code module paste the following code:
'=============
Public Sub Worksheet_Calculate()
Call QuestionNumber
End Sub
'<<============= "

I hope he doesn't mind me posting his solution! Back to the issue at
hand, right now the three worksheets are updating as follows:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1") <-- this name changes depending on the
worksheet
Set pt = ws.PivotTables(1)

pt.PivotCache.Refresh
End Sub

I thought this would be a great idea to have them updated when opened,
but it means the user has to access these worksheets before worksheets
that draw info from the pivot tables on these sheets will work - which
may get quite confusing as I'm not going to be the person running this
file.

I'm not sure what code goes into new modules and which goes into the
code for the worksheet, or if I have to a set of code if for either
cell - B4 and B5 or just B11)
I've tried to play around with getting the formulas to work, but when
I type "Month Information Form" to replace "ThisWorkbook.Names" I run
into problems - I don't think VBA likes the spaces in the sheet name
(should I use underscores/quotations?) Also, do I just replace
"ThisWorkbook" and leave ".Names" at the end? Under the first set of
coding, it states: "RefersTo:=" " " - should I change this to
reference some cell?

If someone could so kindly just tell me where to input each code (in
the "Month Information Form" sheet, the other sheets to be refreshed,
a module?) and if I need to keep the .Name, I would be very
appreciative. I apologize for needing such help, programming has
never been my forte - I was always one of the worst in the class when
I tried it in highschool.

Thank you so much again,
Stefan

Sorry, it appears that I misstated one thing, when I created the pivot
tables I linked them together, so when one updates they all seem to.
Thus, I only have code on one of my worksheets appearing as follows:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
worksheet
Set pt = ws.PivotTables(1)

pt.PivotCache.Refresh
End Sub

Hope that didn't add confusion

Thanks,
Stefan- Hide quoted text -

- Show quoted text -

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")
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.
Set pt = ws.PivotTable3 '<-- name of the pivot table when I
right click and go to properties
pt.PivotCache.Refresh
End If

End Sub
----------------------------------------------------

Then on the worksheets with the pivot tables I have
----------------------------------------------------
Public Sub Worksheet_Calculate()
Call Month
End Sub
----------------------------------------------------
can you see anything wrong with what I'm doing?