Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
I have a grid of 100 random four letter words (no not BAD four lette
words!) and they range from C4 to L13. I am trying to write in VBA so can randomly select two words from the grid to create one 8 letter wor for random password generation. I know that this has been done, I don' want to recreate the wheel, but right now this is eluding me. any hel would be greatly appreciated! RZAL7 I've attached the workbook I have so far Attachment filename: passgenrzv01.xls Download attachment: http://www.excelforum.com/attachment.php?postid=59097 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
With the analysis toolpak installed:-
=INDEX($C$4:$L$13,RANDBETWEEN(1,10),RANDBETWEEN(1, 10))&INDEX($C$4:$L$13,RANDBETW EEN(1,10),RANDBETWEEN(1,10)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "rzal7 " wrote in message ... I have a grid of 100 random four letter words (no not BAD four letter words!) and they range from C4 to L13. I am trying to write in VBA so I can randomly select two words from the grid to create one 8 letter word for random password generation. I know that this has been done, I don't want to recreate the wheel, but right now this is eluding me. any help would be greatly appreciated! RZAL7 I've attached the workbook I have so far. Attachment filename: passgenrzv01.xls Download attachment: http://www.excelforum.com/attachment.php?postid=590974 --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
This works but sometimes generates only one word, any cure for that?
am also in a quandry on how to attach a button to the work sheet t activate this function. Thanks you very much for your post, BTW -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Thanks Ken, I guess I need to figure out how to attach this in a macro
-- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
To attach a button to the macro, go to the view menu, select toolbars, then select Control Toolbox. Select the icon that looks like a button (if you hover over it it will say "Button"). Draw the button where ever you want it on the sheet. If the macro is already in VBA, a dialog box will open asking you which macro you want to attach the button to. Select the appropriate one and you should be good to go.
If the macro is not in VBA when you create the Button, then once you have added the macro, right click on the button and select "Assign Macro..." Below is the full corrected form of the macro i posted earlier. If you don't want to generate multiple passwords with one click of the button, replace everything after the "Next k" line with MsgBox "The password is " & Password & "." Sub RandomPassword() Dim i As Integer, j As Integer, k As Integer Dim Password As String Begin: Password = "" For k = 1 To 2 Randomize i = Int(10 * Rnd + 1) Randomize j = Int(10 * Rnd + 1) Password = Password & Worksheets("sheet1").Cells(3 + i, 2 + j) Next k Dim answer As Integer answer = MsgBox(Prompt:="Password is " & Password & _ ". Do you want to generate another?", Buttons:=vbYesNo) If answer = vbYes Then GoTo Begin End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Hi Marcotte A,
I am only able to generate a dot as a password(!) using this code. Am I doing something wrong?. Martyn "Marcotte A" wrote in message ... To attach a button to the macro, go to the view menu, select toolbars, then select Control Toolbox. Select the icon that looks like a button (if you hover over it it will say "Button"). Draw the button where ever you want it on the sheet. If the macro is already in VBA, a dialog box will open asking you which macro you want to attach the button to. Select the appropriate one and you should be good to go. If the macro is not in VBA when you create the Button, then once you have added the macro, right click on the button and select "Assign Macro..." Below is the full corrected form of the macro i posted earlier. If you don't want to generate multiple passwords with one click of the button, replace everything after the "Next k" line with MsgBox "The password is " & Password & "." Sub RandomPassword() Dim i As Integer, j As Integer, k As Integer Dim Password As String Begin: Password = "" For k = 1 To 2 Randomize i = Int(10 * Rnd + 1) Randomize j = Int(10 * Rnd + 1) Password = Password & Worksheets("sheet1").Cells(3 + i, 2 + j) Next k Dim answer As Integer answer = MsgBox(Prompt:="Password is " & Password & _ ". Do you want to generate another?", Buttons:=vbYesNo) If answer = vbYes Then GoTo Begin End Sub --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.709 / Virus Database: 465 - Release Date: 22.06.2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Thanks Marcotte A,
Now it functions all right. But I wonder, which code section refers to the C4:L13 area cells? TIA "Marcotte A" wrote in message ... "Martyn" wrote: Hi Marcotte A, I am only able to generate a dot as a password(!) using this code. Am I doing something wrong?. Martyn Do you have source words in the range C4:L13? The code is designed to pick 2 random cells from a 10x10 range filled with 4 letter strings and combine them. I'm guessing that those cells are blank so the Password variable never changes from "", so when the MsgBox displays you just get what is in the double quotes (including the final dot). |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Password = Password & Worksheets("sheet1").Cells(3 + i, 2 + j)
Note the Worksheets("sheet1").Cells(3 + i, 2 + j) bit. The cells(x, y) section of that statement is a way of referencing a cell. If you ignore the i and j bit for the moment, you get Cells(3, 2) which is the same as Cells(Row 3, Column 2) which is the cell B2. Now, here is where the i and j come into play. Based on the code, i and j will always be equal to somewhere between 1 and 10 inclusive, so the Cells(Row, Column) bit will always give you Cells(Row 3 + {1 to 10}, Column 2 + {1 to 10}), so the very minimum you will get is Cells(Row 3 + 1, Column 2 + 1), which equals Cells(4, 3), which equals C4 and the max Cells(Row 3 + 10, Column 2 + 10), which equals Cells(13, 12), which equals L14 so your range of C4:L14 is encompassed within those parameters The Worksheets("sheet1"). before the Cells(... bit, simply says that the range being referred to is on Sheet1. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Martyn" wrote in message ... Thanks Marcotte A, Now it functions all right. But I wonder, which code section refers to the C4:L13 area cells? TIA "Marcotte A" wrote in message ... "Martyn" wrote: Hi Marcotte A, I am only able to generate a dot as a password(!) using this code. Am I doing something wrong?. Martyn Do you have source words in the range C4:L13? The code is designed to pick 2 random cells from a 10x10 range filled with 4 letter strings and combine them. I'm guessing that those cells are blank so the Password variable never changes from "", so when the MsgBox displays you just get what is in the double quotes (including the final dot). --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Now this is what I call "detailed info". Thanks a lot Ken.
Your answer to my question is "complete". MARTYN "Ken Wright" wrote in message ... Password = Password & Worksheets("sheet1").Cells(3 + i, 2 + j) Note the Worksheets("sheet1").Cells(3 + i, 2 + j) bit. The cells(x, y) section of that statement is a way of referencing a cell. If you ignore the i and j bit for the moment, you get Cells(3, 2) which is the same as Cells(Row 3, Column 2) which is the cell B2. Now, here is where the i and j come into play. Based on the code, i and j will always be equal to somewhere between 1 and 10 inclusive, so the Cells(Row, Column) bit will always give you Cells(Row 3 + {1 to 10}, Column 2 + {1 to 10}), so the very minimum you will get is Cells(Row 3 + 1, Column 2 + 1), which equals Cells(4, 3), which equals C4 and the max Cells(Row 3 + 10, Column 2 + 10), which equals Cells(13, 12), which equals L14 so your range of C4:L14 is encompassed within those parameters The Worksheets("sheet1"). before the Cells(... bit, simply says that the range being referred to is on Sheet1. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Martyn" wrote in message ... Thanks Marcotte A, Now it functions all right. But I wonder, which code section refers to the C4:L13 area cells? TIA "Marcotte A" wrote in message ... "Martyn" wrote: Hi Marcotte A, I am only able to generate a dot as a password(!) using this code. Am I doing something wrong?. Martyn Do you have source words in the range C4:L13? The code is designed to pick 2 random cells from a 10x10 range filled with 4 letter strings and combine them. I'm guessing that those cells are blank so the Password variable never changes from "", so when the MsgBox displays you just get what is in the double quotes (including the final dot). --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
Oops - Minor typo
for the moment, you get Cells(3, 2) which is the same as Cells(Row 3, Column 2) which is the cell B2. should be B3. Apologies. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ken Wright" wrote in message ... Password = Password & Worksheets("sheet1").Cells(3 + i, 2 + j) Note the Worksheets("sheet1").Cells(3 + i, 2 + j) bit. The cells(x, y) section of that statement is a way of referencing a cell. If you ignore the i and j bit for the moment, you get Cells(3, 2) which is the same as Cells(Row 3, Column 2) which is the cell B2. Now, here is where the i and j come into play. Based on the code, i and j will always be equal to somewhere between 1 and 10 inclusive, so the Cells(Row, Column) bit will always give you Cells(Row 3 + {1 to 10}, Column 2 + {1 to 10}), so the very minimum you will get is Cells(Row 3 + 1, Column 2 + 1), which equals Cells(4, 3), which equals C4 and the max Cells(Row 3 + 10, Column 2 + 10), which equals Cells(13, 12), which equals L14 so your range of C4:L14 is encompassed within those parameters The Worksheets("sheet1"). before the Cells(... bit, simply says that the range being referred to is on Sheet1. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Martyn" wrote in message ... Thanks Marcotte A, Now it functions all right. But I wonder, which code section refers to the C4:L13 area cells? TIA "Marcotte A" wrote in message ... "Martyn" wrote: Hi Marcotte A, I am only able to generate a dot as a password(!) using this code. Am I doing something wrong?. Martyn Do you have source words in the range C4:L13? The code is designed to pick 2 random cells from a 10x10 range filled with 4 letter strings and combine them. I'm guessing that those cells are blank so the Password variable never changes from "", so when the MsgBox displays you just get what is in the double quotes (including the final dot). --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random password gen - Excel VBA
My pleasure, but it was just an explanation of Marcotte's code, so credit where
it's due ( ie Marcotte :- ), especially for sticking at it till you got sorted :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- snip --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) | |||
how to automate opening a password protected excel file? e.g. a .xls that has a password set in the security tab. | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) | |||
Excel password but cant hide the sheets before entering password | Excel Programming |