Write-protect cell with password VBA

[KlausW]

Hi
I use this code to protect cell A8 with a password, but it does not work, the vba code must start when the file is opened.
It is located in a VBA module.
Someone who can help.
All help will be appreciated.
Best regards Klaus W

Private Sub Worksheet_Open(ByVal Target As Range)

Dim SAdress As String

Dim vOLD As Variant

Dim aNEW As Variant

Dim vNEW As Variant

Dim myValue As Variant

SAdress = Target.Address(False, False)
If SAdress = "a8" Then
vNEW = Target.Value
aNEW = Target.Address
Application.EnableEvents = False
Application.Undo
vOLD = Target.Value
Target.Value = vNEW
If Not vOLD = vNEW Then
myValue = InputBox("Enter the code")
If myValue = 12345 Then
Application.ActiveSheet.Name = VBA.Left(Target, 31)

Else:

MsgBox " The entered code is wrong "
Ark1.Range(Target.Address).Value = vOLD

Exit Sub

End If

If Not myValue <> "" Then

Ark1.Range(Target.Address).Value = vOLD

End If

End If

Application.EnableEvents = True

End If

End Sub

 

[johnnyL]

Try Workbook_Open, not worksheet_Open

 

[mikerickson]

The Workbook_Open event doesn't have a Target argument.

What do you mean by protecting cell A8? Typically worksheets are protected not cells.
One quick and dirty way to protect only Cell A8 would be

With ActiveSheet
    .Unprotect
    .Cells.Locked = False
    .Range("A8").Locked = True
    .Protect
End With

 

[KlausW]

Try Workbook_Open, not worksheet_Open

It does not work, please. KW

 

[KlausW]

The Workbook_Open event doesn't have a Target argument.

What do you mean by protecting cell A8? Typically worksheets are protected not cells.
One quick and dirty way to protect only Cell A8 would be

With ActiveSheet
    .Unprotect
    .Cells.Locked = False
    .Range("A8").Locked = True
    .Protect
End With

Thanks, do I have to put the code into a module? KW

 

[KlausW]

Thanks, do I have to put the code into a module? KW

I would like that if you try to type something in cell a8, there should be a box with the text “enter password”, if you type the correct password you can type in the cell, if not you should be asked to type password again.

KW

 

[mikerickson]

You could put this in the code module for the sheet in question

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("A8")) Is Nothing Then
        If Not IsPassword Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            Beep
        End If
    End If
End Sub

Private Function IsPassword() As Boolean
    Const myPassword As String = "password"
    IsPassword = (Application.InputBox("enter password", Default:=myPassword, Type:=2) = myPassword)
    If Not IsPassword Then
        IsPassword = (Application.InputBox("Try again", Default:=myPassword, Type:=2) = myPassword)
    End If
End Function

 

[Marc L]

I would like that if you try to type something in cell a8, there should be a box with the text “enter password”, if you type the correct password you can type in the cell, if not you should be asked to type password again.

Hi, so according to the previous just paste this worksheet event code to the worksheet module :​

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Static Passed As Boolean
    Dim P$
        If Target.Address <> "$A$8" Or Passed Then Exit Sub
    Do
        P = InputBox(vbLf & vbLf & " Enter password to modify this cell :", "Limited cell access")
        If P = "" Then Target(1, 2).Select: Exit Sub
    Loop Until P = "Password"
        Passed = True
End Sub

 

[KlausW]

Thanks both, I simply do not know what I am doing wrong I put the VBA code into a module. Saves the file with marco, opens the file again. But I can still write in cell A8 in. Could it be because there are two sheets, cell A8 is in the "Prisliste" sheet. KW

 

[Marc L]

​

Klaus, as we both wrote the code must not be « into a module » but within the Prisliste worksheet module …​