Help Lock/Unlock Worksheet with other Worksheet as input

[damarwara sandiwan]

Dear all

I want to lock the worksheet (database) me so no one can edit the worksheet unless know the password.
I have tried to lock worksheet (database) I of the ribbon menu in Excel VBA but it makes me not working

for you know, I use another worksheet (receipts) as input to the worksheet (Database)

Also attached file that I mean

please help me to solve this problem. My confusion to finish it because I was a new and never before learning VBA


Thank you

Afan

 

[Chihiro]

Something like below. Change "Your Password" to actual password that you set.

Private Sub CommandButton1_Click()
'Pindah
Dim WSK As Worksheet
Dim WDB As Worksheet

Set WSK = Worksheets("Kuitansi")
Set WDB = Worksheets("Database")

If WorksheetFunction.CountIf(WDB.Columns(1), [L1].Value) = 1 Then
    MsgBox "Receipst Number already exist,Please again"
    Exit Sub
    Else
        WDB.Unprotect "Your Password"
        nextrow = WDB.Cells(Rows.Count, 1).End(xlUp).Row + 1
        WDB.Cells(nextrow, 1).Resize(1, 26).Value = Array([L1], [G7], [K7], [G8], [G9], [G10], [G11], _
        [H11], [D35], [J15], [J16], [J17], [J18], [J19], [J20], [J21], [J22], [J23], [J24], [J25], [J26], [J27], [J28], [J29], [E27], [E28])
'save
        WDB.Protect "Your Password"
        ActiveWorkbook.Save

'print preview
        Sheet1.PrintPreview
    End If

   
End Sub

 

[damarwara sandiwan]

Thanks Chihiro..

i try input like you said

Private Sub CommandButton1_Click()
'Pindah
Dim WSK As Worksheet
Dim WDB As Worksheet

Set WSK = Worksheets("Kuitansi")
Set WDB = Worksheets("Database")

If WorksheetFunction.CountIf(WDB.Columns(1), [L1].Value) = 1 Then
MsgBox "Receipst Number already exist,Please again"
Exit Sub
Else
WDB.Unprotect ([123])
nextrow = WDB.Cells(Rows.Count, 1).End(xlUp).Row + 1
WDB.Cells(nextrow, 1).Resize(1, 26).Value = Array([L1], [G7], [K7], [G8], [G9], [G10], [G11], _
[H11], [D35], [J15], [J16], [J17], [J18], [J19], [J20], [J21], [J22], [J23], [J24], [J25], [J26], [J27], [J28], [J29], [E27], [E28])
'save
WDB.Protect ([123])
ActiveWorkbook.Save

'print preview
Sheet1.PrintPreview
End If


End Sub

but
it's not working
there is show a warning box
"run time error '1004'
"The password not supplied is not correct. verify that the caps lock key is off and be sure to use the correction capitalization"

is there any mistake i did?

 

[NARAYANK991]

Hi ,

Chihiro has posted this line of code :

WDB.Unprotect "Your Password"

If your worksheet password is 123 , either of the following will work :

WDB.Unprotect "123"

WDB.Unprotect("123")

If your worksheet password is [123] , either of the following will work :

WDB.Unprotect "[123]"

WDB.Unprotect("[123]")

Narayan

 

[damarwara sandiwan]

Thank you Narayan
but i just do what you said
& still same

;-((

 

[Chihiro]

So what is the password for the workbook you uploaded? I left it up to you as I don't know it

 

[damarwara sandiwan]

Thank you... Guys...

it's working.....
aoooww... i'm happy for that...


the problem is simple.. i forgot the password....so it'x make the vba erorr said "Password not match"

Lucky me, i still have another master file So i can create new vba with code you gave...

Thank You again... it's Helpfull.