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

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

--


Gary


"Jay" wrote in message
...
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

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
--
Don Guillett
SalesAid Software

"Jay" wrote in message
...
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

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\DavProjTimeTracki ng.xls")
Case Else
MsgBox "Person Reporting name mispelled"
End Select

MsgBox (mySourceWkbkName2.Name)

End Sub
--
HTH...

Jim Thomlinson


"Jay" wrote:

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

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

"Don Guillett" wrote:

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
--
Don Guillett
SalesAid Software

"Jay" wrote in message
...
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

Glad to help
--
Don Guillett
SalesAid Software

"Jay" wrote in message
...
Thanks for all the feedback. It looks like any of these ideas will work
well.
Thanks for the help!

"Don Guillett" wrote:

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
--
Don Guillett
SalesAid Software

"Jay" wrote in message
...
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