Quick Excel Help Page 3

  • gang_of_bitches 5 Dec 2007 13:25:07 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    So, I've got to cheat on one of those annoying excel based quizzes, how do I remove the protection without knowing the password? I've seen it done just can't remember how.
  • Lutz 5 Dec 2007 13:28:21 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Depends how well it's been put together.
    If it's been done well it'll take you ages. Not to crack, but to locate where the info is hidden.

    Create a second sheet, and in A1 put:

    =Sheet1!A1

    Replacing sheet 1 with whatever the sheet is called. If that's disabled link through manually from a second workbook.

    If they're smart though they'll have the answers on a hidden sheet with a stupid name like "HGAHGIY" and you'll never find it.

    So get a password ripper and strip the password from the sheet.
  • gang_of_bitches 5 Dec 2007 13:29:33 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Thanks Lutz, I'll give it a try.
  • Lutz 5 Dec 2007 13:30:17 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Obviously copy and paste A1 on sheet 2 across the coloumns, then down the rows.
  • X201 5 Dec 2007 13:58:22 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Stick the following in VBS

    Sub PasswordBreaker()
    'Author unknown
    'Breaks worksheet password protection.
    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
  • gang_of_bitches 5 Dec 2007 14:05:16 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    X201 wrote:
    Stick the following in VBS

    Sub PasswordBreaker()
    'Author unknown
    'Breaks worksheet password protection.
    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


    Thanks.

    Imagine for a second that I'm truly ignorant. Where exactly do I enter this? I assume its somewhere in the area where you right click on the tab and go to view code for the visual basic screen? I tried just pastiting into the window there and nothing happened.
  • X201 5 Dec 2007 14:07:08 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Which version of Excel are you using?
  • gang_of_bitches 5 Dec 2007 14:08:34 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    X201 wrote:
    Which version of Excel are you using?



    4.0 I think.
  • X201 5 Dec 2007 14:17:01 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Go to tools>Macros

    click "Record New Macro" a little box with a stop button in it will appear - ignore it for now

    Type something into the sheet,
    click the STOP recording button

    Make a mental note of the workbook name
    Press ALT+F11

    three windows should appear one of the is the vbaproject window.
    in this look for an entry called VBAproject (your work book name)
    click the plus to the left of this and then open the Modules section and that should contain a module called module 1

    Double click Module 1 and in the main pane you should see something like this-


    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 05/12/2007 by
    '

    '
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "hello"
    Range("G12").Select
    End Sub


    what you need to do is totally remove that and paste what I posted earlier in place of it

    When done

    close the Visual Basic editor and got to the tools menu in excel
    find macros again and go to run macros

    run the macro called passwordbreaker

    good luck
  • gang_of_bitches 5 Dec 2007 14:24:52 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    X201 wrote:
    Go to tools>Macros

    click "Record New Macro" a little box with a stop button in it will appear - ignore it for now

    Type something into the sheet,
    click the STOP recording button

    Make a mental note of the workbook name
    Press ALT+F11

    three windows should appear one of the is the vbaproject window.
    in this look for an entry called VBAproject (your work book name)
    click the plus to the left of this and then open the Modules section and that should contain a module called module 1

    Double click Module 1 and in the main pane you should see something like this-


    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 05/12/2007 by
    '

    '
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "hello"
    Range("G12").Select
    End Sub


    what you need to do is totally remove that and paste what I posted earlier in place of it

    When done

    close the Visual Basic editor and got to the tools menu in excel
    find macros again and go to run macros

    run the macro called passwordbreaker

    good luck

    Wow. thanks for the effort, I'll try now.

    Its doing something, the hourglass is there, should it take a while to run?
  • catterz 5 Dec 2007 14:25:56 8,763 posts
    Seen 8 years ago
    Registered 19 years ago
    I just tried it myself, and whilst it displayed the incorrect password I set on the worksheet, it managed to unlock the sheet anyway so Kudos. :D

    EDIT: After retrying with a different password it crashed the program every time. Ah well.

    EDIT AGAIN: Strangely, after locking a sheet with the password "thispass" the password "AAAAAABAAABk" will unlock it. Try it. =/
  • X201 5 Dec 2007 14:34:07 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    It's worked on all of those quiz sheets that come around thatI've tried it on.

    Not sure if it's version specific code though.
  • gang_of_bitches 5 Dec 2007 14:38:10 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Woo Hoo it worked, you are a golden god!

    After it ran it said "one useable password is AABBBBABABBO"
    interestingly similar to catterz password.

    cheating is so much more educational than actually doing the quiz properly.
  • X201 5 Dec 2007 14:42:11 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    gang_of_bitches wrote:
    Woo Hoo it worked, you are a golden god!

    After it ran it said "one useable password is AABBBBABABBO"
    interestingly similar to catterz password.

    cheating is so much more educational than actually doing the quiz properly.

    Which quiz is it?
    Is it the Children's TV Programmes one?
    The password seemed familiar.
  • gang_of_bitches 5 Dec 2007 14:45:47 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    X201 wrote:
    gang_of_bitches wrote:
    Woo Hoo it worked, you are a golden god!

    After it ran it said "one useable password is AABBBBABABBO"
    interestingly similar to catterz password.

    cheating is so much more educational than actually doing the quiz properly.

    Which quiz is it?
    Is it the Children's TV Programmes one?
    The password seemed familiar.


    No, 90's Music Quiz, very similar format to the kids one though so maybe the same creator.
  • gang_of_bitches 20 Feb 2008 14:44:06 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Once again I throw myself on the mercy of the EG intelligencia.

    I've got a multi-tabbed document and all of a sudden one of the tabs only shows formulas and references in the cells rather than answers and data. Interstngly the results that were already on the sheet have remained.

    Any ideas on how to get it working again? Think its Excel 4.01 BTW.

    Thanks in advance.
  • Lutz 20 Feb 2008 14:49:42 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Tools / Options

    Click on "View tab", if you're not already on it.

    Uncheck "Formulas" at the bottom.
  • X201 20 Feb 2008 14:50:06 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Tools
    Options
    View

    Untick Formulas

    I think Lutz used special Admin speed typing drugs. :)
  • Grunk 20 Feb 2008 14:51:21 4,717 posts
    Seen 7 years ago
    Registered 16 years ago
    Check that you haven't changed the formatting of the cells.

    right click format cells. it should be general, not text
  • gang_of_bitches 20 Feb 2008 14:54:59 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    You guys are great. I would probably have got there in the end but you save me a load of fannying about.

    Obligatory virtual jaffas for you all.
  • BravoGolf Moderator 22 Feb 2008 11:09:09 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    Argh, shitness

    I have a group of 700 odd users in column A and I want to format any of those users that are already present in another sheet containing 101 users, how do I do that?
  • X201 22 Feb 2008 11:41:20 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    BravoGolf wrote:
    Argh, shitness

    I have a group of 700 odd users in column A and I want to format any of those users that are already present in another sheet containing 101 users, how do I do that?

    By "format" you mean highlight them is some way?
    Turn the cell red that sort of thing?
    or do you want to generate a third list that only has matches in it.
  • BravoGolf Moderator 22 Feb 2008 11:48:12 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    Yes, format them to a different colour (i.e. red). The googles results show how to do this against a set value but not an entire range
  • ruttyboy 4 Mar 2008 08:52:20 7,950 posts
    Seen 4 years ago
    Registered 18 years ago
    This seems to be the ideal thread for this :)

    I have a list of numerals proceeded by a letter, for example:

    A1
    A2
    ...
    A14
    A15
    B1
    B2
    ...
    B14
    B15
    C1
    C2
    ...
    C14
    C15

    When I sort these it includes the letter at the start, it seems to treat the first digit as an integer and the second digit as a decimal of the first, so it will sort them A1 then A10 through to A15, then A2, A3, A4 etc., followed by the same again for the B and C numbers.

    I've tried all sorts of formatting but I can't get it to sort in the correct order using both the prefix and the number as an integer.

    Please help!
  • X201 4 Mar 2008 08:58:24 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Is there a specific reason why you can't have A01, A02 etc?

    You're asking Excel to sort whilst dealing with three different factors here, Alphabetic and Numeric with the additional complication of non-uniform data length.


    Edit: The only way out of it the spings to mind at the moment is hidden columns that have the text and numbers split into the with the left, right text commands
  • ruttyboy 4 Mar 2008 09:00:04 7,950 posts
    Seen 4 years ago
    Registered 18 years ago
    X201 wrote:
    Is there a specific reason why you can't have A01, A02 etc?

    You're asking Excel to sort whilst dealing with three different factors here, Alphabetic and Numeric with the additional complication of non-uniform data length.


    Because it's a massive list and changing them all would be a pain in the arse? :)

    Out of interest, if they were uniform length do you know how it would be done?
  • Grunk 4 Mar 2008 09:12:19 4,717 posts
    Seen 7 years ago
    Registered 16 years ago
    you can write a macro to do it, and some kind of sorting algorithm (qsort or bubble sort).

    It's quite easy, but will be time consuming. Try googling to see if there is one already.

    Alternatively, I think there's a function like "left" or "right" which will strip off the leftmost or rightmost characters in a cell. if you could do that, you could seperate the letters and numbers then sort two resulting columns by number only and recombine them.

    I don't have excel here just now, so I can't look. but you might need to enlist the help of that annoying paperclip.

    (I do not know why they picked a paperclip, instead of a bikini clad Jessica Alba"
  • Lutz 4 Mar 2008 09:14:42 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    ruttyboy wrote:
    Out of interest, if they were uniform length do you know how it would be done?
    Still tricky, but you could probably split the data using "LEFT, RIGHT" etc, order, then re-CONCATENATE the data back together.
  • ruttyboy 4 Mar 2008 09:17:05 7,950 posts
    Seen 4 years ago
    Registered 18 years ago
    Hmm, OK, thanks peeps :)
  • X201 4 Mar 2008 09:19:08 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    With your data in column C

    Put the following in A1
    =LEFT(C1,1)

    Put the following in B1
    =IF(LEN(C1)=2,"0"+T(RIGHT(C1,1)),RIGHT(C1,2))

    Drag both down so that you have multiple lines

    select columns A,B and C
    go to Sort command in Data menu and tell it to sort first by column A and then by Column B
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.