• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide Formula

Raja


No


But you can protect and hide formulas in the relevent cells and leave the rest of the worksheet available for editing even though the formulas are hidden elsewhere


Otherwise you can save the file as a PDF etc and that hides the formulas
 
@Raja


Hi


Why Did you need that type, you don't want to show the formulas to some one which type of formulas you are used in your file.


SP
 
Hello Raja,


What about doing the following:


1)Uncheck the formula bar (Excel office button>excel option>advanced>scroll down a bit and uncheck "show formula bar"). Hence user will not be able see the formula in the formula bar


2)Protect the sheet (F2 will not work to show the formula in the edit mode)


Kaushik
 
@sgmpatnaik:


My file has a set of tables which has the formula =REPT("|",E3/"00:05:00") to a range of cells.


Now the problem is the data in the table is inconsistent and often tends to change and this makes me to have the sheet unprotected


So shared this query here to know if I can hide the formulae without protecting sheet


Raja
 
@Kaushik:


I'm with an assumption that even if I follow your first pointer still the end user would be able to see the formular bar and formulae as well because unchecking the formula bar option at my end will be ineffective to the other users.


Please correct me if I'm wrong


Raja
 
Yes you are correct Raja...My apologies!!


But you can overcome this challenge by writing the below two lines of code. Double click on Thisworkbook (under project explorar) and write this code:


Private Sub Workbook_Open()


Application.DisplayFormulaBar = False


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayFormulaBar = True

End Sub


'Hope the code is clear to you..


But I think this will not solve your problem as user still can press F2/ double click on the cell (as you do not want to protect the sheet).


Give me some time..I will try to come up with a solution, if any.


Kaushik
 
Hi Raja,

[list type=decimal]
[*]Excel.. By Default.. all cells are Locked and Formula is Visible..
[*]To check.. just view any Cell's Property..(Ctrl + 1) and view Protection Tab
[*]But it has no Use.. If you don't protect the sheet

[list type=decimal]So you want only Few Cell (where is Formula.. )
need to Hide Formula.. Select Only those Cell > Cell Property > Protection > Check Hidden
[/list type=decimal]
and Few Cell (Where Data often trends to change)
Need to Unlock..So that Protection has no Effect.. Select Only those cell > Cell Property > Protection > UncCheck Locked..
[/list type=decimal]

Please Change as per requirement.. and give your EndUser Protected Sheet.. So that they can Enter Data only where you want..


Please let us know.. If it solve your problem.


Regards,

Deb
 
@Raja


Hi


i am not sure but try this code hope it will clear / give some idea to you


say you have enter a formula in A8 like (=SUM(A1:A6) when we select the cell A8 then it will display the Formula in Formula bar but with the below code you will get only the value


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static TheFormula As String

If Target.Address = "$A$8" Then

With Target

TheFormula = .Formula

.Value = .Value

End With

Else

With Range("A8")

If Not .HasFormula Then

.Formula = TheFormula

End If

End With

End If

End Sub


NOTE: THE FORMULA WILL WORK BUT WE CAN'T SEE THE FORMULA AND WE CAN'T EDIT


if any thing wrong then please advise


Thanks


SP
 
@Kaushik:

Thanks a lot for the code but as said this code comes with a limitation, I need to wait still you come with something which does exactly what I am looking for.


@Deb:

Manys thanks for your effort and time. As said before the data in the sheet is very inconsistent and has multiple changes on day to day basis. Now this makes my job tedious where each time I have check/uncheck the "Hidden" option. For me "Protect Sheet" option is something which I need to completely rule out.


@SP:

Thank you so much for your approach and your code works brilliantly.

But still need your help as the formulaes are spread across the sheet, now how do I define the range for the code, ie in the above code you have given the range as "A8" but my range is "R3:R12" "V3:V12" and so on.

Please get your code revised where I can give multiple ranges


Guys, each one of you have been putting best possible efforts but unfortunately I need to pick the one which hits my requirement so please dont mind if I point out few shortcomings of your solutions.


Regards,

Raja
 
@Raja


Hi


Try This


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range

Static Started As Boolean

Static Cell As Range

Static TheFormula As String

Set Rng = Range("A1:A20, E1:E20, H1:H20")

If Not Application.Intersect(ActiveCell, Rng) Is Nothing Then

If Not Cell Is Nothing Then

Cell.Formula = TheFormula

End If

Set Cell = ActiveCell

With Cell

TheFormula = .Formula

.Value = .Value

End With

Started = True

Else

If Started Then

With Cell

.Formula = TheFormula

End With

End If

End If

End Sub


Thanks


SP
 
@SP:

Thank you so much.


I have applied to my sheet and found it working as I desired.


Am in process of applying this code to a couple of my reports and I might get back to you in case if something blocks my task


Thanks bro


Raja
 
@ Raja


Hi


glad i solve your problem and you are welcome with any problem


Thanks


SP


Note: WHEN YOU ARE GOING TO EXPERIMENT WITH ANY NEW CODE THEN PLEASE TAKE A BACKUP OF THAT FILE IT IS SAFE FOR YOU AND YOUR DATA
 
i m really so sorry i applied your code dear sgmpatnaik and i have some formula in A2:a20 and then i press ctrl + `

then the formula was infront of me.............ha ha ha.....


regards

CA Mahaveer Somani
 
@ Mahaveer


Hi


Sorry for my late replay, just i was went for some days on PONGAL leaves


good point out and when you press the short cut key or insert row then the formula is display due to we use the range option


when we insert the cell or row then the formula column is out of range and it's display the formula


if you can change the code as


Set Rng = Range("A1:A20, E1:E20, H1:H20")


changed code is


Set Rng = Range("A:A, E:E, H:H")


with the change option, when you insert the row or cell then we get the Value not formula of that cell and the formula is not working for that row and when we replace the cell in same place then it's continuing his work


next i have another code which is protect the formula column, i mean in the active sheet cell has a formula then it's protect the sheet and hide the formula if there is no formula then it's release the cell from protect try this


place the code is this work book


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


Dim rFormulaCheck As Range


On Error Resume Next


Sh.Unprotect Password:="Secret"


With Selection


.Locked = False


.FormulaHidden = False


End With


If Target.Cells.Count = 1 Then


If Target.HasFormula Then


With Target


.Locked = True


.FormulaHidden = True


End With


Sh.Protect Password:="Secret", UserInterFaceOnly:=True


End If


ElseIf Target.Cells.Count > 1 Then


Set rFormulaCheck = Selection.SpecialCells(xlCellTypeFormulas)


If Not rFormulaCheck Is Nothing Then


With Selection.SpecialCells(xlCellTypeFormulas)


.Locked = True


.FormulaHidden = True


End With


Sh.Protect Password:="Secret", UserInterFaceOnly:=True


End If


End If


On Error GoTo 0


End Sub


Thanks for your point out


Keep watching


SP
 
ha ha ha........its really magic @sgmpatnaik

n its really very interesting code....

but still i can see the formula......


Regards

CA Mahaveer Somani
 
You must log in or register to reply here.
Back
Top