IF then Elseif

[micko1]

Its been a while but I need help once again. I am having trouble with the syntax of the above. Scenario is I have a workbook (quoting template) that opens another workbook to print all the relevant documentation. The workbook to open is dependant on what state the user is in. I have a drop down box on the cover sheet where the user selects their state. What I want is if the state "NSW" is selected then the macro opens the required workbook on that server. Please see the part of the macro that is causing me the heartache.

Thanks heaps for taking the time to look at thsi for me.

Private Sub Print_Documents_Click()
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet
Dim fPath As String
'Setup
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Application.Dialogs(xlDialogPrinterSetup).Show

Set wb1 = ThisWorkbook
If wb1.Sheets("TOW Cover Sheet").Range("Y2").Value = "NSW" Then
Workbooks.Open = ("D:\major_Job_packs\Major Job Pack Docs.xlsm")

ElseIf wb1.Sheets("TOW Cover Sheet").Range("Y2").Value = "QLD" Then
Workbooks.Open = ("Q:\Major_Job_Packs\Major Job Pack Docs.xlsm")

ElseIf wb1.Sheets("TOW Cover Sheet").Range("Y2").Value = "VIC" then
Workbooks.Open ("E:\Major_Job_Packs\Major Job Pack Docs.xlsm")

Else
MsgBox "Please select state on cover sheet"
End If

 

[dave3009]

I think this might do what you need

Private Sub Print_Documents_Click()
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet
Dim DriveLet As String
Dim fPath As String
'Setup
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Application.Dialogs(xlDialogPrinterSetup).Show

Set wb1 = ThisWorkbook
Select Case wb1.Sheets("TOW Cover Sheet").Range("Y2").Value
    Case "NSW": DriveLet = "D"
    Case "QLD": DriveLet = "Q"
    Case "VIC": DriveLet = "E"
    Case Else: MsgBox "Please select state on cover sheet"
               DriveLet = "None"
End Select
If DriveLet <> "None" Then Workbooks.Open DriveLet & ":\major_Job_packs\Major Job Pack Docs.xlsm"

 

[micko1]

Dave
Thanks heaps for your help. Just had a quick look and it appears to be what I am after. There is times when the folder structure will be different. ie more than just the drive letter will change, I have toyed with the following adjustment to what you have given me, just wondering what you think. I also removed the "NONE" option. ( not sure if I realy need it).
Again I would like to say that I appreciate your help.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Dialogs(xlDialogPrinterSetup).Show

Set wb1 = ThisWorkbook
Select Case wb1.Sheets("TOW Cover Sheet").Range("Y2").Value
Case "NSW": DriveLet = "C"
Case "QLD": DriveLet = "Q"
Case "VIC": DriveLet = "V"

End Select
If DriveLet = "C" Then
Set wb2 = Workbooks.Open(DriveLet & ":\Program Files\Major Job Documents\Quoting\Major Job Pack Docs.xlsm")
ElseIf DriveLet = "q" Then
Set wb2 = Workbooks.Open(DriveLet & ":\Major_Job_Packs\Major Job Pack Docs.xlsm")

End If