Display formula in all worksheets
 

Need help...

My workbook has multiple worksheets. I am trying to create a macro
that will display the formula in all the worksheets with 1-step. The
following macro don't seems to work. Thanks for your help.


Sub ShowFormula()
'
' Display formula instead of value in all worksheets
'
Dim Sh As Worksheet
'
For Each Sh In ActiveWorkbook.Worksheets
ActiveWindow.DisplayFormulas = True
Next
End Sub


--
slc
------------------------------------------------------------------------
slc's Profile: http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=392786

Display formula in all worksheets
 

This worked for me.

Sub MyDisplayFormula()

ActiveWindow.DisplayFormulas = True

End Sub

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39278

Display formula in all worksheets
 

The macro which you described only works for the current activ
worksheet but not for the rest of the worksheets in the same workbook

--
sl
-----------------------------------------------------------------------
slc's Profile: http://www.excelforum.com/member.php...nfo&userid=143
View this thread: http://www.excelforum.com/showthread.php?threadid=39278

Display formula in all worksheets
 

ok. Use:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
shtName = ActiveSheet.Name
For Each sht In Worksheets
sht.Activate
ActiveWindow.DisplayFormulas = True
Next
Worksheets(shtName).Select
Application.ScreenUpdating = True

End Sub

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392786

Display formula in all worksheets
 

slc,
Here is one i have assigned to a button on my toolbar. It toggles o
and off.

'This is macro for showing formulas, toggle off and on.
'Keyboard shortcut is "ctrl and ~"
Sub formuladisplaytoggle()
If ActiveWindow.DisplayFormulas = False Then
ActiveWindow.DisplayFormulas = True
Else
ActiveWindow.DisplayFormulas = False
End If
End Sub

This help?
Dave

slc Wrote:
Need help...

My workbook has multiple worksheets. I am trying to create a macr
that will display the formula in all the worksheets with 1-step. Th
following macro don't seems to work. Thanks for your help.


Sub ShowFormula()
'
' Display formula instead of value in all worksheets
'
Dim Sh As Worksheet
'
For Each Sh In ActiveWorkbook.Worksheets
ActiveWindow.DisplayFormulas = True
Next
End Su

--
Piranh
-----------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043
View this thread: http://www.excelforum.com/showthread.php?threadid=39278

Display formula in all worksheets
 

Hi SLC,

Your sub fails because whilst you cycle through the worksheets, the active
window remains unchanged.

Try:

'========================
Public Sub ShowFormula()

'\\ Display formula instead of value in all worksheets

Dim SH As Worksheet
Dim SHcurrent As Worksheet

Set SHcurrent = ActiveSheet

Application.ScreenUpdating = False

For Each SH In ActiveWorkbook.Worksheets
SH.Activate
ActiveWindow.DisplayFormulas = True
Next

SHcurrent.Activate

Application.ScreenUpdating = True

End Sub
'<<========================

And, anticipating your next question...:


'========================
Public Sub HideFormulas()

'\\ Restore value display in all worksheets

Dim SH As Worksheet
Dim SHcurrent As Worksheet

Set SHcurrent = ActiveSheet

Application.ScreenUpdating = False

For Each SH In ActiveWorkbook.Worksheets
SH.Activate
ActiveWindow.DisplayFormulas = False
Next

SHcurrent.Activate

Application.ScreenUpdating = True

End Sub

'<<========================


---
Regards,
Norman



"slc" wrote in message
...

Need help...

My workbook has multiple worksheets. I am trying to create a macro
that will display the formula in all the worksheets with 1-step. The
following macro don't seems to work. Thanks for your help.


Sub ShowFormula()
'
' Display formula instead of value in all worksheets
'
Dim Sh As Worksheet
'
For Each Sh In ActiveWorkbook.Worksheets
ActiveWindow.DisplayFormulas = True
Next
End Sub


--
slc
------------------------------------------------------------------------
slc's Profile:
http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=392786

Display formula in all worksheets
 

Mangesh & Piranha,

Thanks. I managed to combined both and this works even better for me

--
sl
-----------------------------------------------------------------------
slc's Profile: http://www.excelforum.com/member.php...nfo&userid=143
View this thread: http://www.excelforum.com/showthread.php?threadid=39278

Display formula in all worksheets
 

slc,

Glad it helped.

Dave
slc Wrote:
Mangesh & Piranha,

Thanks. I managed to combined both and this works even better for me

--
Piranh
-----------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043
View this thread: http://www.excelforum.com/showthread.php?threadid=39278