Tag Archives: Automatic

How to Recover Lost Excel Passwords

Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to.  What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary.  Simply put, if you don’t already have access to the worksheet, this macro will not help you.

Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with.  By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

  • accidentally (or intentionally) removing formulas, or other contents of locked cells
  • adding or deleting rows and columns
  • changing cell, column or row formats
  • sorting data
  • using AutoFilter or PivotTable reports
  • editing objects or scenarios


Protecting a worksheet will not keep users from

  • editing any unlocked cells in the worksheet
  • viewing all data in the worksheet, regardless of if it is in a locked cell or not

Being able to unlock a password protected worksheet is useful, when

  • you have forgotten the password on your own worksheet
  • a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
  • you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
  • you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data

Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet.  This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet.  For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”.  The opposite is also true (i.e. protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”).  Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.

The macro code below will “unlock” one worksheet at a time, using the method used above.  It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.


Sub PasswordRecovery()
'
'MACROS BY EXCELZOOM.COM
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Goal Seek to Solve A Problem

Microsoft Excel is jam packed with cool useful features that tend to go unused by most users, simply because they don’t know how to use them, or even that they exist.  One of these features is Excel’s Goal Seek tool.

First, some background on what it does.  Goal Seek is essentially the answer to every middle school aged kid’s math test word problem.  It is best used when you know the answer to the problem you’re trying to solve, but don’t know all the inputs.

For example, a simple word problem might be “You have 432 sheets of paper to give out to a class of 36 students.  How many sheets of paper does each student get.”  Well you know that 36 multiplied by something will give you 432, but you don’t know what that number is.  You could divide 432 by 36 to get the answer, but let’s assume that isn’t possible right now.  With Goal Seek, you need several things:

  • An answer expressed as a formula, in this case 432 is equal to 36 times something.  Don’t worry, the result of the formula doesn’t have to equal 432 yet.  That’s the magic of Goal Seek.
  • You need one part of the equation, in this case 36.


To start, type the following into your spreadsheet in the cells indicated.

 

1

 36

2

 <leave blank>

3

 =A1*A2

Next, open Goal Seek (Tools | Goal Seek, or Data tab on the Excel 2007 ribbon | What If Analysis | Goal Seek).

You are now going to be asked for three things. 

  1. Set Cell: this is the reference to the cell that contains your formula, in which you want Excel to produce your answer of 432.  In our example it should be A3.  Note: the Set Cell MUST contain a formula or function.
  2. To Value: this is the value you want in your Set Cell (A3).  The value here should be 432.
  3. By Changing Cell: Since we know that the formula in cell A3 is correct, and the value of 36 in cell A1 is correct, we want Excel to produce the answer of 432 by changing cell A2.  Type A2 in this box.  Note: the Changing Cell MUST contain a value.
  4. Click OK.

After clicking OK, Excel will attempt to find a solution to the equation.  Once it does, it will enter the missing number in cell A2 and your formula should now equal 432.

Obviously this is a basic example, and doesn’t use Goal Seek’s capabilities to their fullest extent.  Let’s take another example that looks at a company’s sales by product to get their desired level of revenue.

XYZ Company makes four different products, Product A, Product B, Product C, and Product D.  The company can produce 1,000, 750, 500, and 100 of each product respectively.  The products also sell for $15, $20, $35, and $50 respectively.  Their maximum revenue for products produced that month, therefore is as follows:

 

A

B

C

D

1

ProductProductionSales PriceNet Revenue

2

Product A1,000$15$15,000

3

Product B750$20$15,000

4

Product C500$35$17,500

5

Product D100$50$5,000

6

Total  $52,500

Now say the company feels that their underdog, Product D, could be producing much more.  They also want their revenues to be 60,000.  How many more of Product D will they need to produce?

To figure this out, run Goal Seek just like before, except this time, the “Set Cell” is cell D6, “To Value” is 60,000, and the “By Changing Cell” is cell B5.  After clicking OK, you’ll notice that Excel has changed the value in cell B5 to 250, indicating that you will need to increase production of Product D to 250 units from 100 in order to have revenues of $60,000, assuming everything else remains the same.

Create and Name a New Worksheet

Say you have a spreadsheet that keeps track of weekly sales information, which you want displayed in a new sheet for each month. You don’t want to copy an “original” worksheet each week, so a macro can help to automate this process.

The macro code below will copy the worksheet named “Original”, and place it after the last worksheet in your file. It will then prompt you to enter in the date that corresponds with that week. It will continue to do so until you enter a valid worksheet name (i.e. valid format and a name that isn’t already used somewhere else in the workbook. Make sure your workbook already has a file called “Original”, and it is formatted the way you want all your weekly reports to be formatted. (Note if your “Original” worksheet isn’t called “Original”, you can change the name in the macro code below to whatever you want).

Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub CopyNewSheet()
'
'MACROS BY EXCELZOOM.COM
'
Dim shname As String
Dim wrksh As Worksheet
Worksheets("Original").Copy after:=Sheets(Worksheets.Count)
Set wrksh = ActiveSheet
Do While shname <> wrksh.Name
shname = Application.InputBox _
(Prompt:="Enter This Week's Date")
On Error Resume Next
wrksh.Name = shname
On Error GoTo 0
Loop
Set wrksh = Nothing
End Sub

Run Macro At a Specified Time

Using the Application.OnTime Method will allow you to schedule a macro to be run at a specified time. The specified time can be either after certain amount of time has passed, or at a certain time of day. This is especially useful if you would like a daily or weekly report to be printed, without having to be around to print it. If the report is long, it might take a while to print, so it might be useful for the macro to run after business hours or on the weekend.

The only drawback to this macro is that the file must be open in order for it to run. You can have this automated for you by using the Windows Scheduler found in the Control Panel to open the file at a certain time, and have the code below run automatically when opened.

Copy all the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “my_macro” with whatever name you have given your macro.

Need help? Use our nifty guide to help figure out how to install and use your macros.

The code below will allow you to run a macro called “my_macro” 30 seconds from now. You can change the time to whatever time interval you would like.

Sub Run_After_30_Sec()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime Now + TimeValue("00:00:30"), "my_macro"
End Sub

Use the following if you wish to run the macro at a certain time of day. The code below will automatically run my_macro at 5pm (written in 24 hour time as 17:00:00).

Sub Run_At_5PM()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime TimeValue("17:00:00"), "my_macro"
End Sub

Run Macro Automatically on Open

Insert your macro code into a module, start with Sub Auto_Open(). Enter whatever commands you wish to have executed on opening the file and end with End Sub.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “Msgbox “Hello” ” with whatever macro commands you wish to have run whenever your file is opened.

Need help? Use our nifty guide to help figure out how to install and use your macros.


Sub Auto_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub

Use the following if you wish to put the macro in your workbook as opposed to in a module. Again replace “Msgbox “Hello”” with your macro command:

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub