If statement not working

[Jay]

Two questions about the following code.
1- why is it not working?
2- is there a cleaner way to accomplish this?

Sub Test()

If Range("F4") = "" Then
MsgBox "Enter Person Reporting in Cell F4"
Exit Sub
End If

If Range("F4") = "Jay" Then
mySourceWkbkName2 = "F:\files\ProjTimeTracking.xls"
If Range("F4") = "Dave" Then
mySourceWkbkName2 = "H:\FAC\Dave
Sipes\DavProjTimeTracking.xls"
If Range("F4") <> "" Then
MsgBox "Person Reporting name mispelled"
Exit Sub
End If
End If
End If

MsgBox (mySourceWkbkName2)

End Sub

 

[Gary Keramidas]

you can give this a try

Sub Test()
Dim mySourceWkbkName2 As String
If Range("F4") = "" Then
MsgBox "Enter Person Reporting in Cell F4"
Exit Sub
ElseIf Range("F4") = "Jay" Then
mySourceWkbkName2 = "F:\files\ProjTimeTracking.xls"
ElseIf Range("F4") = "Dave" Then
mySourceWkbkName2 = "H:\FAC\DaveSipes\DavProjTimeTracking.xls"
Else
MsgBox "Person Reporting name mispelled"
Exit Sub
End If
MsgBox (mySourceWkbkName2)

End Sub

 

[Don Guillett]

Try this idea. It could even be a worksheet_change event requiring no
action.

Sub selectcasef4()
Select Case UCase(Range("f4"))
Case "DAVE": x = "Dave"
Case "BILL": x = "Bill"
Case Else
MsgBox "No name": Exit Sub
End Select
MsgBox x
End Sub

 

[Jim Thomlinson]

Give this a whirl... I was not too sure if you wanted to open the books or
not... I made guess...

Sub Test()
dim mySourceWkbkName2 as workbook

Select Case Range("F4").Value
Case ""
MsgBox "Enter Person Reporting in Cell F4"
Case "Jay"
Set mySourceWkbkName2 =
Workbooks.Open("F:\files\ProjTimeTracking.xls")
Case "Dave"
Set mySourceWkbkName2 =
Workbooks.Open("H:\FAC\DaveSipes\DavProjTimeTracking.xls")
Case Else
MsgBox "Person Reporting name mispelled"
End Select

MsgBox (mySourceWkbkName2.Name)

End Sub

 

[Jay]

Thanks for all the feedback. It looks like any of these ideas will work well.
Thanks for the help!

 

[Don Guillett]

Glad to help