excel vba insert row and copy formulas with shortcuts

Kanita

New member
Joined
Sep 30, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
Excel for Mac
Hi there,

I have a file with protected worksheets. Some cells (with formulas) are protected, some are not. Because of protection of some cells my colleagues can not insert a row and copy the one above. That's why I want to use a macro. My solution was the following:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    ActiveSheet.Protect Password:="XXXX", UserInterfaceOnly:=True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

It works, but double click is something, my colleagues want to have for other reasons. I wanted to change it for a shortcut, like Ctrl+D. But because I am not a professional I can not do it myself. Could someone help me to fix the problem?

Thanks a lot!!!
 
Last edited by a moderator:
You could put the same coding into a macro in a standard module instead of using the worksheet double click event.
Use activecell instead of target and assign the Ctrl+D from the macro dialogue that appears when you hit F8.
 
You can do it yourself. Remove your code from the worksheet's codemodule and put this code into a standard codemodule:

Code:
Sub InsertRow()
   Selection.Cells(1, 1).EntireRow.Copy
    Selection.Cells(1, 1).Offset(1).EntireRow.Insert
    On Error Resume Next
    Selection.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

then, on the Developer tab of the Ribbon (you may need to right-click the ribbon and choose "Customize the Ribbon" to select the "Developer" tab) click the Macros button, then select the workbook and the sub InsertRow, and assign it a shortcut key using the Options... dialog as show here:
Capture.JPG

PS The Protect line in your original code only needs to be run once - after that, the sheet can be modified by VBA code without Unprotect/Protect
 
Last edited:
Thank you soooooo much! Ups, I did it! :)
 
Back
Top