D
Dewey
Hi,
I have a bit of an odd problem that has been bugging me for a few days
with so far no solution.
I have a large workbook that needs to run in manual calculation mode
where certain sheets are updated in a particular order using shift-F9.
I am trying to get some code to work that when one of these sheets is
updated it in turn initiates the update of another sheet (not part of
the order, although it may have to be if I can't get this work).
I am using the worksheets("Name").Calculate function to update the
other sheet and I have tried both calling it within a function and
then placing the function call in one of the cells on the first sheet.
excel
cell A1 =refresh_sheet()
vba
Function refresh_sheet()
worksheets("Name").Calculate
End Function
I have also tried using a cell change event private sub within the
sheet and referencing it to a volatile cell with the now() function in
it so that when shift-F9 is run it triggers the macro.
excel
cell A1 = Now()
vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
worksheets("Name").Calculate
End if
The problem seems to be that when I activate these from within excel,
either by shift-F9 or an F2-Enter refresh on the cell it doesn't
appear to run the command. However if I run the code from the vba
window (play button/F5) it works. I know that it has to be something
simple that I am missing.
If anyone knows how to fix this or can even offer another solution to
this problem that would be great.
Thanks in advance
Dwight
I have a bit of an odd problem that has been bugging me for a few days
with so far no solution.
I have a large workbook that needs to run in manual calculation mode
where certain sheets are updated in a particular order using shift-F9.
I am trying to get some code to work that when one of these sheets is
updated it in turn initiates the update of another sheet (not part of
the order, although it may have to be if I can't get this work).
I am using the worksheets("Name").Calculate function to update the
other sheet and I have tried both calling it within a function and
then placing the function call in one of the cells on the first sheet.
excel
cell A1 =refresh_sheet()
vba
Function refresh_sheet()
worksheets("Name").Calculate
End Function
I have also tried using a cell change event private sub within the
sheet and referencing it to a volatile cell with the now() function in
it so that when shift-F9 is run it triggers the macro.
excel
cell A1 = Now()
vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
worksheets("Name").Calculate
End if
The problem seems to be that when I activate these from within excel,
either by shift-F9 or an F2-Enter refresh on the cell it doesn't
appear to run the command. However if I run the code from the vba
window (play button/F5) it works. I know that it has to be something
simple that I am missing.
If anyone knows how to fix this or can even offer another solution to
this problem that would be great.
Thanks in advance
Dwight