VB "or" statement?

Posted by RoB on August 29, 2001 5:33 PM

How do I say "or" in VBA? I have a macro which asks the user for input. I want to test the input string to match 1 of 3 conditions. If it doesnt match, i want an error code to be given.

i was thinking:

userinput as string

if userinput = "1" or "2" or "3" then

code here

else

msgbox("error")

Thanks

Posted by RoB on August 29, 2001 5:42 PM

Here is my code

I guess "Or" is the statement for "or", lol. I'm getting an error in my Or statement though. Can someone see if something is wrong. Thanks

Private Sub Workbook_Open()

Dim CurrentWorkbook As String
Dim ContractSelect As String

CurrentWorkbook = ActiveWorkbook.Name

'Selects the Impact printer on Betty's Computer
Application.ActivePrinter = "EPSON LX-300+ on \\Betty\impact lx300"
ChDir "S:\FileServer\Shared Files"

'Asks user to select Contract
ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN")
'Converts the criteria entered to upper case
ContractSelect = StrConv(ContractSelect, vbUpperCase)

'Tests input
If ContractSelect = "CHEVY" Or "KEY" Or "AMERGEN" Then

'Opens and inputs necessary formulas
If ContractSelect = "CHEVY" Then

Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\RIC Chevy Keybank.xls"
Workbooks("RIC Chevy Keybank.xls").Activate
Range("AB2").Formula = "X"

Else

If ContractSelect = "KEY" Then
Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\RIC Chevy Keybank.xls"
Workbooks("RIC Chevy Keybank.xls").Activate
Range("C2").Formula = "X"

Else

If ContractSelect = "AMERGEN" Then
Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\American General Contract.xls"
End If
End If
End If

'closes the Selector workbook
Workbooks(CurrentWorkbook).Close

Else
'Error message if invalid input is detected
MsgBox ("You typed an invalid contract type, please close this file and open it again")
End If

End Sub

Posted by Anon on August 29, 2001 6:43 PM

Re: Here is my code

If ContractSelect = "CHEVY" Or ContractSelect ="KEY" Or ContractSelect ="AMERGEN" Then

CurrentWorkbook = ActiveWorkbook.Name 'Selects the Impact printer on Betty's Computer Application.ActivePrinter = "EPSON LX-300+ on \\Betty\impact lx300" ChDir "S:\FileServer\Shared Files" 'Asks user to select Contract ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN") 'Converts the criteria entered to upper case ContractSelect = StrConv(ContractSelect, vbUpperCase)

Posted by Anon on August 29, 2001 6:44 PM

Re: Here is my code

If ContractSelect = "CHEVY" Or ContractSelect ="KEY" Or ContractSelect ="AMERGEN" Then

I guess "Or" is the statement for "or", lol. I'm getting an error in my Or statement though. Can someone see if something is wrong. Thanks

If ContractSelect = "CHEVY" Or ContractSelect ="KEY" Or ContractSelect ="AMERGEN" Then

CurrentWorkbook = ActiveWorkbook.Name 'Selects the Impact printer on Betty's Computer Application.ActivePrinter = "EPSON LX-300+ on \\Betty\impact lx300" ChDir "S:\FileServer\Shared Files" 'Asks user to select Contract ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN") 'Converts the criteria entered to upper case ContractSelect = StrConv(ContractSelect, vbUpperCase)

Posted by Robb on August 30, 2001 5:17 AM

Re: Here is my code

Rob

Why not try Select Case:

'Asks user to select Contract
ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN")
'Converts the criteria entered to upper case
ContractSelect = StrConv(ContractSelect, vbUpperCase)

'Tests input

Select Case ContractSelect

'Opens and inputs necessary formulas
Case "CHEVY"

Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\RIC Chevy Keybank.xls"
Workbooks("RIC Chevy Keybank.xls").Activate
Range("AB2").Formula = "X"

Case "KEY"

Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\RIC Chevy Keybank.xls"
Workbooks("RIC Chevy Keybank.xls").Activate
Range("C2").Formula = "X"

Case "AMERGEN"

Workbooks.Open Filename:= _
"S:\FileServer\Shared Files\American General Contract.xls"

Case Else
'Error message if invalid input is detected
MsgBox ("You typed an invalid contract type, please close this file and open it again")

End Select

'closes the Selector workbook
Workbooks(CurrentWorkbook).Close

End Sub

Not too sure why you opened the Amerigen workbook though.

Regards

I guess "Or" is the statement for "or", lol. I'm getting an error in my Or statement though. Can someone see if something is wrong. Thanks CurrentWorkbook = ActiveWorkbook.Name 'Selects the Impact printer on Betty's Computer Application.ActivePrinter = "EPSON LX-300+ on \\Betty\impact lx300" ChDir "S:\FileServer\Shared Files" 'Asks user to select Contract ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN") 'Converts the criteria entered to upper case ContractSelect = StrConv(ContractSelect, vbUpperCase)

Posted by Robb on August 30, 2001 5:22 AM

sligh amendment Re: Here is my code

Changed the position of the close statement, in case it closes the wrong file.

'Selects the Impact printer on Betty's Computer Application.ActivePrinter = "EPSON LX-300+ on \\Betty\impact lx300" ChDir "S:\FileServer\Shared Files" 'Asks user to select Contract ContractSelect = InputBox("Which contract do you want to open? Type Below: CHEVY, KEY, OR AMERGEN") 'Converts the criteria entered to upper case ContractSelect = StrConv(ContractSelect, vbUpperCase)