Excel 97 - Have code to get values from a closed workbook.

Once completed I want a Message Box to appear asking if you want t
view the data. If you select Yes it should open up a hidden page. I
you slect No it should move to another page.

I have worked with Msg Boxes a little but am not sure what I am doin
wrong! Keep getting error messages. Here's the code I currently have.

Sub Importmonday()
GetValuesFromAClosedWorkbook "C:\Data", "Day 1.xls", _
"Sheet1", "AX100:BR1977"
End Sub
Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
Answer = MsgBox("Do you want to view Rosters?", _
vbYesNo + vbQuestion, "Rosters Imported!")
If Answer = vbYes Then
Sheets("Today").Select
Exit Sub
If Answer = vbYes Then
Sheets("Timeline").Visible = True
Sheets("Timeline").Select
End Sub

While I am fairly new to VBA, I know a little and can't figure out wha
to correct!

Thanks

--
Message posted from http://www.ExcelForum.com

Monty My Man,

Your If logic is a bit skewed. You don't seem to like End Ifs. And you test
twice for vbYes!

Replace this

If Answer = vbYes Then
Sheets("Today").Select
Exit Sub
If Answer = vbYes Then
Sheets("Timeline").Visible = True
Sheets("Timeline").Select

with this

If Answer = vbYes Then
Sheets("Today").Select
Exit Sub
Else 'it must be no
Sheets("Timeline").Visible = True
Sheets("Timeline").Select
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Full Monty " wrote in message
...
Excel 97 - Have code to get values from a closed workbook.

Once completed I want a Message Box to appear asking if you want to
view the data. If you select Yes it should open up a hidden page. If
you slect No it should move to another page.

I have worked with Msg Boxes a little but am not sure what I am doing
wrong! Keep getting error messages. Here's the code I currently have.

Sub Importmonday()
GetValuesFromAClosedWorkbook "C:\Data", "Day 1.xls", _
"Sheet1", "AX100:BR1977"
End Sub
Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
Value = .Value
End With
Answer = MsgBox("Do you want to view Rosters?", _
vbYesNo + vbQuestion, "Rosters Imported!")
If Answer = vbYes Then
Sheets("Today").Select
Exit Sub
If Answer = vbYes Then
Sheets("Timeline").Visible = True
Sheets("Timeline").Select
End Sub

While I am fairly new to VBA, I know a little and can't figure out what
to correct!

Thanks!


---
Message posted from http://www.ExcelForum.com/

Bob, once again you're my idol!

This was my first attempt at a real IF/Then type statement in VBA! Al
of the other Msg Boxes I create just use the Exit Sub if they choose N
or Cancel.

But I know I was close. I had the "Else" in there originally just no
the End If! But when I kept getting error after error, I tinkered wit
it too much I guess!

Man do I feel low right now! I just ran the macro again before
entered the correct code and the error message I was got pretty muc
told me there was no End If!

Oh, the second test you saw was a typo. In my code it says No.

I do have one more question, but I will create another post for it
This one is more out of curiosity than need!

Thanks again my friend! I am going to owe you big time

--
Message posted from http://www.ExcelForum.com