PDA

View Full Version : visible sheets count



lior03
01-15-2008, 12:26 AM
hello
i wanted to let the user know how many sheets in the activeworkbook are hidden.if all are uhidden then a msgbox appear and the sub ends if not then thrpough a password the user cna unhide all sheets.

Application.ScreenUpdating = False
Dim password As String
Dim tryagain As Boolean
Dim wssheet As Worksheet
Dim y As Integer
y = ActiveWorkbook.Sheets.Count
For Each wssheet In ActiveWorkbook.Worksheets
If wssheet.Visible = xlSheetVisible Then
MsgBox "all - " & y & "sheets visible", vbInformation, "visible?"
End If
Next wssheet
If y = 1 Then GoTo err
tryagain = True
On Error GoTo err
Do While tryagain = True
password = InputBox("enter password to unhide :", "this macro require a password", Format(Now, "dddd dd/mm/yyyy"))
If password = "1234" Then
wssheet.Visible = xlSheetVisible
tryagain = False
Else
If MsgBox("no way.another try?", vbOKCancel + vbInformation, "an error!!!") = vbCancel Then tryagain = False
End If
Loop
Exit Sub
err:
MsgBox "activeworkbook contains only one sheet.", vbInformation
Application.ScreenUpdating = True

Bob Phillips
01-15-2008, 01:30 AM
Application.ScreenUpdating = False
Dim password As String
Dim tryAgain As Boolean
Dim wsSheet As Worksheet
Dim y As Long, x As Long
y = ActiveWorkbook.Sheets.Count
For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Visible = xlSheetVisible Then
x = x + 1
End If
Next wsSheet
If y = 1 Then GoTo err
If y = x Then
MsgBox "all - " & y & " sheets visible", vbInformation, "visible?"
GoTo sub_exit
End If
tryAgain = True
On Error GoTo err
Do While tryAgain = True
password = InputBox("enter password to unhide :", "this macro require a password", Format(Now, "dddd dd/mm/yyyy"))
If password = "1234" Then
wsSheet.Visible = xlSheetVisible
tryAgain = False
Else
If MsgBox("no way.another try?", vbOKCancel + vbInformation, "an error!!!") = vbCancel Then tryAgain = False
End If
Loop
Exit Sub
err:
MsgBox "activeworkbook contains only one sheet.", vbInformation
sub_exit:
Application.ScreenUpdating = True