|
ABC ~ All
'Bout Computers Unhiding Multiple Worksheets in Excel Two ways to overcome Excel’s insistence that you unhide your hidden sheets one at a time, culminating in a very useful free add-in. Excel lets you hide worksheets within a workbook, which can be handy if – for instance – you want to conceal the workings of your spreadsheets, or want to keep the user interface clean and simple. Fortunately, you can also unhide these sheets, but ONLY ONE AT A TIME. This can be a major frustration if you need to do this a lot, particularly since the command is buried a couple of levels down in the menu structure. I have developed two VBA solutions to this problem, depending on the level of fine control you want over the operation. First of all, there is the quick and dirty method. This macro will simply cycle through all the worksheets in the current workbook and make them all visible: Sub UnHideAllSheets() For Each c In Sheets c.Visible = True Next c End Sub The second method, however, is a little more elaborate and provides direct control over the hide status of each sheet. It runs from the following userform, which I have called frmSelect:
By selecting sheets in either listbox and clicking the appropriate button, the selected sheets will be hidden or unhidden as desired. Standard Windows multi-select functionality is enabled. There is code in a normal module which sets up and opens the form: Public arrSheets() As Boolean
Sub HiddenSheetForm() ReDim arrSheets(Sheets.Count) frmSelect.lbHidden.Clear frmSelect.lbNon.Clear For i = 1 To Sheets.Count arrSheets(i) = Sheets(i).Visible If arrSheets(i) Then frmSelect.lbNon.AddItem Sheets(i).Name Else frmSelect.lbHidden.AddItem Sheets(i).Name End If Next i frmSelect.Show End Sub There is also code behind the form as follows: Private Sub btnHide_Click() For i = 0 To lbNon.ListCount - 1 If lbNon.Selected(i) = True Then For j = 1 To Sheets.Count If Sheets(j).Name = lbNon.List(i) Then arrSheets(j) = False Sheets(j).Visible = False End If Next j End If Next i UpdateForm End Sub
Private Sub btnUnhide_Click() For i = 0 To lbHidden.ListCount - 1 If lbHidden.Selected(i) = True Then For j = 1 To Sheets.Count If Sheets(j).Name = lbHidden.List(i) Then arrSheets(j) = True Sheets(j).Visible = True End If Next j End If Next i UpdateForm End Sub
Sub UpdateForm() lbNon.Clear lbHidden.Clear For i = 1 To Sheets.Count arrSheets(i) = Sheets(i).Visible If arrSheets(i) Then frmSelect.lbNon.AddItem Sheets(i).Name Else frmSelect.lbHidden.AddItem Sheets(i).Name End If Next i End Sub
Private Sub btnClose_Click() Me.Hide End Sub You can download this code packaged as an addin with its own toolbutton from www.grbps.com/addhid.htm. Ray Blake lives in England and spent 15 years training people in the financial services industry there. He had always used PCs in his work, and gradually realized he might make a career out of them. He and his business partner set up GR Business Process Solutions (www.grbps.com) which specializes in innovative IT to support knowledge testing and skills assessment. Although he spends a lot of time these days developing in VB and Access, Excel remains his favorite development tool, because, as he says, 'It can do everything; there's no computer application you can think of that you couldn't develop in Excel.' |
|
|||||
,
