Combining Msg Box and Data

  • Thread starter Thread starter Full Monty
  • Start date Start date
F

Full Monty

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
 
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)
 
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
 
Back
Top