PDA

View Full Version : Solved: VBA -- IF Statement



PianoMan5
10-23-2012, 06:11 AM
Hello! For whatever reason, I cannot figure out how to get the below to work ideal. I only want the integers 1, 2, 3 or 4 to be acceptable and everything else kick out. I certainly tried to condense all of the if statements but if I use a single line with ORs it kicks out if the user entered one of the acceptable responses.

I know it's a bit elementary but I'd appreciate any help. :)

WeekNumber = Application.InputBox("What week of the period will you be reporting?", _
"Reporting// Week #", "Enter either: 1, 2, 3 or 4", Type:=1)
If WeekNumber = False Then Exit Sub
'User selected cancel
If Not WeekNumber = 1 Then
If Not WeekNumber = 2 Then
If Not WeekNumber = 3 Then
If Not WeekNumber = 4 Then
Answer = MsgBox("Please rerun the macro and select a week # between 1-4. ", _
vbOKOnly & vbExclamation, "Selection Error!")
Exit Sub
End If
End If
End If
End If

BrianMH
10-23-2012, 06:36 AM
weeknumber = Application.InputBox("What week of the period will you be reporting?", _
"Reporting// Week #", "Enter either: 1, 2, 3 or 4", Type:=1)
If weeknumber = False Then Exit Sub
'User selected cancel
Select Case weeknumber
Case "1", "2", "3", "4"
'input code to action if it is 1 - 4 here

Case Else
Answer = MsgBox("Please rerun the macro and select a week # between 1-4. ", _
vbOKOnly & vbExclamation, "Selection Error!")
End Select

PianoMan5
10-23-2012, 07:04 AM
Brian -- Thanks for the help! Unfortunately, the select case code does not work well as there isn't a difference between 1-4...the number only differiantes some information in a pivot table created later on.

Being there will never be information beyond the 4th week, I want to eliminate such input.

BrianMH
10-23-2012, 07:42 AM
This does eliminate that input as if it is not 1,2,3 or 4 it gives your message to rerun the macro. Unless I misunderstand your requirements.

If you wanted to combine the if statements you could try


if not (weeknumber = "1" or weeknumber = "2" or weeknumber = "3" or weeknumber = "4")

snb
10-23-2012, 09:14 AM
Sub snb()
Do Until InStr("1234", Left(Application.InputBox("What week of the period will you be reporting?", "Reporting// Week #", "Enter either: 1, 2, 3 or 4", 1), 1)) > 0
Loop
End Sub

PianoMan5
10-24-2012, 08:36 AM
SNB...thanks for the help! Brian -- What you suggested helped me to simply code what I had bit different (wasn't aware I could group things together via parenthesis). I am a newbie without any programming education so what I've learned is thru research / Google. :)

If Not (WeekNumber = 1 Or WeekNumber = 2 Or WeekNumber = 3 Or WeekNumber = 4) Then
Answer = MsgBox("Please rerun the macro and select a week # between 1-4. ", _
vbOKOnly & vbExclamation, "Selection Error!")
Exit Sub

BrianMH
10-25-2012, 05:30 AM
Thats how I've learned too!