Advertisement

11.19.2006 at 04:43PM PST, ID: 22066226
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.4

I need to hide / unhide selected sheets in an excel workbook. I ask this question under 'programming' nearly 2 weeks ago and have had no response.

Asked by rsmccall in Microsoft Excel Spreadsheet Software

Tags: , ,

I need a userbox (ListBox or CheckBox) to Hide / Unhide selected sheets. I have ~6 sheets (Summary, Data, Instructions, etc) that will be static, and 10 sheets (000, 100, 200, 300, 400, 500, 600, 700, 800 & 900) that I want to Hide / Unhide.

I need for the list or checkbox to show all 10 sheets for selection (Data A1:A10), with the existing hidden sheets unchecked and the existing unhidden sheets checked. I want a select/unselect all button. Also, in addition to hiding and unhiding the sheets I need to write the resulting sheet names to a list (Data B1:B?). I am using the following which shows ALL sheets in the workbook.

'=====================================================================
Private Sub Cancel_Click()
    UserForm1.Hide
    SplashScreen.Show
 End Sub
 Private Sub OK_Click()
    Dim i As Integer
    For i = 1 To Sheets.Count
        If Controls("Checkbox" & i).Value = True Then Sheets(i).Visible = True _
        Else: Sheets(i).Visible = False
    Next i
    UserForm1.Hide
    SplashScreen.Show
End Sub
Private Sub UserForm_Activate()
    Dim i As Integer
    Dim chb As Control
    On Error Resume Next
    For J = 1 To 100
        Controls.Remove ("Checkbox" & J)
   Next
    On Error GoTo 0
    For i = 1 To Sheets.Count
        Set chb = Controls.Add("Forms.Checkbox.1", "Checkbox" & i, True)
        chb.Caption = Sheets(i).Name
        If Sheets(i).Visible = True Then Controls("Checkbox" & i).Value = 1 _
        Else Controls("Checkbox" & i).Value = 0
        If i <= 15 Then
           chb.Top = 20 * i - 15
        Else
            If i <= 32 Then
                chb.Top = 20 * i - 315
                chb.Left = 125
            Else
                chb.Top = 20 * i - 655
                chb.Left = 230
            End If
        End If
    Next
End Sub
Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)
   ' Prevents use of the Close button
       If CloseMode = vbFormControlMenu Then
MsgBox "Clicking the Close button does not work." & vbLf & vbLf & "Please click the OK or Cancel button."
    Cancel = True
    End If
End Sub
'=====================================================================Start Free Trial
[+][-]11.19.2006 at 08:04PM PST, ID: 17977016

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Microsoft Excel Spreadsheet Software
Tags: excel, unhide, hide
Sign Up Now!
Solution Provided By: jeverist
Participating Experts: 1
Solution Grade: A
 
 
[+][-]12.27.2006 at 11:27PM PST, ID: 18206733

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]01.01.2007 at 03:22PM PST, ID: 18225262

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32