VB Random Number Generation/Insertion/NextWorksheet

[Craig]

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?

=)

 

[Guest]

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]

Thanks for the message. My brain is fried for the day but I'll give it a
shot tomorrow. Thanks!

 

[Craig]

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?

 

[Craig]

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?