K
kaken6
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/micro...l+cell+change+trigger&rnum=4#0962d6e7cc40eb49
http://groups.google.ca/group/micro...stion+1+to+20+trigger&rnum=1#c00908a17e2edc07
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
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/micro...l+cell+change+trigger&rnum=4#0962d6e7cc40eb49
http://groups.google.ca/group/micro...stion+1+to+20+trigger&rnum=1#c00908a17e2edc07
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