Stopping VBA macro from running after entering password

[jwatson34]

Hi all-

I'm completely new to VBA, so please forgive my ignorance. I am trying to have the code run when excel starts (to keep users from being able to cut and paste). If someone wants to be able to cut and paste, they will enter a password and the code will not run- giving the user the ability to cut an paste (i.e. stop the code from running). I don't know how to make this work. Thanks in advance.

Jonathan

Sub GetPassword()
Dim strPassword As String
strPassword = InputBox(Prompt:="Your password please:", _
Title:="ENTER YOUR PASSWORD", Default:="Your Password here")

If strPassword = "Password" Then ??????

If strPassword <> "Password" Or _
strPassword = vbNullString Then

End If

End Sub
Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
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 = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
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 = False
End Sub

 

[mole999]

maybe a less detailed way.

In cell A1 insert an X, and test for its presence, remove it when the workbook shuts down, if people are going to share passwords they will share the X, you can only do so much

 

[jwatson34]

Thank you for your reply. I'm afraid I wasn't clear about what I need.

The spreadsheet can be used by multiple people who do not have a password. As they don't have a password, the macro will automatically run and prevent them from being able to cut and paste in the spreadsheet (their doing so messes up the formulas and the "downstream" functions).

A few people, "administrators" if you will, will have the password which would allow them to cut and paste. By typing in the password, it would shut off, or circumvent the macro so that the macro doesn't run.

This way, the majority of people cannot cut and paste, but the few who have the password can.

Perhaps there is a simpler and more elegant solution, but I was unable to think of anything.

Thanks again for the help.

 

[mole999]

Maybe I wasn't clear, rather than trying to code a password solution, put a symbol in a cell (which the administrators know about), and check against the cell to validate the bypass. If the mark exists, the vba will allow actions, if not or wrong then the VBA exits sub

 

[jwatson34]

mole999-

Now I understand what you mean, but I'm so new to VBA I don't know how to code it. I imagine it would be an If Then statement such as

If A1 = "X" Then Exit (or End Sub)

But in my attempts, the VBA keeps running and users are still unable to cut and paste even when A1 = "X"

 

[mole999]

unless someone beats me to it, i'll work through later

 

[jwatson34]

Thanks for the help!

 

[mole999]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Range("A1") = "X" Then Exit Sub
'This looks for a case sensitive X in cell A1 on the live sheet, exit sub stops the follow on rules that would disable Cut / Copy

If UCase(ActiveSheet.Range("A1")) = "X" Then Exit Sub
'This allows for x or X to be inputted

'further code below

End Sub

 

[jwatson34]

mole999

I really appreciate the help! Unfortunately, even with I have an X in A1, save and close the document, and re-open it, it still won't allow me to cut and paste.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Range("A1") = "X" Then Exit Sub
'This looks for a case sensitive X in cell A1 on the live sheet, exit sub stops the follow on rules that would disable Cut / Copy


If UCase(ActiveSheet.Range("A1")) = "X" Then Exit Sub
'This allows for x or X to be inputted


End Sub


Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
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 = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
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 = False
End Sub

 

[mole999]

all the subs have to be considered, its not a global option, i was just showing a tiny part of it as concept