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
Kiwi-123Asked:
Who is Participating?
 
nutschCommented:
Try this:

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") <>"abc" Then
MsgBox "wrong 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 

Open in new window

0
 
Kiwi-123Author Commented:
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.
0
 
nutschCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.