Hiding Formula In Cell

[Front]

I am having issue with my table expanding when I protect my sheet. So as a work around I am using VBA to unprotect and then protect when a new entry is added. This works, but it breaks other functions on the table. So I am wondering if there is a way to have the sheet unprotected normally, then if someone tries to click on a cell that has a formula in it, the VBA would automatically protect the sheet. Then if another cell is clicked on it would unprotect it. Is something like that possible? That way I would not lose the functions I need.

 

[offthelip]

You could achieve the same thing by using the code I posted in this thread:
not sure what they call this
the code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Intersect(Target, Range("B8:AC43")) Is Nothing) Then
   Range("b8").Select
End If
End Sub

If you put this code in the worksheet code and then try selecting a cell outside the range B8 to AC43 if changes your selection . If you invert the logic and tailor the range to match your formulae , job done
You don't need to protect the sheet at all using this method. If your formula are just in a column you can just change the column selected but still on the same row

 

[Front]

That's not bad, but I can still see the formulas in the cell if I hold the curser down on the cell until it jumps to the new cell.

 

[johnnyL]

I am having issue with my table expanding when I protect my sheet. So as a work around I am using VBA to unprotect and then protect when a new entry is added. This works, but it breaks other functions on the table. So I am wondering if there is a way to have the sheet unprotected normally, then if someone tries to click on a cell that has a formula in it, the VBA would automatically protect the sheet. Then if another cell is clicked on it would unprotect it. Is something like that possible? That way I would not lose the functions I need.

@Front, Are you wanting to have cells with formulas un editable, as well as not see the formulas in those cells with formulas, but be able to make changes to any other cells in the sheet?

 

[Front]

I just don't want the users to be able to see the formulas, but I do want them to be able to enter data into the cells that do not have formulas. It seems so simple, but after working on this for a bit I am thinking it is impossible.

 

[johnnyL]

That is what I was asking.

Sub HideFormulas()
'
'   Disable locked protection for all cells on a sheet that is not password protected.
    Cells.Select                                                                        ' Select all cells on the sheet
    Selection.Locked = False                                                            ' uncheck the 'Locked' box
    Selection.FormulaHidden = False                                                     ' uncheck the 'Hidden' box
'
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select                               ' Select all cells on sheet with any type of formulas
    Selection.Locked = True                                                             ' Lock these cells with formulas
    Selection.FormulaHidden = True                                                      ' Hide the formulas from these cells
'
'   Set password for sheet, finalize protection ... change the sheet password below from MrExcel to a password of your choosing. ;)
    ActiveSheet.Protect Password:="MrExcel", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Front]

Thank you, but that still breaks the table from expanding when I try to enter a new row. Unless I am doing something wrong. I added the code on the tab by selecting view code and then protected the sheet.

 

[johnnyL]

Do not add it to a sheet code. It is to be entered into a separate module and ran separately.

When you have your sheet unprotected, ie not password protected, run the code. Set the password that you want to use for the sheet at the bottom of the code I provided. The password in the code I provided is currently set to MrExcel. Change that, in the last line of code, to what you want it to be.

 

[Front]

Okay, thank you for that. I got the code to run and it does protect the formulas but it ends up breaking the table, so it does not expand with each new line, which is the issue I have when I use normal protection. I tried a lot of things and nothing seems to work. Pretty close to giving up. I think if there was a way to have the sheet operate without protection and then add it only when the user tries to click a cell with formulas then removes it when they click away that might work, otherwise the protection just breaks other elements of the sheet.

 

[offthelip]

one way round you problem could be to put the equations into columns which are hidden and unselectable using the code I posted and then displaying the results of the equation in another column with just "= H6" (for example assuming the hidden column is H) in the equation. If you make this column unselectable as well then I think that might do what you want. Another possiblity is to put the equations on a very hidden sheet and refer to them on your main sheet