Spreadsheet prompting for information

[Troy]

I want to know if there is a way to create a spreadsheet template that will
prompt for information when opened. I want to have the prompts ask for
certain information and show in specified cells.

[vezerid]

The following code will prompt for a certain value when a workbook
opens.

Private Sub Workbook_Open()

Msg1 = "Please enter name"
Val1 = InputBox(Msg1)
If Val1 <> vbCancel Then
Sheet("Answers").Range("A1") = Val1
End If

End Sub

As you see you set the variable Msg1 to whatever input message you
want. Then, once a value has been entered in the InputBox, this value
is assigned to variable Val1, which is then entered in cell A1 of sheet
called Answers if the Cancel key was not clicked.

This body of code can be repeated INSIDE THE SAME SUBroutine several
times with modifications. I am suggesting this as an alternative to a
user form which can come up when the workbook opens, and which can ask
all questions together and be programmed to enter each form's control
to the appropriate cell.

To install such code:
Right-click on a sheet tab and choose View Code
Then, at top left of the new window that appears, double-click the
ThisWorkbook icon.
Paste the above code (with modifications) in the code window that
appears.

HTH
Kostis Vezerides

[Troy]

I pasted the script starting with "Private Sub Workbook_Open()" and I got an
error. What am I supposed to do with that. also, If i am going to repeat the
process, what gets copied over and over and where does it end

"vezerid" wrote:

> The following code will prompt for a certain value when a workbook
> opens.
>
> Private Sub Workbook_Open()
>
> Msg1 = "Please enter name"
> Val1 = InputBox(Msg1)
> If Val1 <> vbCancel Then
> Sheet("Answers").Range("A1") = Val1
> End If
>
> End Sub
>
> As you see you set the variable Msg1 to whatever input message you
> want. Then, once a value has been entered in the InputBox, this value
> is assigned to variable Val1, which is then entered in cell A1 of sheet
> called Answers if the Cancel key was not clicked.
>
> This body of code can be repeated INSIDE THE SAME SUBroutine several
> times with modifications. I am suggesting this as an alternative to a
> user form which can come up when the workbook opens, and which can ask
> all questions together and be programmed to enter each form's control
> to the appropriate cell.
>
> To install such code:
> Right-click on a sheet tab and choose View Code
> Then, at top left of the new window that appears, double-click the
> ThisWorkbook icon.
> Paste the above code (with modifications) in the code window that
> appears.
>
> HTH
> Kostis Vezerides
>
>

[vezerid]

What kind of error? Where did you paste it? When is the error produced?

I just saw an error in my code, change Sheet("Answers") to
Sheets("Answers"). Maybe this causes it? Did this line get yellow?

To handle multiple asnwers to be stored in a table I suggest you
actually create a sheet Answers. Put headers in the first row,
indicative of the parameter answered with each input box. Then use two
different pieces of code, one for the first reply in each new open and
the other (with modifications) for all other answers of each user.

The code snippet for the first answer (which creates a new row):

Private Sub Workbook_Open()
' Code for first answer in a new Open
Msg1 = "Please enter 1ST ANSWER"
Val1 = InputBox(Msg1)
If Val1 <> vbCancel Then
r = Sheets("Answers").Range("A65536").End(xlUp).Row+1
Sheets("Answers").Range("A" & r) = Val1
End If

'Code for subsequent answers in the same Open
Msg2 = "Please enter 2ND ANSWER"
Val2 = InputBox(Msg2)
If Val2 <> vbCancel Then
Sheets("Answers").Range("B" & r) = Val1
End If

End Sub

Note in the above code that the second snippet needs to be edited and
reused further down the routine. Do not forget to change the letter in
each answer -- Range("B" & r)

This whole thing could be written in a much more elegant way but it
should still do your job.

Does this help now?
Kostis Vezerides

[Troy]

ok, do me a huge favor. make thescript exactly how it should look if you were
doing this with multiple prompts. I can figure out mostly what needs to be
edited. I am confused by the
r = Sheets("Answers").Range("A65536").End(xlUp).Row+1
Sheets("Answers").Range("A" & r) = Val1

So set one out here that would like the real thing, or you can send it to my
e-mail:
[email protected]

"vezerid" wrote:

> What kind of error? Where did you paste it? When is the error produced?
>
> I just saw an error in my code, change Sheet("Answers") to
> Sheets("Answers"). Maybe this causes it? Did this line get yellow?
>
> To handle multiple asnwers to be stored in a table I suggest you
> actually create a sheet Answers. Put headers in the first row,
> indicative of the parameter answered with each input box. Then use two
> different pieces of code, one for the first reply in each new open and
> the other (with modifications) for all other answers of each user.
>
> The code snippet for the first answer (which creates a new row):
>
> Private Sub Workbook_Open()
> ' Code for first answer in a new Open
> Msg1 = "Please enter 1ST ANSWER"
> Val1 = InputBox(Msg1)
> If Val1 <> vbCancel Then
> r = Sheets("Answers").Range("A65536").End(xlUp).Row+1
> Sheets("Answers").Range("A" & r) = Val1
> End If
>
> 'Code for subsequent answers in the same Open
> Msg2 = "Please enter 2ND ANSWER"
> Val2 = InputBox(Msg2)
> If Val2 <> vbCancel Then
> Sheets("Answers").Range("B" & r) = Val1
> End If
>
> End Sub
>
> Note in the above code that the second snippet needs to be edited and
> reused further down the routine. Do not forget to change the letter in
> each answer -- Range("B" & r)
>
> This whole thing could be written in a much more elegant way but it
> should still do your job.
>
> Does this help now?
> Kostis Vezerides
>
>

[vezerid]

OK, I am sending you a workbook. In there I put code for 5 answers, but
I don;t know how many inputs you want and how you will phrase them.
Also, it is a simple approach so, if a user accidentally cancels the
first input box, things will be overwritten.

The part of the code that you cannot explain is for finding the next
available row. It does in VBA what you would do as a human user to find
the last row. You would go to the last row in Excel (65536) and then
you would press Ctrl+Up arrow. This would take you to the last row
filled. +1 gives the next row to write. This is needed for the first
answer only, to generate a new row.

If you want this to be fully proof and enforce the users to answer all
questions or at least all important questions you would be better off
with a User Form.

Regards,
Kostis Vezerides