Excel 2002 problem

ExHell

Board Regular
Joined
Aug 4, 2003
Messages
55
i have been using this spreadsheet for years,since upgrading to 2002
when i activate this sheet I get a password prompt.
Can you please help?
Private Sub worksheet_activate()
ActiveSheet.Protect UserInterfaceOnly:=True
Application.ScreenUpdating = False
BEL1
Application.StatusBar = ""

ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
If range("m24") > 1 Then
MsgBox "You have got more then one system checked you should have only a 1 in one box "
End If
If range("n4") > "" Then
ActiveSheet.Unprotect password:="rp"
range("L4:N4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
range("b200").Select

ActiveSheet.Protect password:="rp"
End If
If range("n4") = 0 Then
ActiveSheet.Unprotect password:="rp"
range("L4:N4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
ActiveSheet.Protect password:="bhprp"
range("b200").Select
End If
ActiveSheet.Protect password:="rp"
Application.StatusBar = ""
range("AW") = 0

ActiveSheet.Protect UserInterfaceOnly:=False
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello,
fine you are ex-hell ! :)
I wouldn't like to be in there :(
reducing your code to what's important to your problem
Code:
Private Sub worksheet_activate()
ActiveSheet.Protect UserInterfaceOnly:=True

If Range("n4") > "" Then
ActiveSheet.Unprotect Password:="rp"

ActiveSheet.Protect Password:="rp"
End If
If Range("n4") = 0 Then
ActiveSheet.Unprotect Password:="rp"

ActiveSheet.Protect Password:="bhprp"

End If
ActiveSheet.Protect Password:="rp"

ActiveSheet.Protect UserInterfaceOnly:=False
End Sub
just unprotect your sheet at the start of your macro and protect at the end
using the SAME password (only one key to heaven :) )

does this help ?
kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,639
Members
412,334
Latest member
ExcelForLifeDontHate
Top