Kiwi-123
asked on
VB Help
Please can you help me alter the attached file. I want to change the command button code so that it requires a password. The password would be held in sheet4 "datasheet" B3
Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
Dim i As Long
Dim j As Long
Dim row As Range
j = Sheets("Archive").Range("B " & ActiveSheet.Rows.Count).En d(xlUp).ro w + 1
For i = 9 To ActiveSheet.Range("B" & ActiveSheet.Rows.Count).En d(xlUp).ro w
Set row = ActiveSheet.Range("A" & i)
If row.Cells(1, 14).Value <> "" Then
Sheets("Archive").Unprotec t Sheets("DataSheet").Range( "C4").Valu e
Sheets("Archive").Range("B " & j & ":S" & j).Value = ActiveSheet.Range("B" & i & ":S" & i).Value
j = j + 1
Sheets("Archive").Protect Sheets("DataSheet").Range( "C4").Valu e
End If
Next i
' Delete the archived rows
For i = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).En d(xlUp).ro w To 9 Step -1
Set row = ActiveSheet.Range("A" & i)
If row.Cells(1, 14).Value <> "" Then
Sheets("Low Volume").Unprotect Sheets("DataSheet").Range( "C5").Valu e
row.Rows(1).EntireRow.Dele te
Sheets("Low Volume").Protect Sheets("DataSheet").Range( "C5").Valu e
Application.ScreenUpdating = True
End If
Next i
End Sub
Thanks for your help
help-12-09-2012.xls
Private Sub CommandButton3_Click()
Application.ScreenUpdating
Dim i As Long
Dim j As Long
Dim row As Range
j = Sheets("Archive").Range("B
For i = 9 To ActiveSheet.Range("B" & ActiveSheet.Rows.Count).En
Set row = ActiveSheet.Range("A" & i)
If row.Cells(1, 14).Value <> "" Then
Sheets("Archive").Unprotec
Sheets("Archive").Range("B
j = j + 1
Sheets("Archive").Protect Sheets("DataSheet").Range(
End If
Next i
' Delete the archived rows
For i = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).En
Set row = ActiveSheet.Range("A" & i)
If row.Cells(1, 14).Value <> "" Then
Sheets("Low Volume").Unprotect Sheets("DataSheet").Range(
row.Rows(1).EntireRow.Dele
Sheets("Low Volume").Protect Sheets("DataSheet").Range(
Application.ScreenUpdating
End If
Next i
End Sub
Thanks for your help
help-12-09-2012.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.