?
Solved

Can't set worksheet to xlSheetVeryHidden

Posted on 2009-02-09
1
Medium Priority
?
416 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

0
Comment
Question by:chtullu135
1 Comment
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 23595284
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question