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

Code shaking when selecting a cell for a long time



I  face  problem  when select  cells into  columns C OR  D   and  will  shake  for  long  time  it  seems  to    loop throught  all  of  the  cells  are  empty  .it  can  be  problem. 

the  code should  hide  formula  in  column E   when  select  the  cell .

Option Explicit
Dim zDic       As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Exit Sub
    Dim zCell  As Range
    Dim zRg    As Range
    Dim lr  As Long
    Set zRg = Range("E2:E500")
    If Target.CountLarge > 1 Then Exit Sub
    If zDic.Count <> zRg.Count Then
        For Each zCell In zRg
            zDic.Add zCell.Address, zCell.FormulaLocal
        If Intersect(Target, zRg) Is Nothing Then
            For Each zCell In zRg
                zCell.FormulaLocal = zDic.Item(zCell.Address)
            If Target.HasFormula Then
                With Target
                    .Value = .Value
                End With
            End If
        End If
    End If

End Sub

any  chance  to  fix  this problem please?




In my rush to answer you, I misunderstood the purpose of your macro. I now see that you copied the code from another site (and modified it) so I have deleted my incorrect answer.

I don't know what you mean by the code "will  shake  for  long  time" and suspect others will not either. Certainly when I try the original code, there is no delay or screen "shake" if I change values in C:D but if I pick a new cell in E, I see the formula in the formula bar (briefly but not afterwards) before I see just the value.

I leave it to others to answer this (but would suggest you instead use VBA to implement the hidden cell in a protected sheet).

John_Ru (rep: 3677) Jul 21, '22 at 12:55 pm
Add to Discussion

Answer the Question

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