Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Hide Formulas in a Worksheet and Prevent Deletion on "merged cells"

0

Hi! 

The follwing Tutorial was postad a few wiks ago to Hide Formulas in a Worksheet and Prevent Deletion,

https://www.teachexcel.com/free-excel-macros/m-36,hide-formulas-prevent-deletion.html?nav=email

#1 I have in my sheet merged cells that contains formulas and when I select this cell the excel sheet start to freeze, is there any way this function should work also of the merged cells?

#2 I have some formulas what I don't want to lock and hide, so I try to add this line of  code 

If Target.formula = "=IF(C28="","",4)" Then Exit Sub
but it doesn't work, can anyone help me with this 2 questions?

thank you

Answer
Discuss

Answers

0
Selected Answer

It seems to me that the code you are using represents a bit of an over-kill in that it treats all sheets in the workbook equally. Please try the code below. Install it in the code sheet of the worksheet on which you want the action. I think it will have the same functionality you already know. If not, tell me where I'm wrong. It will not balk at merged cells/

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 27 Oct 2017
    
    Const Pw As String = "password"
    Dim CellsWithFormula As Range
    Dim n As Long
    
    With Target
        On Error Resume Next
        n = .Cells.Count
        ' if the entire sheet is selected,
        ' only the first cell's merge area will be processed
        If Err Then Set Target = .Cells(1).MergeArea
        On Error GoTo 0
    End With
    With Target
        Set CellsWithFormula = .SpecialCells(xlCellTypeFormulas)
        If Not CellsWithFormula Is Nothing Then
            Unprotect Password:=Pw
            With CellsWithFormula
               .Locked = True
               .FormulaHidden = True
            End With
            Protect Password:=Pw, UserInterFaceOnly:=True
        End If
    End With
End Sub

As for the cell you wish to excclude, the description of it you give is insufficient. The best way to identify it would be by its location. If it's a range, then the range's location. If it must be identified by the formula it holds, the exact formula is needed, as it appears in the cell, including $ signs.

Discuss

Discussion

Hi Variatus 
Thank you, it works good for merged cells, but 

#1 it takes about 2 seconds for every selection change, and the other code I have takes only a half of a second.

#2 your code locks me also cells that contains data validation drop down list and I can't select nothing from the list.
JAExcel (rep: 10) Oct 30, '17 at 10:10 am
Interesting!
#1 Two seconds point to a loop running a few tenthousand times. Place a Debug.Print "Here" at the top of the code to see if it is called repetitively. If yes, Set Application.EnableEvents = False at before Set CellsWithFormula and True just before End With. However, I suspect that you still have the other code running as well. It would run if it's not disabled, and the two might play ping-pong with erach other. You can use the Debug.Print "Here" test for that, too. The point to proceed from is that it isn't the code that takes so long, it's some code which runs unexpectedly, and more than once.
#2 My code only touches cells with formulas in them. Therefore cells with data validation shouldn't be locked by my code. Unlock them manually to see if my code really locks them up (they might just still be locked from earlier trials). If, for some reason, perhaps known to Mr Gates, cells with data validation are included with those that have formulas I would amend my code to pointedly exclude them. However, this action shouldn't be taken before you are absolutely sure of your case.
Variatus (rep: 4889) Oct 30, '17 at 11:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login