Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Issue
Hi...
I have a fairly complex spreadsheet. Amazingly, formulas don't work unless I enter the cell and press return and then the value shows. I can't do this for every cell. Why is this happening? and is there any code I could run in my auto_open to prevent it happening? Auto calculation is fully enabled. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Issue
Hi, Sometimes Excel can incorrectly believe that the cells (or precedents) are (or you want them to be) formatted as text... *Try find & replace all on the "=" sign ie put the equals sign in both the find & the replace fields. This may cause Excel to "wake up". *Check the cell formatting using [F5] - Special & seeing what is selected when you choose various options. *Try changing the cell formatting... hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=568250 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Issue
Hi...
No change. Is there not some code that could poke Excel when I open the file? Thanks Gordon... "broro183" wrote: Hi, Sometimes Excel can incorrectly believe that the cells (or precedents) are (or you want them to be) formatted as text... *Try find & replace all on the "=" sign ie put the equals sign in both the find & the replace fields. This may cause Excel to "wake up". *Check the cell formatting using [F5] - Special & seeing what is selected when you choose various options. *Try changing the cell formatting... hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=568250 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Issue
Hi Gordon, Sorry for not getting back to you sooner - I've been away from home. I'm just learning too but I had a play & came up with the following (hard to test b/c I don't have your problem at the moment), try pasting the following code into your "thisworkbook" module: Option Explicit Private Sub Workbook_Open() 'KickStartCalcs Application.ScreenUpdating = False 'found this when playing - it may help... If Application.CalculationState = xlDone Then MsgBox "Calculation is uptodate when file opened." Else MsgBox "Needs to calculate - being done now..." Application.CalculateFull End If Dim ws As Worksheet Dim OriginalSheet As Worksheet Dim OriginalCell As Range Dim EqtnRange As Range Dim cell As Range 'To id original location for return after macro has run. Set OriginalSheet = ActiveSheet Set OriginalCell = ActiveCell For Each ws In ThisWorkbook.Worksheets With ws ..Select '23 was the value when I recorded a macro using "go to" all _ types of formulae ([F5], Special, Formulas - all ticked) Set EqtnRange = .Cells.SpecialCells(xlCellTypeFormulas, 23) For Each cell In EqtnRange 'The following should save the need for clicking _ into all the cells cell.Activate Next cell End With Next ws 'return to starting point & free memory OriginalSheet.Select OriginalCell.Select Set EqtnRange = Nothing Set OriginalSheet = Nothing Set OriginalCell = Nothing MsgBox "Every cell with a formula in this workbook has been activated." Application.ScreenUpdating = True End Sub hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=568250 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue with the Camera Button for creating dynamically updating pic | Excel Discussion (Misc queries) | |||
Speed Issue after updating cells | Excel Discussion (Misc queries) | |||
When updating a worksheet, how do I create a link updating the sa. | Excel Worksheet Functions | |||
Pivot Table Updating Issue | Excel Programming | |||
Issue updating XLS using ADO | Excel Programming |