Vba code to hide formulas

[KalaiP]

Hello everyone,

I want to hide formulas in formula bar by using macro code without protecting sheets/excel. User only can see the result that driven by the formula. Could anyone advice/provide me how to do this?

Thanks in advance..

Kalai

[Syrkrasi]

not possible; however, you could change the cell(s) format to value

[KalaiP]

Hi Syrkrasi,

Thanks, ok. is there any vba code to protect and hide formulas in the formula bar?

Kalai

[mehmetcik]

Not Strictly True.

You can use a macro that runs whenever you select a cell.

The macro would copy the formula into a string.

The macro would then copy paste values into the cell.

When another cell is selected the macro is run again.

The macro re-enters the formula into the cell.

Right Click on your sheet name at the bottom of excel and select view code.

Paste this code in to the module that opens and then close it.

Please Login or Register  to view this content.

I suppose you could amend it a bit.

Please Login or Register  to view this content.

[Syrkrasi]

Not Strictly True.

You can use a macro that runs whenever you select a cell...
.

It is easy to Lock and hide the cell that contains formulas; however, you will still need to protect the sheet in order to initiate its settings. Hence, this is what the OP is trying to avoid doing.

[Syrkrasi]

I received an error when I clicked into the sheet

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

error shows this line: Range(Add1).Formula = Formulal

[mehmetcik]

The Macro is in the sheet named Test.

You cannot open the macro in that sheet with the test code.

Please Login or Register  to view this content.

[skywriter]

The Macro is in the sheet named Test.

You cannot open the macro in that sheet with the test code.

He stated he received the error when he clicked in the sheet.

If the test sheet is the active sheet when the workbook is opened and you trigger the selection change event you are going to get the error because Add1 is an empty string.

[PStateline]

ALL WRONG!

MAKE YOUR FORMULA CUSTOM FUNCTION IN EXCEL.

FOR THE BASIC EXAMPLE BELOW THE USER WILL ONLY SEE "PERIM_SQFT(THKNESS, ITEM, QTY, LNFT)"

THE FORMULA WORKS IN VBA BACKGROUND.

THEN LOCK DOWN VBA WITH A PASSWORD.

--------------------------------------------------------------------------------------------

Function PERIM_SQFT(THKNESS, ITEM, QTY, LNFT)

Dim D_AISC_CHART As Range
Set D_AISC_CHART = Worksheets("ITEMS DATABASE").Range("B4:AE4000")

If THKNESS > 0 Then
SQFT = LNFT * 2 * QTY
Else: SQFT = QTY * LNFT * Application.WorksheetFunction.VLookup(ITEM, D_AISC_CHART, 3, 0)
End If

If QTY = 0 Then
PERIM_SQFT = 0
Else: PERIM_SQFT = SQFT
End If

End Function