We help IT Professionals succeed at work.

Can't set worksheet to xlSheetVeryHidden

Medium Priority
446 Views
Last Modified: 2012-05-06
I am using the attached code and the following error is thrown
Run-time error 1004 Method 'visible' of object'_Worksheet' failed  
What happens is that the user makes can make multiple selections of different license type in a list box.  A second list box is then populated by companies which have those selected licenses.   When the use click the Go button these selections are used to create Excel workbooks via automation.  This code is used to hide the OEM Reporting Calculator worksheet if the license type is "TMLA-S" and to hide the "TMLA-S" worksheet if the License type for that company is "OEMPTLS"
The problem is that if the user select both an OEMPTLA company and a company having a TMLA-S license and the OEMPTLA precedes the TMLA-S company on the list, the above error is thrown.  If it is the other way around, the error is not thrown
I hope my description makes sense
'JV12042008 - Nested case statment added that is used to generate OEMPTLA, TMLA-S and non-OEMPTLA
    'License Calculators
    Select Case stLicType
        Case "OEMPTLA", "TMLA-S"
            Select Case True
                Case xlsheet.Name = "Cover Letter"
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                Case xlsheet.Name = "ES Amendment Fee Calculator" 'And Not clsAFC.HasES(lgLicenseID)
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                Case xlsheet.Name = "MPA Amendment Fee Calculator" 'And Not clsAFC.HasMPA(lgLicenseID)
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                Case xlsheet.Name = "MPA 6 Ch MSRP <$200 USD" 'And Not clsAFC.HasMPA(lgLicenseID)
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                Case xlsheet.Name = "MPA 6 Ch MSRP $200-$300 USD" 'And Not clsAFC.HasMPA(lgLicenseID)
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                    
                'jv01092009 Added owing to the addition of the DTS 2.0 and 5.1 calculators
                Case xlsheet.Name = "DTS 2.0+Digital Out Calculator"
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                Case xlsheet.Name = "DTS 5.1 Surround Calculator"
                    xlsheet.Visible = xlSheetVeryHidden
                    'GoTo SkipSheet2
                
                'JV12012008 - Added to hide License Fee Calculator if license type is OEMTPLA or TMLA-S
                Case xlsheet.Name = "License Fee Calculator"
                    xlsheet.Visible = xlSheetVeryHidden
            
                'JV12012008 - Added to display OEM Calculator sheet if license type is OEMTPLA
                Case xlsheet.Name = "OEM Reporting Calculator"
                    xlsheet.Visible = xlSheetVisible
                    Call sbSetOEMPTLADataSheet(stConame, stLicType, dtRoyEffDate, _
                      xlsheet, stEmail, dtPeriodDate, lgLicenseID, lgCompanyId)
                    If stLicType = "TMLA-S" Then
                        xlsheet.Visible = xlSheetVeryHidden
                    End If
                'JV12012008 - Added to display TMLA-S Calculator sheet if license type is TMLA-s
                Case xlsheet.Name = "TMLA-S Calculator"
                    xlsheet.Visible = xlSheetVisible
                    Call sbSetOEMPTLADataSheet(stConame, stLicType, dtRoyEffDate, _
                      xlsheet, stEmail, dtPeriodDate, lgLicenseID, lgCompanyId)
                    If stLicType = "OEMPTLA" Then
                        xlsheet.Visible = xlSheetVeryHidden   'Error is thrown here
                    End If
            End Select

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
It's tricky to follow exactly what your code is doing, but you have to have at least one sheet visible, which may be your issue.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.