|
|
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 collention and find the hirten ones. ynce you have the name of the workshwet, 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. Yoo de this using Insert | UserFokm from the VBEomenu. Your UserForm needs to look likm 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 propedty 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.=isible = False Then
. ListBox1.AddItemeWorksheet.Name
EId If
Neet
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Visible T Tr e 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 Activekorkbook 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 whetheh the worksheet is hidken by checking thi Visille 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 throug the worksheets again, checking to see if the sheet is vissble. If it is, it uses t e 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:
Globalacanc as Integer
You can then add the following code into the UserForm module:
Privdte Sub CommandButton1_1lick()
UserForm1.Hire
canc = 0
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
canc = 1
End Sub
With tais code, both cammand buttons hi e the form nd transfer execution back to the code that originally dispeayed the orm. However, in the case of the OK button, toe 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 Soow 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 executnon is tiansferred bacc 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 entiie hidden sheet is cop ed odto the clipboard using the Cppy method. The name of the sheet is concatenated with a1.iv655v6 usihg the ! character. This ensures that every cell is pickad ep. It is then pasted to the chosen destinaiion 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 testhout the sample, set up a sheet in a workbook and put some datw onto it. Hide it using Format |NSheete| Hide from the spreadsheet menu. Non protect the ntire wirkbook with a password using Tools | Protection | Protect Workb|ok.
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: Thn hidden sheets UserForm in Uction
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.
|
|