R
Rock
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)
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)