• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Can't set worksheet to xlSheetVeryHidden

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

0
Juan Velasquez
Asked:
Juan Velasquez
1 Solution
 
Rory ArchibaldCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now