Kent's Repository A Compilation of Life's Problems and Solutions and other things…
Browsing all posts in: Excel

Manually remove sheet protection from an Excel workbook

March 9

For Excel version 2010 and below, an easy VBA macro can be used to generate a compatible password to “un-protect” a worksheet. The macro that is floating around the internet does not actually reveal the password, but generates another password that will authenticate the same way to Excel. This was possible due to a weakness in the excel password system where it hashed passwords into a weak algorithm (Explanation Here).

For later version of Excel, “un-protecting” a worksheet became a little tougher, but still doable. The reason it’s possible to remove the sheet protection is because protection does not equal encryption, a protected sheet is not encrypted. Encryption is a totally different animal and not one that I have an easy answer for. But if you’re just looking to remove sheet protection, then chances are it can be done.

Read the rest of this entry »

Useful Excel Functions: Unhide all hidden worksheets

December 2

This one comes to you compliments of

The following short VBA code also can help you display all of the hidden sheets at the same time.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

3. Press the F5 key to run this macro. And the hidden sheets will be displayed at once.

Read the rest of this entry »

Useful Excel Functions: Substitute Multiple Characters

November 5

Normally, the excel substitute() function is limited to finding 1 string and substituting said string for another string. This Excel User Defined Function allows you to substitute multiple sets of strings/characters. I created a sheet with 2 columns (as named ranges), “CleanOLD” and “CleanNew” and used the following formula =SubstituteMultiple(TRIM(CLEAN(A1)),CleanOld,CleanNew)

Excel Multiple Substitute

Read the rest of this entry »