Hiding rows in a protected worksheet..

[cbs81]

Hello, I have a fully protected sheet and each cell is locked, so the user cannot click on any cell to view contents... I have a button which is unlocked and assigned a macro to that button which hides certain rows in my worksheet... i have the following code, but it doesnt seem to work.. can somebody shed some light on my code.. thankyou:



Sub HURows()
BeginRow = 71
EndRow = 320
ChkCol = 5
Worksheets("MySheet").Unprotect Password:="drowssap"
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Worksheets("MySheet").Protect Password:="drowssap"
End Sub

[johnske]

Always use Option Explicit and explicitly declare all variables and constants. I don't know why you're using the constants BeginRow, EndRow, and ChkCol when you can write it as...

Option Explicit
'
Sub HURows()
'
Dim RowCnt As Long
'
Worksheets("MySheet").Unprotect Password:="drowssap"
'
For RowCnt = 71 To 320
If Cells(RowCnt, 5).Value = 0 Then
Cells(RowCnt, 5).EntireRow.Hidden = True
Else
Cells(RowCnt, 5).EntireRow.Hidden = False
End If
Next RowCnt
'
Worksheets("MySheet").Protect Password:="drowssap"
'
End Sub
If you really want to define them as constants, use...

Option Explicit
'
Sub HURows()
'
Dim RowCnt As Long
'
Const BeginRow As Long = 71
Const Endrow As Long = 320
Const ChkCol As Long = 5
'
Worksheets("MySheet").Unprotect Password:="drowssap"
'
For RowCnt = BeginRow To Endrow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
'
Worksheets("MySheet").Protect Password:="drowssap"
'
End Sub
Lastly, you should make explicit reference to the sheet, and you can also use Rows rather than Cells such as...

Option Explicit
'
Sub HURows()
'
Dim RowCnt As Long
'
Const BeginRow As Long = 71
Const Endrow As Long = 320
Const ChkCol As Long = 5
'
With Worksheets("MySheet")
.Unprotect Password:="drowssap"
'
For RowCnt = BeginRow To Endrow
If .Cells(RowCnt, ChkCol).Value = 0 Then
.Rows(RowCnt).Hidden = True
Else
.Rows(RowCnt).Hidden = False
End If
Next RowCnt
'
.Protect Password:="drowssap"
End With
'
End Sub
All of these work if MySheet is the ActiveSheet, but only the last one is guaranteed to work if it's not :)

[cbs81]

When i copy and paste this in my script, i get the error msg:

subscript out of range

how do i fix this error?

thankyou

[johnske]

You're using Worksheets("MySheet") as the sheet you're trying to reference. If Worksheets("MySheet") doesn't exist you'll get that error. The sheet must be spelt exactly the same, no leading or trailing spaces etc.