problem in code

[Prasad]

Hi,

I am trying to hide/unhide some sheets in a wb through following vba, but problem is appearing when running the hide macro. On debubing, it highlights the " SelectedSheets.Visible = False" line. Pl help me to make this code running.

Code: Select all

Option Explicit
Sub hide()
Dim strName As Variant
     For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
        "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
        "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
        "MSIL PLAN", "HMSI", "H2 PLAN-10-11").Select
     SelectedSheets.Visible = False
     ActiveWorkbook.Protect Structure:=True, Windows:=False
     .Protect Password:="123"
   End With
 Next
 Sheets("Comparative").Select
  Range("B4").Select
End Sub

Code: Select all

Option Explicit
Sub unhide()
 Dim strPwd As String
 Dim strName As Variant
  ' Ask for password
  strPwd = InputBox("Please enter the password.")
  If Not strPwd = "123" Then
    ' Give user another chance
    strPwd = InputBox("Password incorrect. Please try again.")
    If Not strPwd = "123" Then
      ' Twice wrong - get out
      MsgBox "Sorry! Better luck next time.", vbExclamation
      Exit Sub
    End If
  End If
        ActiveWorkbook.Protect Structure:=False, Windows:=False
        For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
        "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
        "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
        "MSIL PLAN", "HMSI", "H2 PLAN-10-11")
        With Worksheets(strName)
        .SelectedSheets.Visible = True
    Sheets("Summary").Activate
  End With
  Next strName
  ' Final actions
  Sheets("Comparative").Select
  Range("B4").Select
End Sub

[Prasad]

forgot to mention, I am using Excel 2003.

[StuartR]

This section of code seems a bit strange to me

Code: Select all

 With Worksheets(strName)
        .SelectedSheets.Visible = True
    Sheets("Summary").Activate
  End With

There is no need to use With and End With when you only have one line that starts with a .
WorkSheets(strName) does not have a .SelectedSheets attribute, SelectedSheets is an attribute of the Workbook, not of a single worksheet.

Since strName is the name of a single sheet you can replace all of this with

Code: Select all

    WorkSheets(strName).Visible = True
    Sheets("Summary").Activate

[Don Wells]

forgot to mention, I am using Excel 2003.

The following code has been modified to hide the sheets for you.

Code: Select all

    Option Explicit
    Sub hide()
    Dim strName As Variant
         For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
            "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
            "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
            "MSIL PLAN", "HMSI", "H2 PLAN-10-11") 
         Sheets(strName).Visible = False
         ActiveWorkbook.Protect Structure:=True, Windows:=False
         .Protect Password:="123"
       End With
    Next
    Sheets("Comparative").Select
      Range("B4").Select
    End Sub

H.T.H.

[Prasad]

Thanks StuartR, Thanks Don,

Now it shows compilation error in .Protect Password:="123" with invalid or unqualified reference.

forgot to mention, I am using Excel 2003.

The following code has been modified to hide the sheets for you.

Code: Select all

    Option Explicit
    Sub hide()
    Dim strName As Variant
         For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
            "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
            "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
            "MSIL PLAN", "HMSI", "H2 PLAN-10-11") 
         Sheets(strName).Visible = False
         ActiveWorkbook.Protect Structure:=True, Windows:=False
         .Protect Password:="123"
       End With
    Next
    Sheets("Comparative").Select
      Range("B4").Select
    End Sub

H.T.H.

[Leif]

I'm far from an expert, but don't you need a 'With' before the 'End With' ?

[Don Wells]

Thanks StuartR, Thanks Don,

Now it shows compilation error in .Protect Password:="123" with invalid or unqualified reference.

The following code should do what you want. Note that the protection command has been moved out of the For - Next loop as you are protecting the workbook, not individual sheets.

Code: Select all

Option Explicit
Sub hide()
Dim strName As Variant

  For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
  "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
  "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
  "MSIL PLAN", "HMSI", "H2 PLAN-10-11")
    Sheets(strName).Visible = False
  Next
  ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=False
  Sheets("Comparative").Select
    Range("B4").Select
End Sub

[Prasad]

Perfect. Exactly as it was needed.

Thank you Don, Thanks StaurtR, Thanks Leif.

Thanks StuartR, Thanks Don,

Now it shows compilation error in .Protect Password:="123" with invalid or unqualified reference.

The following code should do what you want. Note that the protection command has been moved out of the For - Next loop as you are protecting the workbook, not individual sheets.

Code: Select all

Option Explicit
Sub hide()
Dim strName As Variant

  For Each strName In Array("Summary", "1011", "Apr 1011", "May 1011 ", "Jun 1011", "Jul 1011", _
  "Aug 1011", "Sep 1011", "Oct (R) 1011", "Nov (R) 1011", "Dec (R) 1011", _
  "Jan (R) 1011 ", "Feb (R) 1011", "Mar (R) 1011", "No.summ", "sp Sep 10", "sp jan10", _
  "MSIL PLAN", "HMSI", "H2 PLAN-10-11")
    Sheets(strName).Visible = False
  Next
  ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=False
  Sheets("Comparative").Select
    Range("B4").Select
End Sub