Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form instead of message box to collect data
Currently I have 7 questions in Sheet1/Column A. Each question has a number
tied to it in Column B. For example A1 contains Question #1 while B1 contains the number 1, A2 contains Question #2 while B2 contains the number 2, and so on down the list. I am using the macro below to display each question in its own message box. In the message box, below each question, is a blank field to give an answer. Once the question is answered the operator can click OK or Cancel. If the operator clicks OK the question will be pasted into the first available even row Sheet2/Column B, the answer will be pasted into the next available even row Sheet2/Column B, and the procedure will continue with another question. If the operator clicks CANCEL the question and answer will still be pasted into Sheet2/Column B in the first available even row, as discussed, but the macro will stop running afterwards. Sub MacroMessagebox() Dim Answer As Variant Dim AnsRow As Long Dim I As Long Dim QARng As Range Dim QAWks As Worksheet Dim QueRow As Long Dim ResultWks As Worksheet Set QAWks = Worksheets("Sheet1") Set ResultsWks = Worksheets("Sheet2") Set QARng = QAWks.Range("A1", QAWks.Cells(Rows.Count, "B").End(xlUp)) LastRow = ResultsWks.Cells(Rows.Count, "B").End(xlUp).Row AnsRow = IIf(LastRow Mod 2 = 1, LastRow + 1, LastRow) QueRow = AnsRow + 2 Do For I = 1 To QARng.Rows.Count Answer = InputBox(QARng.Item(I, 1)) If Answer < "" Then ResultsWks.Cells(AnsRow, "B") = QARng.Item(I, 1) ResultsWks.Cells(QueRow, "B") = Answer AnsRow = AnsRow + 4 QueRow = QueRow + 4 Else Answer = MsgBox("You clicked Cancel. Do you want to quit now?", vbYesNo) If Answer = vbYes Then Exit Sub End If Next I Loop End Sub I am looking for help with a macro that, instead of using message boxes, will call 1 User Form (but I plan to add additional User Forms later) and paste its contents/answers into the first available even rows in Column B, just as the macro with the message boxes already does. I included the name of the User Form, its Labels, and its Text Boxes below. If you can provide assistance with this thank you. Question1 €“ UserForm1 Label1 data pasted into Sheet2: B4 (first available even row) Question2 - UserForm1 Label2 data pasted into Sheet2: B8 (next available even row) Question3 - UserForm1 Label3 data pasted into Sheet2: B12 (next available even row) Question4 - UserForm1 Label4 data pasted into Sheet2: B16 (next available even row) Question5 - UserForm1 Label5 data pasted into Sheet2: B20 (next available even row) Question6 - UserForm1 Label6 data pasted into Sheet2: B24 (next available even row) Question7 - UserForm1 Label7 data pasted into Sheet2: B28 (next available even row) Answer1 - User Form ComboBox1 data pasted into Sheet2: B4 (first available even row) Answer2 - User Form TextBox2 data pasted into Sheet2: B8 (next available even row) Answer3 - User Form TextBox3 data pasted into Sheet2: B12 (next available even row) Answer4 - User Form TextBox4 data pasted into Sheet2: B16 (next available even row) Answer5 - User Form TextBox5 data pasted into Sheet2: B20 (next available even row) Answer6 - User Form TextBox6 data pasted into Sheet2: B24 (next available even row) Answer7 - User Form TextBox7 data pasted into Sheet2: B28 (next available even row) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form instead of message box to collect data
Your Questions and Answers should be staggered:
Question1 €“ UserForm1 Label1 data pasted into Sheet2: B4 (first available even row) Question2 - UserForm1 Label2 data pasted into Sheet2: B8 (next available even row) Question3 - UserForm1 Label3 data pasted into Sheet2: B12 (next available even row) Question4 - UserForm1 Label4 data pasted into Sheet2: B16 (next available even row) Question5 - UserForm1 Label5 data pasted into Sheet2: B20 (next available even row) Question6 - UserForm1 Label6 data pasted into Sheet2: B24 (next available even row) Question7 - UserForm1 Label7 data pasted into Sheet2: B28 (next available even row) Answer1 - User Form ComboBox1 data pasted into Sheet2: B6 (first available even row) Answer2 - User Form TextBox2 data pasted into Sheet2: B10 (next available even row) Answer3 - User Form TextBox3 data pasted into Sheet2: B14 (next available even row) Answer4 - User Form TextBox4 data pasted into Sheet2: B18 (next available even row) Answer5 - User Form TextBox5 data pasted into Sheet2: B22 (next available even row) Answer6 - User Form TextBox6 data pasted into Sheet2: B26 (next available even row) Answer7 - User Form TextBox7 data pasted into Sheet2: B30 (next available even row) "Rock" wrote: Currently I have 7 questions in Sheet1/Column A. Each question has a number tied to it in Column B. For example A1 contains Question #1 while B1 contains the number 1, A2 contains Question #2 while B2 contains the number 2, and so on down the list. I am using the macro below to display each question in its own message box. In the message box, below each question, is a blank field to give an answer. Once the question is answered the operator can click OK or Cancel. If the operator clicks OK the question will be pasted into the first available even row Sheet2/Column B, the answer will be pasted into the next available even row Sheet2/Column B, and the procedure will continue with another question. If the operator clicks CANCEL the question and answer will still be pasted into Sheet2/Column B in the first available even row, as discussed, but the macro will stop running afterwards. Sub MacroMessagebox() Dim Answer As Variant Dim AnsRow As Long Dim I As Long Dim QARng As Range Dim QAWks As Worksheet Dim QueRow As Long Dim ResultWks As Worksheet Set QAWks = Worksheets("Sheet1") Set ResultsWks = Worksheets("Sheet2") Set QARng = QAWks.Range("A1", QAWks.Cells(Rows.Count, "B").End(xlUp)) LastRow = ResultsWks.Cells(Rows.Count, "B").End(xlUp).Row AnsRow = IIf(LastRow Mod 2 = 1, LastRow + 1, LastRow) QueRow = AnsRow + 2 Do For I = 1 To QARng.Rows.Count Answer = InputBox(QARng.Item(I, 1)) If Answer < "" Then ResultsWks.Cells(AnsRow, "B") = QARng.Item(I, 1) ResultsWks.Cells(QueRow, "B") = Answer AnsRow = AnsRow + 4 QueRow = QueRow + 4 Else Answer = MsgBox("You clicked Cancel. Do you want to quit now?", vbYesNo) If Answer = vbYes Then Exit Sub End If Next I Loop End Sub I am looking for help with a macro that, instead of using message boxes, will call 1 User Form (but I plan to add additional User Forms later) and paste its contents/answers into the first available even rows in Column B, just as the macro with the message boxes already does. I included the name of the User Form, its Labels, and its Text Boxes below. If you can provide assistance with this thank you. Question1 €“ UserForm1 Label1 data pasted into Sheet2: B4 (first available even row) Question2 - UserForm1 Label2 data pasted into Sheet2: B8 (next available even row) Question3 - UserForm1 Label3 data pasted into Sheet2: B12 (next available even row) Question4 - UserForm1 Label4 data pasted into Sheet2: B16 (next available even row) Question5 - UserForm1 Label5 data pasted into Sheet2: B20 (next available even row) Question6 - UserForm1 Label6 data pasted into Sheet2: B24 (next available even row) Question7 - UserForm1 Label7 data pasted into Sheet2: B28 (next available even row) Answer1 - User Form ComboBox1 data pasted into Sheet2: B4 (first available even row) Answer2 - User Form TextBox2 data pasted into Sheet2: B8 (next available even row) Answer3 - User Form TextBox3 data pasted into Sheet2: B12 (next available even row) Answer4 - User Form TextBox4 data pasted into Sheet2: B16 (next available even row) Answer5 - User Form TextBox5 data pasted into Sheet2: B20 (next available even row) Answer6 - User Form TextBox6 data pasted into Sheet2: B24 (next available even row) Answer7 - User Form TextBox7 data pasted into Sheet2: B28 (next available even row) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form instead of message box to collect data
Hi Philosophaie. I do not completely understand your post. Could you
elaborate? "Philosophaie" wrote: Your Questions and Answers should be staggered: Question1 €“ UserForm1 Label1 data pasted into Sheet2: B4 (first available even row) Question2 - UserForm1 Label2 data pasted into Sheet2: B8 (next available even row) Question3 - UserForm1 Label3 data pasted into Sheet2: B12 (next available even row) Question4 - UserForm1 Label4 data pasted into Sheet2: B16 (next available even row) Question5 - UserForm1 Label5 data pasted into Sheet2: B20 (next available even row) Question6 - UserForm1 Label6 data pasted into Sheet2: B24 (next available even row) Question7 - UserForm1 Label7 data pasted into Sheet2: B28 (next available even row) Answer1 - User Form ComboBox1 data pasted into Sheet2: B6 (first available even row) Answer2 - User Form TextBox2 data pasted into Sheet2: B10 (next available even row) Answer3 - User Form TextBox3 data pasted into Sheet2: B14 (next available even row) Answer4 - User Form TextBox4 data pasted into Sheet2: B18 (next available even row) Answer5 - User Form TextBox5 data pasted into Sheet2: B22 (next available even row) Answer6 - User Form TextBox6 data pasted into Sheet2: B26 (next available even row) Answer7 - User Form TextBox7 data pasted into Sheet2: B30 (next available even row) "Rock" wrote: Currently I have 7 questions in Sheet1/Column A. Each question has a number tied to it in Column B. For example A1 contains Question #1 while B1 contains the number 1, A2 contains Question #2 while B2 contains the number 2, and so on down the list. I am using the macro below to display each question in its own message box. In the message box, below each question, is a blank field to give an answer. Once the question is answered the operator can click OK or Cancel. If the operator clicks OK the question will be pasted into the first available even row Sheet2/Column B, the answer will be pasted into the next available even row Sheet2/Column B, and the procedure will continue with another question. If the operator clicks CANCEL the question and answer will still be pasted into Sheet2/Column B in the first available even row, as discussed, but the macro will stop running afterwards. Sub MacroMessagebox() Dim Answer As Variant Dim AnsRow As Long Dim I As Long Dim QARng As Range Dim QAWks As Worksheet Dim QueRow As Long Dim ResultWks As Worksheet Set QAWks = Worksheets("Sheet1") Set ResultsWks = Worksheets("Sheet2") Set QARng = QAWks.Range("A1", QAWks.Cells(Rows.Count, "B").End(xlUp)) LastRow = ResultsWks.Cells(Rows.Count, "B").End(xlUp).Row AnsRow = IIf(LastRow Mod 2 = 1, LastRow + 1, LastRow) QueRow = AnsRow + 2 Do For I = 1 To QARng.Rows.Count Answer = InputBox(QARng.Item(I, 1)) If Answer < "" Then ResultsWks.Cells(AnsRow, "B") = QARng.Item(I, 1) ResultsWks.Cells(QueRow, "B") = Answer AnsRow = AnsRow + 4 QueRow = QueRow + 4 Else Answer = MsgBox("You clicked Cancel. Do you want to quit now?", vbYesNo) If Answer = vbYes Then Exit Sub End If Next I Loop End Sub I am looking for help with a macro that, instead of using message boxes, will call 1 User Form (but I plan to add additional User Forms later) and paste its contents/answers into the first available even rows in Column B, just as the macro with the message boxes already does. I included the name of the User Form, its Labels, and its Text Boxes below. If you can provide assistance with this thank you. Question1 €“ UserForm1 Label1 data pasted into Sheet2: B4 (first available even row) Question2 - UserForm1 Label2 data pasted into Sheet2: B8 (next available even row) Question3 - UserForm1 Label3 data pasted into Sheet2: B12 (next available even row) Question4 - UserForm1 Label4 data pasted into Sheet2: B16 (next available even row) Question5 - UserForm1 Label5 data pasted into Sheet2: B20 (next available even row) Question6 - UserForm1 Label6 data pasted into Sheet2: B24 (next available even row) Question7 - UserForm1 Label7 data pasted into Sheet2: B28 (next available even row) Answer1 - User Form ComboBox1 data pasted into Sheet2: B4 (first available even row) Answer2 - User Form TextBox2 data pasted into Sheet2: B8 (next available even row) Answer3 - User Form TextBox3 data pasted into Sheet2: B12 (next available even row) Answer4 - User Form TextBox4 data pasted into Sheet2: B16 (next available even row) Answer5 - User Form TextBox5 data pasted into Sheet2: B20 (next available even row) Answer6 - User Form TextBox6 data pasted into Sheet2: B24 (next available even row) Answer7 - User Form TextBox7 data pasted into Sheet2: B28 (next available even row) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I collect specific data from cells in a fill-in form? | Excel Discussion (Misc queries) | |||
Returning focus to a user form after getting message box response | Excel Programming | |||
use a worksheet as a form to collect data | Excel Worksheet Functions | |||
use Excel2000 as web interface to collect user input? | Excel Programming | |||
Status Message User Form in Excel '97 | Excel Programming |