invalid use of property message

I am trying to cre4ate a macro that checks the value of a number of sheets to 
determine if they are already visible and if so it will not close them - 

     With Sheets("Pay Inflation - Biometrics")
         Sheets ("Statistics")
         Sheets ("Direct Cost Savings Breakdown")
         Sheets ("OT Reduction")
         Sheets ("Nurse OT Reduction")
         Sheets ("Premium Labor Utilization")
         Sheets ("Pay inflation - Timestamp")
         Sheets ("Calculation Error")
         Sheets ("Leave Inflation")
         Sheets ("Absenteeism")
         Sheets ("Walk Time Reductions")
         Sheets ("Paper Costs")
         Sheets ("Direct Cost Savings")
         Sheets ("Financial Analysis")
         Sheets ("Project Timeline Savings ")
         Sheets ("Total Cost of Ownership")
        If .Visible <> xlSheetVisible Then
            .Visible = Not .Visible
        End If
        End With
   
When I run the macro I get the "invalid use of property" message.. Can some 
one tell me what I am doing wrong ??


-- 
Thanks

Larry 
0
Utf
3/28/2010 5:30:01 PM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
1013 Views

Similar Articles

[PageSpeed] 23

This works for hidden sheets (not veryHidden)

Sub DelHidden()

Dim sh As Worksheet
For Each sh In Worksheets
    With Worksheets(sh.Name)
     If Not .Visible = xlSheetVisible Then
       Application.DisplayAlerts = False
       sh.Delete
       Application.DisplayAlerts = True
     End If
    End With
Next sh

End Sub


"Larry Fitch" <LarryFitch@discussions.microsoft.com> wrote in message
news:F3B285E7-9452-4BCD-A582-880A26F4DE8C@microsoft.com...
> I am trying to cre4ate a macro that checks the value of a number of sheets
to
> determine if they are already visible and if so it will not close them -
>
>      With Sheets("Pay Inflation - Biometrics")
>          Sheets ("Statistics")
>          Sheets ("Direct Cost Savings Breakdown")
>          Sheets ("OT Reduction")
>          Sheets ("Nurse OT Reduction")
>          Sheets ("Premium Labor Utilization")
>          Sheets ("Pay inflation - Timestamp")
>          Sheets ("Calculation Error")
>          Sheets ("Leave Inflation")
>          Sheets ("Absenteeism")
>          Sheets ("Walk Time Reductions")
>          Sheets ("Paper Costs")
>          Sheets ("Direct Cost Savings")
>          Sheets ("Financial Analysis")
>          Sheets ("Project Timeline Savings ")
>          Sheets ("Total Cost of Ownership")
>         If .Visible <> xlSheetVisible Then
>             .Visible = Not .Visible
>         End If
>         End With
>
> When I run the macro I get the "invalid use of property" message.. Can
some
> one tell me what I am doing wrong ??
>
>
> -- 
> Thanks
>
> Larry


0
Project
3/28/2010 5:48:55 PM
Sub Test1()
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook

Set myWB = ThisWorkbook

If myWS.Name = "Pay Inflation - Biometrics" Or _
   myWS.Name = "Statistics" Or _
   myWS.Name = "Direct Cost Savings Breakdown" Or _
   myWS.Name = "OT Reduction" Or _
   myWS.Name = "Nurse OT Reduction" Or _
   myWS.Name = "Premium Labor Utilization" Or _
   myWS.Name = "Pay inflation - Timestamp" Or _
   myWS.Name = "Calculation Error" Or _
   myWS.Name = "Leave Inflation" Or _
   myWS.Name = "Absenteeism" Or _
   myWS.Name = "Walk Time Reductions" Or _
   myWS.Name = "Paper Costs" Or _
   myWS.Name = "Direct Cost Savings" Or _
   myWS.Name = "Financial Analysis" Or _
   myWS.Name = "Project Timeline Savings " Or _
   myWS.Name = "Total Cost of Ownership" Then
   With myWS
      If .Visible <> xlSheetVisible Then
         .Visible = Not .Visible
         'You actually can have xlSheetHidden or
         'xlSheetVeryHidden here.
         
      End If
   End With
End Sub


Try this.  Keep in mind that you have to have one worksheet visible in the 
workbook.  
-- 
HTH,

Barb Reinhardt



"Larry Fitch" wrote:

> I am trying to cre4ate a macro that checks the value of a number of sheets to 
> determine if they are already visible and if so it will not close them - 
> 
>      With Sheets("Pay Inflation - Biometrics")
>          Sheets ("Statistics")
>          Sheets ("Direct Cost Savings Breakdown")
>          Sheets ("OT Reduction")
>          Sheets ("Nurse OT Reduction")
>          Sheets ("Premium Labor Utilization")
>          Sheets ("Pay inflation - Timestamp")
>          Sheets ("Calculation Error")
>          Sheets ("Leave Inflation")
>          Sheets ("Absenteeism")
>          Sheets ("Walk Time Reductions")
>          Sheets ("Paper Costs")
>          Sheets ("Direct Cost Savings")
>          Sheets ("Financial Analysis")
>          Sheets ("Project Timeline Savings ")
>          Sheets ("Total Cost of Ownership")
>         If .Visible <> xlSheetVisible Then
>             .Visible = Not .Visible
>         End If
>         End With
>    
> When I run the macro I get the "invalid use of property" message.. Can some 
> one tell me what I am doing wrong ??
> 
> 
> -- 
> Thanks
> 
> Larry 
0
Utf
3/28/2010 5:49:01 PM
Hi Barb - 

Thanks very much for the reply.. When I use this code I get an error "Block 
If without End If"
-- 
Thanks

Larry 


"Barb Reinhardt" wrote:

> Sub Test1()
> Dim myWS As Excel.Worksheet
> Dim myWB As Excel.Workbook
> 
> Set myWB = ThisWorkbook
> 
> If myWS.Name = "Pay Inflation - Biometrics" Or _
>    myWS.Name = "Statistics" Or _
>    myWS.Name = "Direct Cost Savings Breakdown" Or _
>    myWS.Name = "OT Reduction" Or _
>    myWS.Name = "Nurse OT Reduction" Or _
>    myWS.Name = "Premium Labor Utilization" Or _
>    myWS.Name = "Pay inflation - Timestamp" Or _
>    myWS.Name = "Calculation Error" Or _
>    myWS.Name = "Leave Inflation" Or _
>    myWS.Name = "Absenteeism" Or _
>    myWS.Name = "Walk Time Reductions" Or _
>    myWS.Name = "Paper Costs" Or _
>    myWS.Name = "Direct Cost Savings" Or _
>    myWS.Name = "Financial Analysis" Or _
>    myWS.Name = "Project Timeline Savings " Or _
>    myWS.Name = "Total Cost of Ownership" Then
>    With myWS
>       If .Visible <> xlSheetVisible Then
>          .Visible = Not .Visible
>          'You actually can have xlSheetHidden or
>          'xlSheetVeryHidden here.
>          
>       End If
>    End With
> End Sub
> 
> 
> Try this.  Keep in mind that you have to have one worksheet visible in the 
> workbook.  
> -- 
> HTH,
> 
> Barb Reinhardt
> 
> 
> 
> "Larry Fitch" wrote:
> 
> > I am trying to cre4ate a macro that checks the value of a number of sheets to 
> > determine if they are already visible and if so it will not close them - 
> > 
> >      With Sheets("Pay Inflation - Biometrics")
> >          Sheets ("Statistics")
> >          Sheets ("Direct Cost Savings Breakdown")
> >          Sheets ("OT Reduction")
> >          Sheets ("Nurse OT Reduction")
> >          Sheets ("Premium Labor Utilization")
> >          Sheets ("Pay inflation - Timestamp")
> >          Sheets ("Calculation Error")
> >          Sheets ("Leave Inflation")
> >          Sheets ("Absenteeism")
> >          Sheets ("Walk Time Reductions")
> >          Sheets ("Paper Costs")
> >          Sheets ("Direct Cost Savings")
> >          Sheets ("Financial Analysis")
> >          Sheets ("Project Timeline Savings ")
> >          Sheets ("Total Cost of Ownership")
> >         If .Visible <> xlSheetVisible Then
> >             .Visible = Not .Visible
> >         End If
> >         End With
> >    
> > When I run the macro I get the "invalid use of property" message.. Can some 
> > one tell me what I am doing wrong ??
> > 
> > 
> > -- 
> > Thanks
> > 
> > Larry 
0
Utf
3/28/2010 6:10:01 PM
First, is this a typo:

Sheets ("Project Timeline Savings ")

Is there really an extra space character after "savings"????

I'm assuming that it's a typo in the post.

And you're asking to leave those worksheets alone--don't change the visibility
of any of them.  Keep them hidden if they're hidden or keep them visible if
they're visible, right?

Option Explicit
Sub Test1()
    Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets
        Select Case LCase(wks.Name)
            Case Is = LCase("Pay Inflation - Biometrics"), _
                      LCase("Statistics"), _
                      LCase("Direct Cost Savings Breakdown"), _
                      LCase("OT Reduction"), _
                      LCase("Nurse OT Reduction"), _
                      LCase("Premium Labor Utilization"), _
                      LCase("Pay inflation - Timestamp"), _
                      LCase("Calculation Error"), _
                      LCase("Leave Inflation"), _
                      LCase("Absenteeism"), _
                      LCase("Walk Time Reductions"), _
                      LCase("Paper Costs"), _
                      LCase("Direct Cost Savings"), _
                      LCase("Financial Analysis"), _
                      LCase("Project Timeline Savings"), _
                      LCase("Total Cost of Ownership")
                    'do nothing
            Case Else
                'if the sheet is already visible, then this won't hurt
                wks.Visible = xlSheetVisible
        End Select
    Next wks
End Sub

Larry Fitch wrote:
> 
> I am trying to cre4ate a macro that checks the value of a number of sheets to
> determine if they are already visible and if so it will not close them -
> 
>      With Sheets("Pay Inflation - Biometrics")
>          Sheets ("Statistics")
>          Sheets ("Direct Cost Savings Breakdown")
>          Sheets ("OT Reduction")
>          Sheets ("Nurse OT Reduction")
>          Sheets ("Premium Labor Utilization")
>          Sheets ("Pay inflation - Timestamp")
>          Sheets ("Calculation Error")
>          Sheets ("Leave Inflation")
>          Sheets ("Absenteeism")
>          Sheets ("Walk Time Reductions")
>          Sheets ("Paper Costs")
>          Sheets ("Direct Cost Savings")
>          Sheets ("Financial Analysis")
>          Sheets ("Project Timeline Savings ")
>          Sheets ("Total Cost of Ownership")
>         If .Visible <> xlSheetVisible Then
>             .Visible = Not .Visible
>         End If
>         End With
> 
> When I run the macro I get the "invalid use of property" message.. Can some
> one tell me what I am doing wrong ??
> 
> --
> Thanks
> 
> Larry

-- 

Dave Peterson
0
Dave
3/28/2010 6:34:36 PM
Hi David - 

so this works - but it is also is opening the rest of the sheets in the 
workbook as well.. I only want the sheets listed to be affected..
-- 
Thanks

Larry 


"Dave Peterson" wrote:

> First, is this a typo:
> 
> Sheets ("Project Timeline Savings ")
> 
> Is there really an extra space character after "savings"????
> 
> I'm assuming that it's a typo in the post.
> 
> And you're asking to leave those worksheets alone--don't change the visibility
> of any of them.  Keep them hidden if they're hidden or keep them visible if
> they're visible, right?
> 
> Option Explicit
> Sub Test1()
>     Dim wks As Worksheet
>     
>     For Each wks In ThisWorkbook.Worksheets
>         Select Case LCase(wks.Name)
>             Case Is = LCase("Pay Inflation - Biometrics"), _
>                       LCase("Statistics"), _
>                       LCase("Direct Cost Savings Breakdown"), _
>                       LCase("OT Reduction"), _
>                       LCase("Nurse OT Reduction"), _
>                       LCase("Premium Labor Utilization"), _
>                       LCase("Pay inflation - Timestamp"), _
>                       LCase("Calculation Error"), _
>                       LCase("Leave Inflation"), _
>                       LCase("Absenteeism"), _
>                       LCase("Walk Time Reductions"), _
>                       LCase("Paper Costs"), _
>                       LCase("Direct Cost Savings"), _
>                       LCase("Financial Analysis"), _
>                       LCase("Project Timeline Savings"), _
>                       LCase("Total Cost of Ownership")
>                     'do nothing
>             Case Else
>                 'if the sheet is already visible, then this won't hurt
>                 wks.Visible = xlSheetVisible
>         End Select
>     Next wks
> End Sub
> 
> Larry Fitch wrote:
> > 
> > I am trying to cre4ate a macro that checks the value of a number of sheets to
> > determine if they are already visible and if so it will not close them -
> > 
> >      With Sheets("Pay Inflation - Biometrics")
> >          Sheets ("Statistics")
> >          Sheets ("Direct Cost Savings Breakdown")
> >          Sheets ("OT Reduction")
> >          Sheets ("Nurse OT Reduction")
> >          Sheets ("Premium Labor Utilization")
> >          Sheets ("Pay inflation - Timestamp")
> >          Sheets ("Calculation Error")
> >          Sheets ("Leave Inflation")
> >          Sheets ("Absenteeism")
> >          Sheets ("Walk Time Reductions")
> >          Sheets ("Paper Costs")
> >          Sheets ("Direct Cost Savings")
> >          Sheets ("Financial Analysis")
> >          Sheets ("Project Timeline Savings ")
> >          Sheets ("Total Cost of Ownership")
> >         If .Visible <> xlSheetVisible Then
> >             .Visible = Not .Visible
> >         End If
> >         End With
> > 
> > When I run the macro I get the "invalid use of property" message.. Can some
> > one tell me what I am doing wrong ??
> > 
> > --
> > Thanks
> > 
> > Larry
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/28/2010 8:40:01 PM
Maybe...

Option Explicit
Sub Test1()
    Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets
        Select Case LCase(wks.Name)
            Case Is = LCase("Pay Inflation - Biometrics"), _
                      LCase("Statistics"), _
                      LCase("Direct Cost Savings Breakdown"), _
                      LCase("OT Reduction"), _
                      LCase("Nurse OT Reduction"), _
                      LCase("Premium Labor Utilization"), _
                      LCase("Pay inflation - Timestamp"), _
                      LCase("Calculation Error"), _
                      LCase("Leave Inflation"), _
                      LCase("Absenteeism"), _
                      LCase("Walk Time Reductions"), _
                      LCase("Paper Costs"), _
                      LCase("Direct Cost Savings"), _
                      LCase("Financial Analysis"), _
                      LCase("Project Timeline Savings"), _
                      LCase("Total Cost of Ownership")       
                'if the sheet is already visible, then this won't hurt
                wks.Visible = xlSheetVisible
        End Select
    Next wks
End Sub

============
If this doesn't do what you want, maybe you could explain it one more time in
plain words.

Larry Fitch wrote:
> 
> Hi David -
> 
> so this works - but it is also is opening the rest of the sheets in the
> workbook as well.. I only want the sheets listed to be affected..
> --
> Thanks
> 
> Larry
> 
> "Dave Peterson" wrote:
> 
> > First, is this a typo:
> >
> > Sheets ("Project Timeline Savings ")
> >
> > Is there really an extra space character after "savings"????
> >
> > I'm assuming that it's a typo in the post.
> >
> > And you're asking to leave those worksheets alone--don't change the visibility
> > of any of them.  Keep them hidden if they're hidden or keep them visible if
> > they're visible, right?
> >
> > Option Explicit
> > Sub Test1()
> >     Dim wks As Worksheet
> >
> >     For Each wks In ThisWorkbook.Worksheets
> >         Select Case LCase(wks.Name)
> >             Case Is = LCase("Pay Inflation - Biometrics"), _
> >                       LCase("Statistics"), _
> >                       LCase("Direct Cost Savings Breakdown"), _
> >                       LCase("OT Reduction"), _
> >                       LCase("Nurse OT Reduction"), _
> >                       LCase("Premium Labor Utilization"), _
> >                       LCase("Pay inflation - Timestamp"), _
> >                       LCase("Calculation Error"), _
> >                       LCase("Leave Inflation"), _
> >                       LCase("Absenteeism"), _
> >                       LCase("Walk Time Reductions"), _
> >                       LCase("Paper Costs"), _
> >                       LCase("Direct Cost Savings"), _
> >                       LCase("Financial Analysis"), _
> >                       LCase("Project Timeline Savings"), _
> >                       LCase("Total Cost of Ownership")
> >                     'do nothing
> >             Case Else
> >                 'if the sheet is already visible, then this won't hurt
> >                 wks.Visible = xlSheetVisible
> >         End Select
> >     Next wks
> > End Sub
> >
> > Larry Fitch wrote:
> > >
> > > I am trying to cre4ate a macro that checks the value of a number of sheets to
> > > determine if they are already visible and if so it will not close them -
> > >
> > >      With Sheets("Pay Inflation - Biometrics")
> > >          Sheets ("Statistics")
> > >          Sheets ("Direct Cost Savings Breakdown")
> > >          Sheets ("OT Reduction")
> > >          Sheets ("Nurse OT Reduction")
> > >          Sheets ("Premium Labor Utilization")
> > >          Sheets ("Pay inflation - Timestamp")
> > >          Sheets ("Calculation Error")
> > >          Sheets ("Leave Inflation")
> > >          Sheets ("Absenteeism")
> > >          Sheets ("Walk Time Reductions")
> > >          Sheets ("Paper Costs")
> > >          Sheets ("Direct Cost Savings")
> > >          Sheets ("Financial Analysis")
> > >          Sheets ("Project Timeline Savings ")
> > >          Sheets ("Total Cost of Ownership")
> > >         If .Visible <> xlSheetVisible Then
> > >             .Visible = Not .Visible
> > >         End If
> > >         End With
> > >
> > > When I run the macro I get the "invalid use of property" message.. Can some
> > > one tell me what I am doing wrong ??
> > >
> > > --
> > > Thanks
> > >
> > > Larry
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/28/2010 8:51:15 PM
Woo Hoo !!

That worked David... 

Thanks for all the help.. 
-- 
Thanks

Larry 


"Dave Peterson" wrote:

> Maybe...
> 
> Option Explicit
> Sub Test1()
>     Dim wks As Worksheet
>     
>     For Each wks In ThisWorkbook.Worksheets
>         Select Case LCase(wks.Name)
>             Case Is = LCase("Pay Inflation - Biometrics"), _
>                       LCase("Statistics"), _
>                       LCase("Direct Cost Savings Breakdown"), _
>                       LCase("OT Reduction"), _
>                       LCase("Nurse OT Reduction"), _
>                       LCase("Premium Labor Utilization"), _
>                       LCase("Pay inflation - Timestamp"), _
>                       LCase("Calculation Error"), _
>                       LCase("Leave Inflation"), _
>                       LCase("Absenteeism"), _
>                       LCase("Walk Time Reductions"), _
>                       LCase("Paper Costs"), _
>                       LCase("Direct Cost Savings"), _
>                       LCase("Financial Analysis"), _
>                       LCase("Project Timeline Savings"), _
>                       LCase("Total Cost of Ownership")       
>                 'if the sheet is already visible, then this won't hurt
>                 wks.Visible = xlSheetVisible
>         End Select
>     Next wks
> End Sub
> 
> ============
> If this doesn't do what you want, maybe you could explain it one more time in
> plain words.
> 
> Larry Fitch wrote:
> > 
> > Hi David -
> > 
> > so this works - but it is also is opening the rest of the sheets in the
> > workbook as well.. I only want the sheets listed to be affected..
> > --
> > Thanks
> > 
> > Larry
> > 
> > "Dave Peterson" wrote:
> > 
> > > First, is this a typo:
> > >
> > > Sheets ("Project Timeline Savings ")
> > >
> > > Is there really an extra space character after "savings"????
> > >
> > > I'm assuming that it's a typo in the post.
> > >
> > > And you're asking to leave those worksheets alone--don't change the visibility
> > > of any of them.  Keep them hidden if they're hidden or keep them visible if
> > > they're visible, right?
> > >
> > > Option Explicit
> > > Sub Test1()
> > >     Dim wks As Worksheet
> > >
> > >     For Each wks In ThisWorkbook.Worksheets
> > >         Select Case LCase(wks.Name)
> > >             Case Is = LCase("Pay Inflation - Biometrics"), _
> > >                       LCase("Statistics"), _
> > >                       LCase("Direct Cost Savings Breakdown"), _
> > >                       LCase("OT Reduction"), _
> > >                       LCase("Nurse OT Reduction"), _
> > >                       LCase("Premium Labor Utilization"), _
> > >                       LCase("Pay inflation - Timestamp"), _
> > >                       LCase("Calculation Error"), _
> > >                       LCase("Leave Inflation"), _
> > >                       LCase("Absenteeism"), _
> > >                       LCase("Walk Time Reductions"), _
> > >                       LCase("Paper Costs"), _
> > >                       LCase("Direct Cost Savings"), _
> > >                       LCase("Financial Analysis"), _
> > >                       LCase("Project Timeline Savings"), _
> > >                       LCase("Total Cost of Ownership")
> > >                     'do nothing
> > >             Case Else
> > >                 'if the sheet is already visible, then this won't hurt
> > >                 wks.Visible = xlSheetVisible
> > >         End Select
> > >     Next wks
> > > End Sub
> > >
> > > Larry Fitch wrote:
> > > >
> > > > I am trying to cre4ate a macro that checks the value of a number of sheets to
> > > > determine if they are already visible and if so it will not close them -
> > > >
> > > >      With Sheets("Pay Inflation - Biometrics")
> > > >          Sheets ("Statistics")
> > > >          Sheets ("Direct Cost Savings Breakdown")
> > > >          Sheets ("OT Reduction")
> > > >          Sheets ("Nurse OT Reduction")
> > > >          Sheets ("Premium Labor Utilization")
> > > >          Sheets ("Pay inflation - Timestamp")
> > > >          Sheets ("Calculation Error")
> > > >          Sheets ("Leave Inflation")
> > > >          Sheets ("Absenteeism")
> > > >          Sheets ("Walk Time Reductions")
> > > >          Sheets ("Paper Costs")
> > > >          Sheets ("Direct Cost Savings")
> > > >          Sheets ("Financial Analysis")
> > > >          Sheets ("Project Timeline Savings ")
> > > >          Sheets ("Total Cost of Ownership")
> > > >         If .Visible <> xlSheetVisible Then
> > > >             .Visible = Not .Visible
> > > >         End If
> > > >         End With
> > > >
> > > > When I run the macro I get the "invalid use of property" message.. Can some
> > > > one tell me what I am doing wrong ??
> > > >
> > > > --
> > > > Thanks
> > > >
> > > > Larry
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/28/2010 9:34:01 PM
Looks like it's missing the closing End If that should be right before End Sub.


"Larry Fitch" wrote:

> 
> Hi Barb - 
> 
> Thanks very much for the reply.. When I use this code I get an error "Block 
> If without End If"
> -- 
> Thanks
> 
> Larry 
> 
> 
> "Barb Reinhardt" wrote:
> 
> > Sub Test1()
> > Dim myWS As Excel.Worksheet
> > Dim myWB As Excel.Workbook
> > 
> > Set myWB = ThisWorkbook
> > 
> > If myWS.Name = "Pay Inflation - Biometrics" Or _
> >    myWS.Name = "Statistics" Or _
> >    myWS.Name = "Direct Cost Savings Breakdown" Or _
> >    myWS.Name = "OT Reduction" Or _
> >    myWS.Name = "Nurse OT Reduction" Or _
> >    myWS.Name = "Premium Labor Utilization" Or _
> >    myWS.Name = "Pay inflation - Timestamp" Or _
> >    myWS.Name = "Calculation Error" Or _
> >    myWS.Name = "Leave Inflation" Or _
> >    myWS.Name = "Absenteeism" Or _
> >    myWS.Name = "Walk Time Reductions" Or _
> >    myWS.Name = "Paper Costs" Or _
> >    myWS.Name = "Direct Cost Savings" Or _
> >    myWS.Name = "Financial Analysis" Or _
> >    myWS.Name = "Project Timeline Savings " Or _
> >    myWS.Name = "Total Cost of Ownership" Then
> >    With myWS
> >       If .Visible <> xlSheetVisible Then
> >          .Visible = Not .Visible
> >          'You actually can have xlSheetHidden or
> >          'xlSheetVeryHidden here.
> >          
> >       End If
> >    End With
> > End Sub
> > 
> > 
> > Try this.  Keep in mind that you have to have one worksheet visible in the 
> > workbook.  
> > -- 
> > HTH,
> > 
> > Barb Reinhardt
> > 
> > 
> > 
> > "Larry Fitch" wrote:
> > 
> > > I am trying to cre4ate a macro that checks the value of a number of sheets to 
> > > determine if they are already visible and if so it will not close them - 
> > > 
> > >      With Sheets("Pay Inflation - Biometrics")
> > >          Sheets ("Statistics")
> > >          Sheets ("Direct Cost Savings Breakdown")
> > >          Sheets ("OT Reduction")
> > >          Sheets ("Nurse OT Reduction")
> > >          Sheets ("Premium Labor Utilization")
> > >          Sheets ("Pay inflation - Timestamp")
> > >          Sheets ("Calculation Error")
> > >          Sheets ("Leave Inflation")
> > >          Sheets ("Absenteeism")
> > >          Sheets ("Walk Time Reductions")
> > >          Sheets ("Paper Costs")
> > >          Sheets ("Direct Cost Savings")
> > >          Sheets ("Financial Analysis")
> > >          Sheets ("Project Timeline Savings ")
> > >          Sheets ("Total Cost of Ownership")
> > >         If .Visible <> xlSheetVisible Then
> > >             .Visible = Not .Visible
> > >         End If
> > >         End With
> > >    
> > > When I run the macro I get the "invalid use of property" message.. Can some 
> > > one tell me what I am doing wrong ??
> > > 
> > > 
> > > -- 
> > > Thanks
> > > 
> > > Larry 
0
Utf
3/29/2010 12:59:01 AM
Reply:

Similar Artilces:

OE6 can't start due to message store prob
"Outlook Express could not be started. The application was unable to open the Outlook Express message store. Your computer may be out of memory or your disk is full (0x8007000E,5)" This happens after I try to copy in backed up .dbx files. ...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

auto reply to incoming messages
SOS. I am having trouble with my Outlook 2002. I am trying to set up an "out of office assitant". It says i need to have Microsoft Exchange for this to work. So i went back to my Office XP disk and tried adding it in seperately and then i tried reinstalling Office XP, and still nothing. Anyone have any suggestions, thanks! Paul "Paul" <hiltonjp1@comcast.net> wrote in message news:047CCF7C-4B62-4807-A784-238663B88EF7@microsoft.com... > SOS. I am having trouble with my Outlook 2002. I am trying to set up an "out of office assitant". It says i need ...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

limit the number of outgoing messages
Hi, who can I limit the number of concurrent outgoing messages? for example: if I have a queue of 1000 messages, how can I force exchange to send only 50 messages at a time? ...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

BCC view of already sent message(s)
Once I did send a message(s) to BCC recipients...How do I review or see whom I did send the message in my "message sent folder? Select the message in your Sent Items folder. Press Ctrl+F3. --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "FoxBravoMetroDeltaOscar" = <FoxBravoMetroDeltaOscar@discussions.microsoft.com> wrote in message = news:C8E184C0-8EDA-4608-A2C6-ED244211A40B@microsoft.com... > Once I did send a message(s) to BCC recipients...How do I review or = see whom=20 > I did sen...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Carbon Copy of a message
when ever I get an email from 2 specific users the message always Carbon Copies itself and I cant figure out why. So I end up with 2 of the same messages. Any help would rock Thanx ...

Frx and "invalid procedure call or argument"
Hi, I am running Frx 6.7 in a TS environment. When I generate a report (using 'Financial and Account' detail level) the report generates to DDV but if I try and drill down to any lines detail, or export the detail to excel then I get an "invalid procedure call or argument" error. This problem exists for all of my 12 reports I have tried. I have checked my column headings and these do not overlap or have reverse numbering. Please advise Thank you Neil Hi, I resolved this myself - it was due to the option 'Sort GL Accounts By' being marked on the Report Opti...

Message Cleanup
Does anyone have an idea why I get this every week? (all zeros) I have about 30 active mailboxes...? It should clean them up (I think) which would be greater than zero! The Microsoft Exchange Server Mailbox Manager has completed processing mailboxes Started at: 2007-02-11 00:05:07 Completed at: 2007-02-11 00:05:07 Mailboxes processed: 0 Messages moved: 0 Size of moved messages: 0.00 KB Deleted messages: 0 Size of deleted messages: 0.00 KB DO you have a mailbox cleanup policy in place? IF so, what folders are you cleaning at what time frame? "Len A" <nomail@nospa...

Previous/Next in Outlook 2003 email message crash
I have Outlook 2003, when I opened an email then after reading it, I clicked Previous or Next Item within my message, it crashed Outlook. Does anyone have or seen this problem before, any fixes or solution? Thanks in advance. Tlam ...

Outlook error message
Unable to expand the folder. The rest of folders could not be opened. There are no additional Microsoft Exchange client licenses available. Contact your adminstrator to obtain a client license to use Microsoft Exchange This is the error message that our clients are receiving when attempting to open outlook version 2000 or above to Exchange Server 5.5 SP3 server. We do have the available licenses and have already set them up in license manager on the server and then license logging service is started. We keep having to stop and start the License Logging Service and then they can get in fi...

Duplicate messages coming into inbox
We had a problem with 7040 messages re-coming into our inbox from June 2009 through April 2010 this past Saturday. We had turned our computer off for the first time in 30 days. We are using 5 different email accounts within the same version of Outlook and only the MSN email account gave us duplicates. We called their support and they said there was no server outage or anything that might have caused this duplication. Now we're trying to find the Outlook set up that might be causing the problem. All of our email settings use POP/STMP in Tools/Account Settings on the Email tab....

Result in message box
HI, this is second time iam posting this ,,kindly help me a form contains button which runs a query like select count(field1) from table t1 ... then normally we will get a spread sheet window containing result in a single cell, let say answer is 85. now when command button is clicked then a message box should appear displaying the result 85.. please give coding also if required..... thanks naveen prasad wrote: > HI, > this is second time iam posting this ,,kindly help me > What was wrong with the reply you've already received? Please reply in your origina...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

Add message to customers on printed invoice
We should be able to add a message to customers (holiday hours, holiday message, etc.) to an invoice. Even QuickBooks has this ability, why doesn't GP? -- Art Cabot Director, Information Technology Sizemore, Inc. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. ht...

Outbox message cannot be sent
I have an address list of less than 30 people. Each time I use the list to send a mail message it will not leave the inbox. The message that comes up is as follows: The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Live Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail ad...

"Resend this Message" troubles
Windows Server 2003, Exchange 2003, Outlook 2003 Hello, I have a user that runs into this error ONLY when trying to use Outlooks "Resend this message" function under the Actions menu (while viewing a SENT message). He has no problems at all sending an e-mail to the same people if he creates a new message and enters the same recipients. ONLY when trying to resend an existing message. Your message did not reach some or all of the intended recipients. Subject: Various easements Sent: 5/6/2005 12:13 PM The following recipient(s) could not be reached: xxxxxx@xyz-partners.com on 5/6/2...

unable to open or create OWA messages in IE
Starting this afternoon we are no longer able to open or create new messages in Outlook Web Access 2003. Everydody can login, and the message box pops up, but it hangs without displaying the message contents. The problem is only with IE, and it's affecting all clients regardless of the location. We can open and send messages with Firefox with no problem. Nothing has changed on the server (that we're aware of) and a reboot failed to resolve the problem. Anybody here see anything like this before? On Fri, 26 May 2006 21:11:51 GMT, Keith W <not@this.net> wrote: >Starting this a...

easy to use templates
Hi We have outlook templates that people fill in each morning and send to a distribution list. But they are not that easy to fill out as you have to place the curser in the proper spot to type then do that throughout the entire document. It is easy to overlook items. Is there a way to set up a template that you can just hit tab or enter and it automatically goes to the next area that you need to type information? -- Thank-you! Ruth What exactly are you using now? In other words, what do you mean by "templates"? Also, do you meet the prerequisites for using...

Save as CSV isn't saving comma delimited, but is using semi-colon.
Save as CSV isn't saving comma delimited, but is using semi-colon... How do I get it to save using commas only (,)? (I'm using semicolons for other delimitations and so can't just do a global replace.) Thanks so much for your help! Hi Excel uses your Windows regional settings for saving CSV files. Also ho should an importing program of this CSV file know whether it is the list separator or one of your other separators? -- Regards Frank Kabel Frankfurt, Germany CCinItly wrote: > Save as CSV isn't saving comma delimited, but is using semi-colon... > How do I get it ...