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

skip cells contains formulas when DCLICK without protection

0

Hi  guys,

I  use  code  when double click the  cell will insert  new  row  with  the  same  formatting & formulas , but  I  want  preventing user to double click on any  cell contains  formuals  without  using  protection for  some  cells .

in  my  case  I   don't  want  the  user double click  in any  cells in columns A,E  contains formulas. I  no  know  if  could  be  by  vba 

this  is  the  code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
      Application.CutCopyMode = False
      On Error GoTo 0
   End With
End Sub
excel
MS Office Version: 2010
posted by: speed (rep: 40) on Sun Jun 4, 2023 at 3:35 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Speed

Try the changes in bold below (setting the Cancel means they won't enter the cell as usual if the cell contains a formula in A or E)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' stop users double-clicking formula in A or E
With Target
    If .HasFormula = True And (.Column = 1 Or .Column = 5) Then
        Cancel = True
        Exit Sub
    End If
End With

With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
      Application.CutCopyMode = False
      On Error GoTo 0
   End With
End Sub
posted by: John_Ru (rep: 6142) on Mon Jun 5, 2023 at 8:04 am
Discuss

Discussion

Hi John,
this is  exactly  what  I want  it . 
many  thanks .
speed (rep: 40) Jun 5, '23 at 1:31 pm
Great. Thanks for selecting my Answer, Speed. 
John_Ru (rep: 6142) Jun 5, '23 at 1:47 pm
Add to Discussion
0

Hello speed and welcome.

This is an easy fix. At the top of the macro add these two lines:

If Target.Column = 1 Then Exit Sub   ' column "A"
If Target.Column = 5 Then Exit Sub   ' column "E"
' or use the following
'If Target.Column = 1 Or Target.Column = 5 Then Exit Sub

There is one other tweak needed. With your code, the "Sum" formula does not update to include the added row. To ensure the formula updates add the following after the line End With:

Dim LRcolE As Long   ' the last used row in Column "E" (the one with the "SUM" formula)

LRcolE = Range("E" & Rows.Count).End(xlUp).Row
Range("E" & LRcolE).Formula = "=Sum(E8:E" & LRcolE - 1 & ")"

Cheers   :-)

posted by: WillieD24 (rep: 557) on Sun Jun 4, 2023 at 6:00 pm
Discuss

Discussion

Hi Willie
what  I  meant the  user  can't double click  in cells contain formulas at  all .
your  way still  enables the  user double click in cells contain formulas .
speed (rep: 40) Jun 5, '23 at 6:06 am
Hi speed,
I agree John's solution is a bit better than mine. However, I still see that the "SUM" formula in column "E" of the "Total" row does not update to include the newly added row. (Excel 2007, 2016). Therefore I recommend adding the code I suggested at the end. Here it is again:
' ensure SUM formula in column E of Total row is updated to include newly added row
Dim LRcolE As Long   ' the last used row in Column "E" (the one with the "SUM" formula)
 
LRcolE = Range("E" & Rows.Count).End(xlUp).Row
Range("E" & LRcolE).Formula = "=Sum(E8:E" & LRcolE - 1 & ")"

Cheers   :-)
WillieD24 (rep: 557) Jun 5, '23 at 4:40 pm
Add to Discussion


Answer the Question

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