Chapter 36: Displaying Hidden Sheets Without a Password


Overview

This example shows the real power of the VBA language. If you hide a worksheet and protect it with a password, you are not supposed to be allowed to view it. There are password-cracking programs on the market, but later versions of Excel have encrypted the passwords so they cannot be cracked. Knowing this, you might assume that your worksheet is safe.

In the Excel object model, however, Microsoft left a ‚“back door ‚½ that you can exploit. You cannot view the passwords, but you can go through the Worksheets collection and find the hidden ones. Once you have the name of the worksheet, you can simply copy its contents onto a visible worksheet and view everything there.

For this code, you will need to insert a new UserForm to provide an interface to the user . You do this using Insert UserForm from the VBE menu. Your UserForm needs to look like Figure 36-1.


Figure 36-1: Designing a UserForm for viewing hidden sheets

This form has two list boxes, one to show all the hidden sheets and one to show the possible destination sheets (which are visible). The user clicks a hidden sheet to select it and then clicks a sheet in the visible list to select the destination.

You need to include two label controls to hold the headings for the list boxes so the user can see what each one represents. You also need two command buttons for OK and Cancel actions. The caption on the form can be changed by clicking the form and altering the Caption property in the Properties window.

This example UserForm is different from previous ones because the form has to be populated with information before it is displayed. If you do not do this, the user will only see two empty list boxes, which will not be very useful.

To write the code to do this, double-click the form to enter the code window for the UserForm. The first piece of code needs to go in the UserForm activate event, which is called whenever the form is first displayed. On the module, select UserForm in the top-left drop-down menu and select Activate in the top-right drop-down menu. Insert the following code:

 Private Sub UserForm_Activate() 
ListBox1.Clear
ListBox2.Clear
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Visible = False Then

ListBox1.AddItem Worksheet.Name

End If
Next
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Visible = True Then

ListBox2.AddItem Worksheet.Name

End If
Next
End Sub

The first thing the code does is ensure that both list boxes are completely empty by using the Clear method.

The code then cycles through each worksheet within the ActiveWorkbook Worksheets collection. Notice that you are only using the active workbook here and not all workbooks that may be loaded. If you used all workbooks, they could be difficult to show in the list boxes, and it could lead to addressing problems. Although you could show all of them by concatenating on the workbook name or adding another list box, it would get quite confusing.

The code checks whether the worksheet is hidden by checking the Visible property. If it is set to false, it uses the AddItem method of the list box to add the worksheet name to the first list box.

It then goes through the worksheets again, checking to see if the sheet is visible. If it is, it uses the AddItem method to add the worksheet name to the second list box. When the code opens the UserForm, the user sees two list boxes, one with all the hidden sheets in it and the other with all the visible sheets.

You also need some code attached to the OK and Cancel buttons. For this you will insert a global variable in the declarations part of a module called canc :

 Global canc as Integer 

You can then add the following code into the UserForm module:

 Private Sub CommandButton1_Click() 

UserForm1.Hide

canc = 0

End Sub Private Sub CommandButton2_Click()

UserForm1.Hide

canc = 1

End Sub

With this code, both command buttons hide the form and transfer execution back to the code that originally displayed the form. However, in the case of the OK button, the global variable canc is set to 0, and it is set to 1 if the Cancel button is clicked.

Having set the user interface up, you need to add some code into a module:

 Sub hidden_sheets() 
UserForm1.Show
If canc = 1 Then Exit Sub
s1 = UserForm1.ListBox1.Text
s2 = UserForm1.ListBox2.Text
If s1 = "" Or s2 = "" Then Exit Sub
Range(s1 & "!a1.iv65536").Copy
Range(s2 & "!a1").PasteSpecial
Range(s2 & "!a1").Select
End Sub

The code is fairly straightforward for what it does. The first line shows your UserForm using the Show method. This allows the user to see all the names of the hidden sheets and make a selection of where they would like them copied to.

When the form is hidden and execution is transferred back to the hidden_sheets routine, the canc variable is checked. If the user clicked Cancel, the procedure is exited and nothing happens. If the user clicked OK, the hidden_sheets procedure is executed.

Two variables , s1 and s2 , are loaded with the text selected from each list box. If no selection was made in one or the other of the list boxes, the procedure is exited and nothing happens.

The entire hidden sheet is copied onto the clipboard using the Copy method. The name of the sheet is concatenated with a1.iv65536 using the ! character. This ensures that every cell is picked up. It is then pasted to the chosen destination using the PasteSpecial method. In the example code, the cell A1 is the destination. Cell A1 is then selected on the destination sheet, which makes it the current worksheet and moves the focus to it.

To test out the sample, set up a sheet in a workbook and put some data onto it. Hide it using Format Sheet Hide from the spreadsheet menu. Now protect the entire workbook with a password using Tools Protection Protect Workbook.

If you try to make your hidden sheet appear, it is impossible . The option Format Sheet Unhide on the spreadsheet menu is now disabled. Without the password, you cannot see the sheet; but run the code and you will see a screen similar to Figure 36-2.


Figure 36-2: The hidden sheets UserForm in action

Select the hidden sheet and then select a destination sheet. Click OK, and the contents of the hidden sheet will appear on your destination sheet. Remember, this procedure does not check whether the destination sheet is blank. Any data on it will be completely overwritten and lost.




Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net