How to enable copy paste function using password in excel 365

Occasional Visitor

Good day everyone.

 

I am new in this field so please be patient with me.

 

So I was working on a Spreadsheet. This spreadsheet has two set of passwords. The first password is when you open the spreadsheet, the second password is triggered in the Master Sheet if the Admin is trying to enter a new data in the spreadsheet. The first sheet is where the common user allowed to enter a date only (Please check the picture below for your reference), the other parts of the sheet is locked. On the second sheet is the Master Sheet, this sheet is where all the information of the Spreadsheet is located. When a data is entered in the Master Sheet the data linked in the 1st sheet for the other user to view but cannot be edited. The Master Sheet is locked upon opening and can be unlocked by a password where only the admin has the knowledge of.

 

1st Sheet

Kenji0589_0-1651800466920.png

 

Master Sheet

Kenji0589_1-1651800519452.png

 

This spreadsheet is using this code disabling the copy/paste function:

 

Private Sub Workbook_Activate()
Application.CutCopyMode = True
Application.OnKey "^c", ""
Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = True
Application.OnKey "^c", ""
Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = True
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = True
Application.CutCopyMode = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

 

So my question is this. How can I enable the Copy/Paste Function if the Admin has entered the password in the Master Sheet and disable again the Copy/Paste Function once the Spreadsheet is closed.

 

And can I add also, in the 1st sheet, all the rows and columns are locked and only the Calibration date is unlocked because this is the only cell where the user is allowed to enter a data. this cell has a data validation where only a date and N/A is allowed to enter but if i try to enter a bunch of number this is the error I get. What i want is when a users entered a bunch of numbers an error will pop out and inform the user to enter a date or N/A only.

Kenji0589_2-1651801805823.png

 

Thank you to anyone who will answer my question.

 

0 Replies