asked on

VB help

Please can you help me add a password for the macro to execute, if "abc" is entered then the code will function, if not a msgbox will state "wrong password" and exit the macro.

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
Dim i As Long
Dim j As Long
Dim row As Range

If InputBox("Please enter the password", "Enter Password") <> Worksheets("Datasheet").Range("B4") Then
MsgBox "incorrect Password."

Exit Sub
End If

j = Sheets("Archive").Range("B" & ActiveSheet.Rows.Count).End(xlUp).row + 1
For i = 9 To ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row

Set row = ActiveSheet.Range("A" & i)

If row.Cells(1, 14).Value <> "" Then

Sheets("Archive").Unprotect Sheets("DataSheet").Range("B5").Value

Sheets("Archive").Range("B" & j & ":s" & j).Value = ActiveSheet.Range("B" & i & ":S" & i).Value

j = j + 1

Sheets("Archive").Protect Sheets("DataSheet").Range("B5").Value

End If

Next i

'Delete the archived rows

For i = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row To 9 Step -1

Set row = ActiveSheet.Range("A" & i)

If row.Cells(1, 14).Value <> "" Then

Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B4").Value

row.Rows(1).EntireRow.Delete

Sheets("Low Volume").Protect Sheets("DataSheet").Range("B4").Value
Application.ScreenUpdating = True

End If

Next i

End Sub

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kiwi-123

ASKER

Thanks Nutsch it works well; just asking your advice really, is it a good idea to use the Application.ScreenUpdating? I figured the code would be more robust if I did.

nutsch

It doesn't make the code more robust BUT it makes it incredibly faster, and easier on the eyes.

So yes, most often a good idea.

Thomas