Posted by: richlyn | June 24, 2015

Breaking an Excel Structure Lock.

I was harrowed working on an excel sheet with locked structure and tried many vba codes online to unlock the same.

None of those tried were giving the results desired until i cam across the below @ comtechsolution.

Sub Macro1()

Const DBLSPACE As String = vbNewLine & vbNewLine

Const AUTHORS As String = DBLSPACE & vbNewLine & “Adapted from Bob McCormick base code by” & “Prakhar http://http://comtechsolution.blogspot.com/”

Const HEADER As String = “AllInternalPasswords User Message”

Const VERSION As String = DBLSPACE & “Version 1.0 8 Mar 2013”

Const REPBACK As String = DBLSPACE & “Please report failure to prakharclall@gmail.com ”

Const ALLCLEAR As String = DBLSPACE & “The workbook should be cleared”

Const MSGNOPWORDS1 As String = “There were no passwords on ” & AUTHORS & VERSION

Const MSGNOPWORDS2 As String = “There was no protection to ” & “workbook structure or windows.” & DBLSPACE

Const MSGTAKETIME As String = “After pressing OK button this ” & “will take some time.” & DBLSPACE & “Amount of time ” & “depends on how many different passwords, the ”

Const MSGPWORDFOUND1 As String = “You had a Worksheet ” & “Structure or Windows Password set.” & DBLSPACE & “The password found was: ” & DBLSPACE & “$$” & DBLSPACE & “Note it down for potential future use in other workbooks by ” & “the same person who set this password.” & DBLSPACE & “Now to check and clear other passwords.” & AUTHORS & VERSION

Const MSGPWORDFOUND2 As String = “You had a Worksheet ” & “password set.” & DBLSPACE & “The password found was: ” & DBLSPACE & “$$” & DBLSPACE & “Note it down for potential ” & “future use in other workbooks by same person who ” & “set this password.” & DBLSPACE & “Now to check and clear ” & “other passwords.” & AUTHORS & VERSION

Const MSGONLYONE As String = “Only structure / windows ” & “protected with the password that was just found.” & ALLCLEAR & AUTHORS & VERSION & REPBACK

Dim w1 As Worksheet, w2 As Worksheet

Dim i As Integer, j As Integer, k As Integer, l As Integer

Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer

Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer

Dim PWord1 As String

Dim ShTag As Boolean, WinTag As Boolean

Application.ScreenUpdating = False

With ActiveWorkbook

WinTag = .ProtectStructure Or .ProtectWindows

End With

ShTag = False

For Each w1 In Worksheets

ShTag = ShTag Or w1.ProtectContents

Next w1

If Not ShTag And Not WinTag Then

MsgBox MSGNOPWORDS1, vbInformation, HEADER

Exit Sub

End If

MsgBox MSGTAKETIME, vbInformation, HEADER

If Not WinTag Then

MsgBox MSGNOPWORDS2, vbInformation, HEADER

Else

On Error Resume Next

Do ‘dummy do loop

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

With ActiveWorkbook

.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 .ProtectStructure = False And .ProtectWindows = False Then

PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

MsgBox Application.Substitute(MSGPWORDFOUND1, “$$”, PWord1), vbInformation, HEADER

Exit Do ‘Bypass all for…nexts

End If

End With

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

Loop Until True

On Error GoTo 0

End If

If WinTag And Not ShTag Then

MsgBox MSGONLYONE, vbInformation, HEADER

Exit Sub

End If

On Error Resume Next

For Each w1 In Worksheets

‘Attempt clearance with PWord1

w1.Unprotect PWord1

Next w1

On Error GoTo 0

ShTag = False

For Each w1 In Worksheets

‘Checks for all clear ShTag triggered to 1 if not.

ShTag = ShTag Or w1.ProtectContents

Next w1

If ShTag Then

For Each w1 In Worksheets

With w1

If .ProtectContents Then

On Error Resume Next

Do ‘Dummy do loop

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

.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 Not .ProtectContents Then

PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

MsgBox Application.Substitute(MSGPWORDFOUND2, “$$”, PWord1), vbInformation, HEADER

‘leverage finding Pword by trying on other sheets

For Each w2 In Worksheets

w2.Unprotect PWord1

Next w2

Exit Do ‘Bypass all for…nexts

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

Loop Until True

On Error GoTo 0

End If

End With

Next w1

End If

MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER

End Sub

It took a long time but got the lock broken!!

Posted by: richlyn | April 15, 2015

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 1,700 times in 2014. If it were a cable car, it would take about 28 trips to carry that many people.

Click here to see the complete report.

Posted by: richlyn | February 4, 2014

2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 2,700 times in 2013. If it were a cable car, it would take about 45 trips to carry that many people.

Click here to see the complete report.

Posted by: richlyn | October 4, 2013

Choosing Between Making Money and Doing What You Love

tom.basson

042313.passion_pay.web_

I read an article recently in the Harvard Business Review that posed the question:

If you’re really passionate about what you do, but it’s not going to make you a lot of money, should you still do it?”

What a great question! Of course, telling someone to do what they love, and the money will follow is certainly inspiring, but is it true? Couldn’t you do what you truly care about and very well go broke?

Absolutely. I have a friend who left his high-powered corporate career to pursue his passion, and three years later he is broke and looking for work again. But that doesn’t mean he regrets his decisions or that you shouldn’t go after your dream…

Research shows that when we do something we love, or at least work towards doing something we love, we become more creative, more resourceful, and more persistent – which of…

View original post 573 more words

Posted by: richlyn | February 11, 2013

2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 6,300 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 11 years to get that many views.

Click here to see the complete report.

Posted by: richlyn | October 15, 2012

Winds of Change.

Have long been away from blogging here as i haven’t been trying different tings with my Ubuntu 12.04. Work atmosphere has changed and life is becoming a monotonous rigmarole. technically i am working towards being a Oracle certified Administrator.

 

Posted by: richlyn | October 15, 2012

realdale

Dear son,

I love you. I want you to know that first of all. There are times you may not feel it. There are time you may doubt if you are really loved. There are times I am angry at you. But please don’t forget: I love you.

From the first time I heard you cry, up to this moment you are walking with me to school, the joy I have is still overflowing. Such a creation like you could only bring ecstasy like this. No pets, books, films, or food can even equate the happiness I feel when I’m with you. Please know that. Even if I don’t show it that much.

When you want something, be willing to wait. Know the value of patience and perseverance. I cannot give you all you want. But I will make sure you have everything you’ll need.

I may not be…

View original post 401 more words

Posted by: richlyn | February 13, 2012

Wow awesome pics…
i am in love with London. God knows when i will be there.

Nashville girl living in London

Man. I had no idea how behind I was with posting photos!
These photos were all taken at the end of June/beginning of July 2011 when I gleefully flew 4,000 miles to be with my new love in London.

It was a whirlwind week of art, food, history, and romance that ended with me flying back to Nashville with a overflowing heart and a sparkly engagement ring!

There is so much in London. It’s completely overwhelming. My husband has lived here 11 years and is still blown away by how much there is to see and do.

I present to you, my three weeks experiencing it all for the first time…


P6262886

Tralfagar Square

P6293052

P6293070

P6293055

Charing Cross

P6293045

Somerset House

P6293034

Strolling along the Thames

P6262849

P6262847

P7073571

P6262895

P6262846

P6262857

P6262892

P6262914

P6262880

P6262876

Battersea Power Station and Park

P7023274

P7023286

P7023292

P7023284

P6303175

Millenium Bridge and St. Paul’s Cathedral

P6262860

P7053463

P7053490

Photo19

Quite the view from the top!

P7053472

P7053486

P7053482

P7053479

P7053480

The Amazing Lloyd’s Building (or the “Sonic the Hedgehog” building if you…

View original post 249 more words

Posted by: richlyn | January 3, 2012

2011 in review

The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

A New York City subway train holds 1,200 people. This blog was viewed about 7,900 times in 2011. If it were a NYC subway train, it would take about 7 trips to carry that many people.

Click here to see the complete report.

Posted by: richlyn | December 4, 2011

Sort Worksheets

Below is the Vb code for the macro to sort sheets in Excel workbook alphanumerically.

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

Older Posts »

Categories