Hi,
Nitin: u can send me the file at mala_rs_singh@rediffmail.com - but I abandoned 123 about 5 years ago and don't have it installed on my system - but i'll try to check it out elsewhere - pl. also tell me which version of 123 u're using.
Yakpol: The sub Display_Formulas is a very good idea. Only reversing the process might cause problems with some genuinely bonafide cells containing strings beginning with "=". Such cells are not unusual in engineering spreadsheets in which computational steps are shown.
Everyone: Hope u all don't find me tiringly verbose - its the only way I know how...Don't be intimidated by the size of what follows - a good portion of it is just copy/paste material.
I applied my mind some more to the problem and came up with something I think u'll find interesting ...
It involves the use of the 'Conditional Formatting' feature available in Excel 97 onwards and (of course) VBA.
The idea was that if the active cell were highlighted with special formatting in both windows (say italics, border around and grey fill) it would be stand out in the inactive window, thereby making it easy to locate.
Note that this would apply only to the ACTIVE cell - i.e. as soon as u select some other cell in any of the windows, the new cell becomes the active cell - the old cell then should appear normal and the italics, border around and grey fill conditional format would apply to this cell.
To achieve this we need to do the following:
(I repeat the code above in case there may have been some changes):
1. In the workbook insert a new module and enter (or copy/paste) the following code:
Public FormulaWin As Window, OriginalWin As Window
Public FormulaMode As Boolean, ActAddr As Range
Sub SetFormMode()
Set ActAddr = Range("ActiveCellAddr"
Set OriginalWin = ActiveWindow
ActiveWindow.NewWindow
Set FormulaWin = Windows(2)
Windows.Arrange ArrangeStyle:=xlTiled, ActiveWorkbook:=True, SyncHorizontal:=True, SyncVertical:=True
FormulaWin.DisplayFormulas = True
FormulaMode = True
End Sub
Sub ResetFormMode()
If Windows.Count = 2 Then
FormulaMode = True
FormulaWin.Close
Windows(1).WindowState = xlMaximized
End If
FormulaMode = False
End Sub
Sub WhatToSet()
If FormulaMode Then
ResetFormMode
ActAddr.ClearContents
Else
SetFormMode
End If
End Sub
2. In the event module for Sheet1 copy/paste:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If FormulaMode Then
ActAddr = ActiveCell.Address
End If
End Sub
3. Assign ctrl+f hotkey to the macro 'WhatToSet'. This macro is designed to work as a toggle to set the view mode off and on.
4. Name the cell 'IV1'
ActiveCellAddr - it is presumed u have no data in this cell. In the unlikely event that u do, u can name any cell which u're sure will NEVER contain data with this name.
5.Select cell A1 and do Insert>Name>Define, in the name box type
CFrm and in the Refers to: box type
=(ADDRESS(ROW(!A1),COLUMN(!A1),1)=ActiveCellAddr)
6. Select the entire worksheet, or, better still, the range containing data and do Format>Conditional formatting... In Condition1 (of course this presumes u've not used conditional formatting on Sheet1 for any other reason) select
formula is instead of
cell value is int the dropdown combo on the left. In the ref-edit box to the right type
=CFrm. Click Format... and set the type of formatting u want for the highlighted cell (I used italics, border around and grey fill).
7. Now just hit ctrl+f and navigate in any of the windows to your heart's content...also, ctrl+f to land!!
Of course everything from 4 to 6 above could be automated in VBA, the mechanism could be extended to include other sheets, the conditional formatting (consumes memory) could be removed or applied depending upon whether the toggle disabled or enabled the formula view mode - the amount of fine tuning u can do depends upon your wish and need and your imagination... you won't find Excel wanting.
Moral of the story: Most good things originate from the thought 'If only I could...' and the endeavour to make it real.
Good luck!!
