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
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
not possible; however, you could change the cell(s) format to value
If your original question was resolved, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Click on the Add Reputation button (located at the lower-left corner of all post) for those who assisted you in solving your issue.
Hi Syrkrasi,
Thanks, ok. is there any vba code to protect and hide formulas in the formula bar?
Kalai
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.
Public Formula1 As String Public Add1 As String Private Sub Worksheet_Activate() Application.EnableEvents = False Range("A1").Select Formula1 = Range("A1").Formula Range("A1").Value = Range("A1").Value Add1 = "A1" Application.EnableEvents = True End Sub Private Sub Worksheet_Deactivate() Range(Add1).Formula = Formula1 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Range(Add1).Formula = Formula1 Add1 = ActiveCell.Address Formula1 = ActiveCell.Formula ActiveCell.Value = ActiveCell.Value End Sub
I suppose you could amend it a bit.
Public Formula1 As String Public Add1 As String Private Sub Worksheet_Activate() Application.EnableEvents = False Range("A1").Select Formula1 = Range("A1").Formula Range("A1").Value = Range("A1").Value Add1 = "A1" Application.EnableEvents = True End Sub Private Sub Worksheet_Deactivate() Range(Add1).Formula = Formula1 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '*********************************************** If Target.Cells.Count > 1 or Add1 ="" Then Exit Sub '*********************************************** Range(Add1).Formula = Formula1 Add1 = ActiveCell.Address Formula1 = ActiveCell.Formula ActiveCell.Value = ActiveCell.Value End Sub
Last edited by mehmetcik; 12-12-2017 at 05:10 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
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
The Macro is in the sheet named Test.
You cannot open the macro in that sheet with the test code.
Public Formula1 As String Public Add1 As String Private Sub Worksheet_Activate() Application.EnableEvents = False Range("A1").Select Formula1 = Range("A1").Formula Range("A1").Value = Range("A1").Value Add1 = "A1" Application.EnableEvents = True End Sub Private Sub Worksheet_Deactivate() Range(Add1).Formula = Formula1 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '*********************************************** If Target.Cells.Count > 1 or Add1 ="" Then Exit Sub '*********************************************** Range(Add1).Formula = Formula1 Add1 = ActiveCell.Address Formula1 = ActiveCell.Formula ActiveCell.Value = ActiveCell.Value End Sub
Last edited by mehmetcik; 12-12-2017 at 05:11 PM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
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
Last edited by PStateline; 12-19-2019 at 12:24 PM.
There are currently 2 users browsing this thread. (0 members and 2 guests)
Bookmarks