[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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
0
Kiwi-123
Asked:
Kiwi-123
  • 2
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now