Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VB Random Number Generation/Insertion/NextWorksheet
I've made a survey in excel that I will be sending out to multiple
individuals. When completed the surveys will then be imported to another program for statistical analysis. The workbook has about 13 worksheets. It is set up in a webpage fashion so they click continue at the bottom of a worksheet when they complete the page. Answers on each page are then linked to answersheets for a condensed version of data for ease of importing. Inorder to track individuals when all the information is collected I need to attach a number to their information. There are two basic ways I've thought of to fix this problem. The low tech way is to manually go in and create the number in each file before I send the survey out. The second way.........the better way..............is to generate a random number and have it inserted for me. Saving me the trouble of making a million files..... Here is what I want to happen: 1) Individual opens Excel - "Click to begin" button appears (This is seen) 2) Button generates random number....lets say............8 digits (Not seen) 3) Number which was generated is entered into three worksheets (answers, answers2, answers3). (Not seen) 4) Person is forwarded to first page of survey. (Seen) 5) Person goes through the survey.........the rest of it is already set up. If anyone could help me do this I would really appreciate it as I have figured out how I want it to work but no skills in implimenting it. =( Pretty please? =) |
#2
|
|||
|
|||
Hi Craig,
I think I have a solution for you.. 1 -- You need to set up three sheets with the names you indicated, i.e., Answers1, 2, 3 2 -- You need to paste the following code into a VBA module. 3 -- Assign the macro to a button on the sheet. You can do this by placing a "shape" rectangle or other from AutoShapes you can then add some text and then right click on the shape to assign a macro.. 4 -- Click on the button to run the macro.. The macro is rather crude but it will: a -- Place a sequential number in cell-a1 using the NOW() function in the form of 38409.8950347222. This is better than a random number since there is no guarantee that the same "random" will not show up again. The Now() function is guaranteed not to repeat. b -- The program will then set the format to hidden ";;;" for the number c -- Lastly the row will be hidden from view. Again, it is rather crude, but it should give you a starting point.. ----------------CODE----------------- Sub Survey() ' ' Macro recorded 2/26/2005 by GG For i = 1 To 3 ShtNm = "Answers" & i Worksheets(ShtNm).Range("a1").Value = Now() Worksheets(ShtNm).Range("a1").NumberFormat = ";;;" Worksheets(ShtNm).Range("a1").EntireRow.Hidden = True Next i End Sub --------------------------END CODE----------------- GG "Craig" wrote: I've made a survey in excel that I will be sending out to multiple individuals. When completed the surveys will then be imported to another program for statistical analysis. The workbook has about 13 worksheets. It is set up in a webpage fashion so they click continue at the bottom of a worksheet when they complete the page. Answers on each page are then linked to answersheets for a condensed version of data for ease of importing. Inorder to track individuals when all the information is collected I need to attach a number to their information. There are two basic ways I've thought of to fix this problem. The low tech way is to manually go in and create the number in each file before I send the survey out. The second way.........the better way..............is to generate a random number and have it inserted for me. Saving me the trouble of making a million files..... Here is what I want to happen: 1) Individual opens Excel - "Click to begin" button appears (This is seen) 2) Button generates random number....lets say............8 digits (Not seen) 3) Number which was generated is entered into three worksheets (answers, answers2, answers3). (Not seen) 4) Person is forwarded to first page of survey. (Seen) 5) Person goes through the survey.........the rest of it is already set up. If anyone could help me do this I would really appreciate it as I have figured out how I want it to work but no skills in implimenting it. =( Pretty please? =) |
#3
|
|||
|
|||
Thanks for the message. My brain is fried for the day but I'll give it a
shot tomorrow. Thanks! "GusGG" wrote in message ... Hi Craig, I think I have a solution for you.. 1 -- You need to set up three sheets with the names you indicated, i.e., Answers1, 2, 3 2 -- You need to paste the following code into a VBA module. 3 -- Assign the macro to a button on the sheet. You can do this by placing a "shape" rectangle or other from AutoShapes you can then add some text and then right click on the shape to assign a macro.. 4 -- Click on the button to run the macro.. The macro is rather crude but it will: a -- Place a sequential number in cell-a1 using the NOW() function in the form of 38409.8950347222. This is better than a random number since there is no guarantee that the same "random" will not show up again. The Now() function is guaranteed not to repeat. b -- The program will then set the format to hidden ";;;" for the number c -- Lastly the row will be hidden from view. Again, it is rather crude, but it should give you a starting point.. ----------------CODE----------------- Sub Survey() ' ' Macro recorded 2/26/2005 by GG For i = 1 To 3 ShtNm = "Answers" & i Worksheets(ShtNm).Range("a1").Value = Now() Worksheets(ShtNm).Range("a1").NumberFormat = ";;;" Worksheets(ShtNm).Range("a1").EntireRow.Hidden = True Next i End Sub --------------------------END CODE----------------- GG "Craig" wrote: I've made a survey in excel that I will be sending out to multiple individuals. When completed the surveys will then be imported to another program for statistical analysis. The workbook has about 13 worksheets. It is set up in a webpage fashion so they click continue at the bottom of a worksheet when they complete the page. Answers on each page are then linked to answersheets for a condensed version of data for ease of importing. Inorder to track individuals when all the information is collected I need to attach a number to their information. There are two basic ways I've thought of to fix this problem. The low tech way is to manually go in and create the number in each file before I send the survey out. The second way.........the better way..............is to generate a random number and have it inserted for me. Saving me the trouble of making a million files..... Here is what I want to happen: 1) Individual opens Excel - "Click to begin" button appears (This is seen) 2) Button generates random number....lets say............8 digits (Not seen) 3) Number which was generated is entered into three worksheets (answers, answers2, answers3). (Not seen) 4) Person is forwarded to first page of survey. (Seen) 5) Person goes through the survey.........the rest of it is already set up. If anyone could help me do this I would really appreciate it as I have figured out how I want it to work but no skills in implimenting it. =( Pretty please? =) |
#4
|
|||
|
|||
Ok so you might think that is pretty rough code but I can't tell the
difference. =) 1) The Worksheet row doesn't need to be hidden. The answer sheets are just linked cells to their answers on other worksheets so the answers are all condensed for easy export. I would like to be able to look at the answer sheets quickly so can I just turn the true into false and have it show up? 2) The NOW() function is really what I was trying to get at so thank you for using that! I thought that might be a more difficult thing to program so I was going with the probability random number thing. Two questions about this: 1) Curiosity question: How is the number figured? Multiplication of date/time stamps? 2) Is there a way to maintain the no identical numbers using the NOW() function using whole numbers or rounding the number which is generated? I don't really need a 15 digit number. 3) My "big" problem now is the hyperlink. I can add the macro and have it function correctly however when I hyperlink to the next worksheet it will not perform the macro BEFORE "executing" the hyperlink. In other words when I add the hyperlink it just takes me to the next page and doesn't preform the macro. Do I need to hyperlink to the worksheet in the macro? Ideas on getting this to work all on the same button? "GusGG" wrote in message ... Hi Craig, I think I have a solution for you.. 1 -- You need to set up three sheets with the names you indicated, i.e., Answers1, 2, 3 2 -- You need to paste the following code into a VBA module. 3 -- Assign the macro to a button on the sheet. You can do this by placing a "shape" rectangle or other from AutoShapes you can then add some text and then right click on the shape to assign a macro.. 4 -- Click on the button to run the macro.. The macro is rather crude but it will: a -- Place a sequential number in cell-a1 using the NOW() function in the form of 38409.8950347222. This is better than a random number since there is no guarantee that the same "random" will not show up again. The Now() function is guaranteed not to repeat. b -- The program will then set the format to hidden ";;;" for the number c -- Lastly the row will be hidden from view. Again, it is rather crude, but it should give you a starting point.. ----------------CODE----------------- Sub Survey() ' ' Macro recorded 2/26/2005 by GG For i = 1 To 3 ShtNm = "Answers" & i Worksheets(ShtNm).Range("a1").Value = Now() Worksheets(ShtNm).Range("a1").NumberFormat = ";;;" Worksheets(ShtNm).Range("a1").EntireRow.Hidden = True Next i End Sub --------------------------END CODE----------------- GG "Craig" wrote: I've made a survey in excel that I will be sending out to multiple individuals. When completed the surveys will then be imported to another program for statistical analysis. The workbook has about 13 worksheets. It is set up in a webpage fashion so they click continue at the bottom of a worksheet when they complete the page. Answers on each page are then linked to answersheets for a condensed version of data for ease of importing. Inorder to track individuals when all the information is collected I need to attach a number to their information. There are two basic ways I've thought of to fix this problem. The low tech way is to manually go in and create the number in each file before I send the survey out. The second way.........the better way..............is to generate a random number and have it inserted for me. Saving me the trouble of making a million files..... Here is what I want to happen: 1) Individual opens Excel - "Click to begin" button appears (This is seen) 2) Button generates random number....lets say............8 digits (Not seen) 3) Number which was generated is entered into three worksheets (answers, answers2, answers3). (Not seen) 4) Person is forwarded to first page of survey. (Seen) 5) Person goes through the survey.........the rest of it is already set up. If anyone could help me do this I would really appreciate it as I have figured out how I want it to work but no skills in implimenting it. =( Pretty please? =) |
#5
|
|||
|
|||
Other tasks i'm working on:
I have built a survey that functions much like a webpage. The first page will create a unique subject number and place it into worksheets called answers1, answers2 and answers3 which the user will never see. Each worksheet is hyperlinked to the previous worksheet and the next worksheet. What I would like to do is two things. 1) Automatically save the worksheet based on the subject number which was created and stored in the worksheet named Answers1 in cell A1. I would like this to be done each time the person clicks the hyperlinked button which takes them to the next worksheet. That way no information is lost and each file which is returned to me has a different name which was automatically generated. 2) I would like at the end of the survey the ability to have them click a button, have the button save the information one last time and then close excel. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How to keep random number from changing using RANDBETWEEN? | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) | |||
How can I get Positive values only from the random number generat. | Excel Discussion (Misc queries) | |||
Random Number Questions | Excel Worksheet Functions |