Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
You want the questions in column A and the answers in column B??
-- Gary''s Student - gsnu200829 "Paul" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
TYPO ALERT
For i = 0 To 2 that's a zero, not a letter o. :) susan On Jan 27, 10:15*am, Gary''s Student wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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. -- Gary''s Student - gsnu2007L "Paul" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
Weirdly enough Susan, "o" worked too lol
"Susan" wrote: TYPO ALERT For i = 0 To 2 that's a zero, not a letter o. :) susan On Jan 27, 10:15 am, Gary''s Student wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
Good catch!
The universe keeps telling me to use Option Explicit; some day I will learn to listen. -- Gary''s Student - gsnu200829 "Susan" wrote: TYPO ALERT For i = 0 To 2 that's a zero, not a letter o. :) susan On Jan 27, 10:15 am, Gary''s Student wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu2007L "Paul" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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). -- Rick (MVP - Excel) "Paul" wrote in message ... Weirdly enough Susan, "o" worked too lol "Susan" wrote: TYPO ALERT For i = 0 To 2 that's a zero, not a letter o. :) susan On Jan 27, 10:15 am, Gary''s Student wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
No clue whatsoever, so thought I'd ask the experts
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? "Gary''s Student" wrote: Good catch! The universe keeps telling me to use Option Explicit; some day I will learn to listen. -- Gary''s Student - gsnu200829 "Susan" wrote: TYPO ALERT For i = 0 To 2 that's a zero, not a letter o. :) susan On Jan 27, 10:15 am, Gary''s Student wrote: 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 -- Gary''s Student - gsnu200829 "Paul" wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have no clue about how to use vba | Excel Discussion (Misc queries) | |||
Have not a clue how to do this | Excel Programming | |||
No Clue... | Excel Programming | |||
I have not got a clue | Excel Discussion (Misc queries) | |||
any clue? | Excel Programming |