No clue whatsoever, so thought I'd ask the experts

[Paul]

Many years ago a colleague of mine created a program (or I think it was some
sort of VBA?) when I opened Excel (or Word) where a window would pop up,
asking me a question, to which I woudl type the data/answer. After all the
questions were answered, the program finished, then it put the data into
specific cells/tables...I found it fascinating but never knew how it was done.

I have a spreadsheet with a column of data, which is generally a title of a
topic or a question. To the right of the data is the answer to the left
column.

Simply put, if I had : -

Cell A1 as "What is your name", then Cell B1 would be "Paul"
Cell A2 as "Where do you live", then Cell B2 would be "England"

....etc, etc until all my Cells/Lines are complete.

Now...what I would like to do is use a "looping" program (if there is such
thing) to prompt a window to pop up (like a Userform) with a Label "What is
your Name" and a Text Box so I can type the answer "Paul".

I then want this to loop over and over again, so maybe a "Next" Command
Button needs to be there (and maybe, Previous, Close etc), this then prompts
the next question "Where do you live", which will input the data "England" on
the next line and so on and so on.

How would this be done?

Hope I've explained it OK. Hope you can help

Regards
Paul

 

[Gary''s Student]

You want the questions in column A and the answers in column B??

 

[Gary''s Student]

How about:

Sub AskMeNoQuestions()
Dim s(3)
s(0) = "What is the meaning of life?"
s(1) = "How many neutrinos are there in the universe?"
s(2) = "Why are the key pads on phones and computer keyboards different?"
For i = o To 2
x = Application.InputBox(prompt:=s(i), Type:=2)
j = i + 1
Cells(j, 1).Value = s(i)
Cells(j, 2).Value = x
Next
End Sub

 

[Paul]

STUNNING Gary...it looks so simple...so to continue the question and answer
to say 60 which bits would I need to add/change on your program?

Would you add s (4), s (5)....up to s (60) etc for as many questions as you
want?

Would the 2 in For i = 2 change too, to For i = 60?

And, if I wanted to change the Cell position to say C2 and D4 for the
question and answer, which bit would I change?

Apologies, but I know nothing about VBA

But thank you for your help, it's much appreciated

Regards
Paul

 

[Susan]

TYPO ALERT

For i = 0 To 2

that's a zero, not a letter o.


susan

 

[Gary''s Student]

You are right on track.

1. increase the Dim to match the number of questions
2. continue to enter the questions as in the sample
3. increase the limit in the For statement

To change the columns being used to store the Q/A we need to change the
Cells statements

For example:

Cells(j, 1).Value = s(i)

is really the same as:

Cells(j, "A").Value = s(i)

In fact, many programmers prefer using the second form.

If you run into difficulties, just update this post.

 

[Paul]

Weirdly enough Susan, "o" worked too lol

TYPO ALERT

For i = 0 To 2

that's a zero, not a letter o.


susan

 

[Gary''s Student]

Good catch!

The universe keeps telling me to use Option Explicit; some day I will learn
to listen.

 

[Paul]

It's worked brilliantly so far Gary...I'm sure there are going to be
additional information I would like to add but as a starter for 10 that is
spot on for me

Muchas Grasias

 

[Rick Rothstein]

The 'o' worked because you do not have an Option Explicit statement
protecting you from typing in undeclared variables; so VB simply created a
Variant variable named 'o' and, since it was being used in a context
requiring a counter, coerced it to an Integer (or perhaps a Double... not
positive about that) and used the default value of a newly declared numeric
variable which is '0' (zero).

 

[Paul]

If I created my own UserForm to make it look like I wanted, would I still be
able to use this VB Code? How would I then enter Open and Close boxes?