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
937 Views

Similar Articles

[PageSpeed] 35

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:

Error message in Outlook when deleting
Hello- XP is the OS, Office 2000 and Outlook is the app with troubles. When trying to delete messages from my Inbox, an error message stating Messaging Interface unknown error, restart Outlook if problem persists. I have restarted multiple times and reinstalled Office 2000 in the repair mode, downloaded the updates and still cannot delete the messages. My inbox is getting very full! Please give guidance to repair this issue. Thanks! I have posted the same problem, and do not see a response. Nothing in the Knowledge Base has solved this problem for me, either. Let's hope we g...

Text in Message Box
When we send an e-mail in Outlook 2003, only a portion of the message in the text box appears for the recipeint and in the sent item message. Any ideas? >-----Original Message----- >When we send an e-mail in Outlook 2003, only a portion of >the message in the text box appears for the recipeint and >in the sent item message. Any ideas? >. >One thing to check is the IE updates. ...

Using Contacts in Journal
Yes, another journal question. Currently, I can only associate contacts in my "Contacts" folder with journal entries. As I am working on a LAN/WAN, there are several other contacts I have that I would like to associate with journal entries. Would like to pick contacts from Global Address List and the sublists to Global. Also have other "contact" folders in my "Outlook Today" folder (?) and would like to be able to select these individuals as well. How about contacts from public folders? Sorry, I work for a large corporation and have many many contacts in seve...

invalid OLE parameter type (vc++)
Hi all, I have an MFC app. I added an Automation Method via the ClassWizard which put this code in the .h file: afx_msg short dxOpen(); Then I added a parameter: afx_msg short dxOpen(CString* fName); Now when I try to go back to the ClassWizard, I get a parsing error: --- Parsing Error: "CString*" is not a valid OLE parameter type Input line: "afx_msg short dxOpen(CString* fName);" -- Any idea what I am doing wrong? Thanks J. J, The KB-article Q139072 details the acceptable parameter types for OLE automation. As the automation interface can be called from a nu...

Invalid registration key message
hi there, I have encountered the following error when I want to inserting some testing data into Greatplains database. The error message listed below: Error Number = 459 Stored Procedure taGLTransactionHeaderInsert Error Description = eConnect Registration has failed for General Ledger, Invalid Registration Key could anybody give me any advice on getting rid of this error ? cheers, GreatPlains User ...

Getting an error message "Cannot shift objects off of sheet"
While horizontally grouping (outlining) a set of columns, I receive an error message that reads "Cannot shift objects off of sheet" when I press the ( - ) to collapse the columns. There are no objects that I am aware of, and the message does not appear when the last column is ungrouped. Also, it appears when the previous group is collapsed and visa versa. That is, I can collapse one group and get the message when I try to collapse the other group. It is really strange the message pops up at some of the oddest times. Has anyone encountered this before. Anyone's help would b...

Qustion about perfomance using Exchange cache modus
To all Experts (and thanks for your reply), I have spend many Google searches on the advantages of the "Exchange cache modus" (Outlook 2003). On the Internet there is particularly spoken about the advantages on the client side (faster e-mail, always access to your e-mail, etc.) However, I could not find anything about the advantages or disadvantages on (performance of) the Exchange server when using cache modus My question with regard to the "Exchange cache mode" is simple: What strategy uses less recourses on the (Exchange) server, clients with or without cache mode...

Lost email messages
Today, after being gone on business for a week, I opened Outlook 2003. It showed 121 messages to be downloaded. The download seemed to be going fine but when it completed there were no new emails in my inbox and Outlook 2003 reported that I had errors in my .pst file. I ran the Inbox repair tool which says it fixed everything. Yet those 121 messages are gone:( I renamed outlook.pst to outlook.pst.bak, renamed my week old back up file to outlook.pst and tried to redownload the messages. No luck. A week's worth of email gone? That sucks:( Did they get wiped off the server when I dow...

Messages older than a week missing in my Inbox
I'm very, very new to using MSOutlook. I've been receiving messages and now messages older than "last week" are missing? I didn't archive them, I didn't delete them. They are just missing, Unfortunately, these are very, very important messages. Where are they? How can I find them? What do I do? -- Scott Check your "View" Settings to ensure it is not set to "Last Seven Days." How to do this may depend on your OUtlook version which you did not mention. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the ...

in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell?
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? Hi Daniel, See http://www.mvps.org/dmcritchie/excel/event.htm change event excel does not keep track of the old value, nor can you get if from the event code. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniel" <softwareengineer98037@yahoo.com> wrote i...

I got 'forbidden' popup message OWA.
I got 'forbidden' popup message when sending, replying emails using OWA. Please help! Also any configuration change gives me a also "Denied" Help please! Are you using a pop-up blocker on your workstation? On Sun, 20 Jun 2004 14:43:50 -0700, "TJC" <anonymous@discussions.microsoft.com> wrote: >I got 'forbidden' popup message when sending, replying >emails using OWA. Please help! Also any configuration >change gives me a also "Denied" Help please! No pop-up blocker and this is for all my 50 account users. Very strange becau...

Receiving messages with no text.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop When receiving new messages, I can view content and attachments on some <br> mails but not on others. Subject bar and name is always visible. There's no <br> pattern either as to which mails work and which don't. Have rebuilt <br> database, but with no apparent success. On 1/26/10 9:35 AM, emet51@officeformac.com wrote: > When receiving new messages, I can view content and attachments on > some mails but not on others. Subject bar and name is always vis...

trying to save a publisher doc to cd,message no disc in drive
I am trying to save a publisher doc (brochure) to a CD, each time I get a message that there is no disc in the drive, D or E, when each time there has been. What am I doing wrong. I have tried saving other things, in other programs without a problem. MsJLee <MsJLee@discussions.microsoft.com> was very recently heard to utter: > I am trying to save a publisher doc (brochure) to a CD, each time I > get a message that there is no disc in the drive, D or E, when each > time there has been. What am I doing wrong. I have tried saving > other things, in other programs with...

Best way to use CAsyncSocket.
What is the best way for implementing a CAsyncSocket derived class for being a TCP client? I've been using a class for a few years, but I want a better class. I'm thinking about writing a class that would incorporate a CAsyncSocket derived class. That CAsyncSocket derived class would run in a separate thread? Is using a separate thread the best way? If in a separate thread, should the class send data out of the thread every time it gets data, using ::PostMessage to the mainfrm? Is there a better way? So if I'm doing PostMessage I would first "new" ...

Message stuck in Outbox
Ihave three emails that I attempted to forward on to ather parties.They remain in my Windows mail and I am not able to delete them.This now prevents me fromsending any new emails ...

Prevent use of Colon
Hello all, Is there any way to prevent a user from entering a colon in a cell, as this converts the format to Date and makes things very complicated once I receive all these spreadsheets back from the users. Ive disabled the colon with ascii commands on the user form that 'feeds' the data. Though, perhaps there's also some way of doing this on the worksheet itself with validation that Im not aware? Many Thanks Ed --- Message posted from http://www.ExcelForum.com/ What should be entered instead of a colon? A period? You could have a macro that runs when you open the workbook...

0x8004010F message
Outlook 2003 WAS sending/receiving POP3 mail perfectly until I reinstalled Windows XP Home and now with the exact same version of Outlook, I'm getting an error message (0x8004010F) saying that the operation failed. Whats even more maddening is that when I click the "Test account settings" button, the test email sends, which proves my server settings are correct. A colleague of mine in work has the same issue (again only after reinstalling) but he's using XP Pro and Outlook 2002. Can some one please help us, its driving us mad ...

Query table using criteria from an Excel document
Hello, I have a Access table of names and addresses. There is a postcode (zipcode) text field. I also have an excel spreadsheet with a single column of postcodes; no duplicates. I need to get query to return each name and address in the table which has a postcode listed in the spreadsheet. Typing each postcode manually into the query seems clumsy. Any ideas? Add a link to the Excel data. Create a new query joining the linked Excel data and your Access table on the postcode field. Those that match will result. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...

Create Customer using web services
I would like to cal the GPWebservices using a formatted XML file. I was able to create a basic XML file to create a customer in GP. However I’m having trouble specifying the customer organization. The WSDL specifies the following format <ns1:Context> <!--Optional:--> <ns1:WorkOnBehalfOf>?</ns1:WorkOnBehalfOf> <!--Optional:--> <ns1:OrganizationKey/> <!--Optional:--> <ns1:CultureName>?</ns1:CultureName> <ns1:CurrencyType>?</ns1:CurrencyType...

How do i use auto text to fill in a word after type few letters
i am trying to set my auto text to like at the end of a document , if i type Sat... a screen tip will appear and display full Saturday? There is no autocomplete function in Word 2007, like that you describe, other than for dates. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> ...

Error Message #35
TRQL_NOT_LESS_OR_EQUAL Happens when I am on line. No certain pattern. ...

merged inbox folder from computer to lapton, now inbox messages are missing
i was trying to set microsoft outlook on my laptop. in the process, somehow, my main (stationary) computer's outlook inbox messages were automatically moved from that inbox to my laptops inbox. when i changed the configuration from internet (dial up) access to exchange (workgroup) the messages in both computer's inbox file disapeared. i'm looking for help on how to recover those messages from by inbox??? thanks erin.dorsett@idbois.ang.af.mil <anonymous@discussions.microsoft.com> wrote: > i was trying to set microsoft outlook on my laptop. in > the proces...

Using Float Type Variables for Primary on SQL 2005
We seem to have hit an oddity. We have a stored procedure that gets the next sequence number. It is protected by a transaction so no two processes could update the table of ids at the same time. Every so often, we get duplicates. How could that happen? Is the value based on mantissa? If so, is there a way to expanded it? Keep in mind, I can't change the type from float. Any help is greatly appreciated. Thanks in advance On 2010-07-19 16:19, RG wrote: > We seem to have hit an oddity. > Please don't multi-post. If you have to post to multiple newsgroup...

2 criteria cells used to select which validation list to use
Greetings All: (Excel 2003) I'm trying to "clean up" various workbooks and consolidate into one workbook (probably about 75 sheets, when done). I have come to the conclusion that, for clarity of my formulas, I should have used range names. I have the Name Manager, version 3.2 from Jan Karel, which hopefully will prove to be more than a little useful. My current problem relates to the use of validation, based on info from two cells, (either/or), and requiring a result of 1 of 2 validation lists. I hope the following is clear. Cell C3: Truck Cell D3 = 0 (not applicable) or 1...

How do I use a variable in a cell reference? #2
I'm trying to reference a cell using other cells, for example: B(60-A30), which is to mean reference the cell in the Bth column, and the (60-A30)th row. How do I do this in Excel 2000? I think you can use a command called Indirect. eg if you had "B1" written in cell A1 you could do the following: =indirect(A1) and it would retrieve the contense of cell B1 You can also use concatenate formulas within the indirect to construct a cell refrence from two cells. eg if Cell C1 contained the number 3 =indirect(concatenate("A",C1)) would retrieve the contense of Cell A3...