Macro WIP - Issue with user prompts

[This_Guy]

Hello,

I have written the following code:

Sub The_Prompts_Nightmare()

On Error Resume Next
    
Dim q1 As String
Dim q2 As String
Dim q3 As String
Dim q4 As String
q1 = InputBox("Do option 1? (y)", "Q1", "y")
q2 = InputBox("Do option 2 on entire workbook? (y)" & vbNewLine & vbNewLine & _
              "Do option 2 on selected cells only? (n)" & vbNewLine & vbNewLine & _
              "Or forget this all together (empty box)", "Q2", "")
q3 = InputBox("Do option 3 on entire workbook?  (y)" & vbNewLine & vbNewLine & _
              "Do option 3 on selected cells only? (n)" & vbNewLine & vbNewLine & _
              "Or forget this all together (empty box)", "Q3", "")
q4 = InputBox("Finally, do option 4? (y)", "Q4", "")

MsgBox "q1 = " & q1 & vbNewLine & "q2 = " & q2 & vbNewLine & "q3 = " & q3 & vbNewLine & "q4 = " & q4

If Trim(q1) = "y" Or "Y" Then
MsgBox "q1(y)... Trim(q1) = " & Trim(q1)
'Do some code here
End If

If Trim(q2) = "y" Or "Y" Then
MsgBox "q2(y)... Trim(q2) = " & Trim(q2)
'Do some code here

ElseIf Trim(q2) = "n" Or "N" Then
MsgBox "q2(n)... Trim(q2) = " & Trim(q2)
'Do some code here
End If

If Trim(q3) = "y" Or "Y" Then
MsgBox "q3(y)... Trim(q3) = " & Trim(q3)
'Do some code here

ElseIf Trim(q3) = "n" Or "N" Then
MsgBox "q3(n)... Trim(q3) = " & Trim(q3)
'Do some code here
End If

If Trim(q4) = "y" Or "Y" Then
MsgBox "q4(y)... Trim(q4) = " & Trim(q4)
'Do some code here
End If

End Sub

The problem is, that no matter what I type into the prompts (i.e., a "y", "n", or a blank ""), I get the prompts' output as though I had typed in "y"'s for all of them... What am I missing here?

Also, I am aware aware of the other prompt styles, such as the yes/no option boxes, but I prefer this route instead.

Thanks,
This guy

 

[ChrisM]

I just copied and pasted your code, up through the first Msgbox line, ran it, chose all "n", and the messagebox correctly showed 4 n's.

 

[This_Guy]

Yes sir that is correct. If you choose all n's, then the first message box will correctly show something like this:

q1 = n
q2 = n
q3 = n
q4 = n

However, the issue is the subsequent message boxes. For me, the second prompt shows the following:

q1... Trim(q1) = n

Per q1's if statement, this messagebox should not populate at all, as we had chosen n.

Also, the issue is present in the subsequent message boxes as well. For me, the third prompt shows the following:

q2... Trim(q2) = n

As we had chosen "n", the q2 elseif statement dictates that the result should have been:

q2... Trim(q2) = n

Do you see the issue now?

 

[ChrisM]

When you do multiple tests in an If-Then you have to repeat all terms:

Not correct --> If Trim(q1) = "y" Or "Y" Then

correct --> If Trim(q1) = "y" Or Trim(q1) = "Y" Then

But both can be written a bit easier with:

If Trim(lcase(q1)) = "y" Then

 

[This_Guy]

Wow Chris,

You sir are awesome. Thank you so much for the guidance... Seriously. Awesome.

For completeness, below is the full code in case anyone else out there encounters this problem as well.

Sub The_Prompts_Issue_Fixed()

On Error Resume Next
    
Dim q1 As String
Dim q2 As String
Dim q3 As String
Dim q4 As String
q1 = InputBox("Do option 1? (y)", "Q1", "y")
q2 = InputBox("Do option 2 on entire workbook? (y)" & vbNewLine & vbNewLine & _
              "Do option 2 on selected cells only? (n)" & vbNewLine & vbNewLine & _
              "Or forget this all together (empty box)", "Q2", "")
q3 = InputBox("Do option 3 on entire workbook?  (y)" & vbNewLine & vbNewLine & _
              "Do option 3 on selected cells only? (n)" & vbNewLine & vbNewLine & _
              "Or forget this all together (empty box)", "Q3", "")
q4 = InputBox("Finally, do option 4? (y)", "Q4", "")

MsgBox "q1 = " & q1 & vbNewLine & "q2 = " & q2 & vbNewLine & "q3 = " & q3 & vbNewLine & "q4 = " & q4

If LCase(Trim(q1)) = "y" Then
MsgBox "q1(y)... Trim(q1) = " & Trim(q1)
'Do some code here
End If

If LCase(Trim(q2)) = "y" Then
MsgBox "q2(y)... Trim(q2) = " & Trim(q2)
'Do some code here

ElseIf LCase(Trim(q2)) = "n" Then
MsgBox "q2(n)... Trim(q2) = " & Trim(q2)
'Do some code here
End If

If LCase(Trim(q3)) = "y" Then
MsgBox "q3(y)... Trim(q3) = " & Trim(q3)
'Do some code here

ElseIf LCase(Trim(q3)) = "n" Then
MsgBox "q3(n)... Trim(q3) = " & Trim(q3)
'Do some code here
End If

If LCase(Trim(q4)) = "y" Then
MsgBox "q4(y)... Trim(q4) = " & Trim(q4)
'Do some code here
End If

End Sub

Thanks again,
This guy