Links not working an Excel spreadsheet with VBA buttons on it.

[Patrick]

I have got a budgeting spreadsheets in Excel 2003 as follows:
1) "Data Entry {1-10}" worksheets
2) On each data entry spreadsheets, there is an "aggregate" function, which
is invokable with a click on a Button. The code behind each button on each
of these data entry worksheet is as follows:
2.1) It Set ThisWorkbook.objCurWorkSheet =
Worksheets("currentWorksheetName")
2.2) It invokes ThisWorkbook.processItem
2.2.1) processItem is a workbook level global function that aggregate data
from the current data entry worksheet into a "Overall Operating budget"
worksheet
3) I have 3 seperate "Team operating budget" worksheet that function as
follows (they are intended to be a "view" into the "Overall Operating
budget" worksheet:
3.1) The values are obtained as hyperlinks into the "Overall Operating
Budget" worksheet
For instance cell G19 in "Team1 Operating budget" is set to "='Overall
Operating Budget'!G62"
3.2) "Team{1-3} Operating Budget" worksheets are locked

This has been working fine, but recently the linking seem to stop working
and values no longer seem to be linking to values in "Overall Operating
Budget" worksheet. I have the following observations:
1) Even if I unlock the "Team{1-3}Operating Budget" worksheets, the links
are not updating the value
2) The values are NOT updated when I close and re-open the worksheets
3) The only way the values seems to be updating is if I go into the cells
with linked values in "Team{1-3} Operatin Budget", press F2 to modify cell
(but actually makes no changes), then press ENTER, and the value is
refreshed instantly
4) If I make further changes in "Data Entry {1-10}", pressed the Aggregate
button to update values in the "Overall Operating Budget" worksheet which
change the value that the updated cell of 3 above is linked into, then the
value in the cell updated at 3 above does not change until I repeat step 3!!

How is this possible and how could I fix this?

 

[Wei-Dong XU [MSFT]]

Hi,

Thanks for posting in the group!

I have reviewed your thread. Currently I am finding somebody who could help
you on it. We will post back in the newsgroup as soon as possible.

If there is anything unclear, please feel free to post in the group and we
will follow up there.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang]

Hi Patrick,

To isolate the problem, I think we may try first to copy the excel file
onto another machine with the same office version to see the problem
persists, so that we will know if the problem is in the excel file or the
offce product environment.
If the problem can be reproduce on another machine, the problem may be
caused by the file itself, I think we may try to recreate an excel and
then copy the Macro to the the new one.
Otherside, we may try to repair installed the office product on the problem
machine to see if that works for you.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Patrick]

This is Very *weird* !

1) Problem is reproducible on other PCs also running Office 2003
Professional on Win XP Pro
2) I have to press F9 to get the links to reappear (I am 101% sure I didn't
previously have to do this)
3.1) If I am on Worksheet "Team1 Operating Budget", Cell E21, with a value
of "='Overall Operating Budget'!E10" with a value of 123
3.2) If I select Cell E21 on "Team1 Operating Budget" worksheet and press
Crtl+C to copy
3.2) If then move to cell E22 on "Team1 Operating Budget" worksheet and
press Crtl+V to paste, then cell E22 get a forumla of "='Overall Operating
Budget'!E11" *and* a value of 123 (when on E11, of "Overall Operating
Budget" the value is actually 222). I had to press F2 and Enter to get the
value updating!
3.3) If at 3.2 I instead select past special to paste formula, also the
paste doesn't get me a value of 222 !

 

[Patrick]

Would rather not have to go down that route! Since the XLS file contain
many different worksheets each with 2 VBA buttons in them that sets a global
variable denoting the worksheet being worked on before invoking a global
function defined at the Workbook level.

 

[Peter Huang]

Hi Patrick,

Yes, it would be a big project to recreate such a workbook. So I suggest
you create a new workbook and just in one cell input the formular which
linked to another sheet and then copy the formula into the next cell to see
if the link is updated so that we can judge if the excel application has
any problem.

If you can send me a reproduce sample I would be appreciated so that I can
forward it to our dev team for further troubleshooting. Or I think you may
try to contact MS PSS.
http://support.microsoft.com

If you still have any concern, please feel free to let me know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Patrick]

Hi Patrick,

Yes, it would be a big project to recreate such a workbook. So I suggest
you create a new workbook and just in one cell input the formular which
linked to another sheet and then copy the formula into the next cell to see
if the link is updated so that we can judge if the excel application has
any problem.

If you can send me a reproduce sample I would be appreciated so that I can
forward it to our dev team for further troubleshooting. Or I think you may
try to contact MS PSS.
http://support.microsoft.com

If you still have any concern, please feel free to let me know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Patrick]

Check out this zipped spreadsheet, which I have uploaded to
http://www.geocities.com/drkestrel/pSxltGpqjXQ16cKymlun/Budget0405DEMO.zip

1) Upon clicking "Aggregate Invoices" or "Undo" in worksheets
29000-29,81200,28000-29,28100-29, data would be feed into the "Operating
Budget" worksheet.
2) Cells corresponding to "Actual expenditure" in "Operating Budget- ITNET"
worksheet are linked to cell in "Operating Budget" worksheet.
3) For simplicity, I have removed other worksheets that feed data into or
link into "Operating Budget" .
4) the password for unprotected the sheet, etc. is pass

 

[Peter Huang]

Hi Patrick,

I can not reproduce the problem with the excel file you provided.
Here is my reproduce steps.
1. unprotected Operating Budget sheet with pass
2. input 1234 to E11
3, shift to sheet Operating Budget - ITNET
4. select E21, and press Ctrl +C
5. select E22, and press Ctrl +V
6, The E22's value will be 1,234 which is as expected.( I did not press any
other key, once I press Ctrl+V, the cell will be updated)

From the test, I think there is no error in the xls file you provide, so I
think there may be something wrong with the excel 2003. So you may try to
perform the simple test in my last post. If you can reproduce the problem,
I think you may try to repair install the office 2003 or even reinstall the
office 2003.

If you still have any concern, please feel free to let me know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Patrick]

The problem is NOT with the copy and paste!

1) As you enter "1234" incell E11 of "Operating Budget" worksheet, cell E22
of "Operatig Budget- ITNET" which has a value of "='Operating Budget'!E11"
should also instantly has the value changed to 1234
2) Cell E22 of "Operatig Budget- ITNET" remains unchanged until I hit the F9
button! This is the "bug"???????
3) Other worksheets (e.g. 29000-29, 81200, 28000-29, 28100-29) feed data
into th "Actual" section of the "Operating Budget" worksheet when the
"Aggregate invoices" button or the "Undo" button is clicked. Cells in the
"Actual" section of "Operating Budget - ITNET" link to cells in the
"Operating Budget" worksheet.

 

[Peter Huang]

Hi Patrick,

Thank you for the information, now I can reproduce the problem.
After I further troubleshooting, I finally find that you have set the
Calculation option to manual, and this setting is based on file.
Now to enable the auto calculation, you may follow the steps below.
1. open menu Tools/Options
2. select Calculation Tab
3, in the Calculation section, you will find the original file setting is
Manual, just set it to Automatic.

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.