Elseif ?

[YellowLabPro]

I want to make sure I have correct workbook activated.
To do this I am using a Msgbox.
If the answer is No, then how do I tell it to stop so I can select the correct workbook?
And if Yes then the code proceeds, and if cancel, then cancel the code?
[vba]
If vbNo Then
MsgBox "HALT! You Must Activate the Correct" & Chr(10) & "Workbook For the Program to Run Correctly!", vbCritical
ElseIf vbYes Then
MsgBox "Procede"
Else: MsgBox "Cancel"
End If
Set Wbs = ActiveWorkbook
Set Wbt = Workbooks("Master PO.xls")
Set Wss = Wbs.ActiveSheet
Set Wst = Wbt.Worksheets("FF")
[/vba]

Thanks,
YLP

[mdmackillop]

Hi Doug
[VBA]
Sub test()
Dim Response As Integer
Response = MsgBox("HALT! You Must Activate the Correct" & Chr(10) _
& "Workbook For the Program to Run Correctly!", vbYesNo + vbCritical)
If Response = vbYes Then
MsgBox "Proceed"
Else
MsgBox "Cancel"
End If
End Sub

[/VBA]

[YellowLabPro]

Hi Malcolm,
No matter what I enter w/ your code I get the Halt. What I prefer to do is, If Yes then let the code run, If No then start over at the top and force me to select another workbook, and If Cancel then do nothing, exit the sub.
I think my code does it all except the responses don't force the correct action. Or am I dreaming.....?

[YellowLabPro]

Oops
I missed one line of code....
Let me re-work this

[YellowLabPro]

I redid w/ your code: I missed the response line and so it acts better now. But still would like to have slightly different method...

I would like to ask first if it is the correct workbook.
If it is, Proceed.
If Not: Warning, Stop, pick the correct one
Cancel: Cancel

[mdmackillop]

How do you identify the "correct" workbook?

[YellowLabPro]

By answering Yes to the vbYesNoCancel

[Bob Phillips]

Give them a dropdown list of the books available and activate the selected one.

[YellowLabPro]

I don't think I can do that.
These are workbooks coming in from different companies all the time, so adding them would be more trouble than its worth.
The other thing too is that I have the Target sheet open and I end up on this and forget to switch back to the Source worksheet. This just helps me keep track of where I am .

[mdmackillop]

[vba]Sub test()
Dim Response As Integer
TryAgain:
'Your code

Response = MsgBox("HALT! You Must Activate the Correct" & Chr(10) _
& "Workbook For the Program to Run Correctly!", vbYesNoCancel + vbCritical)
Select Case Response
Case vbNo
GoTo TryAgain
Case vbCancel
Exit Sub
End Select
'continue code

End Sub

[/vba]

[Bob Phillips]

I don't think I can do that.
These are workbooks coming in from different companies all the time, so adding them would be more trouble than its worth.
The other thing too is that I have the Target sheet open and I end up on this and forget to switch back to the Source worksheet. This just helps me keep track of where I am .

Store them all in a particular directory, and then just load the dropdown from there.

[YellowLabPro]

Malcolm,
Getting closer. Your code, which I edited slightly provides the warnings. But after it receives the "No" and goes to TryAgain, we need to pause the code somehow to let me select the correct sheet.
Can you help w/ this one?

thx,

Doug

[malik641]

I would go with Bob's suggestion on this. You could store all these workbooks into a particular directory on a server and use that list of xls files for the drop-down. I wouldn't be too hard to implement.

EDIT: You can also use that list against the active workbook to determine if your procedure will run in the first place.

[Norie]

Doug

Why activate in the first place?

Generally it's not needed to activate/select anything, eg workbooks, worksheets etc, to work with them.

[YellowLabPro]

Because there is a standard source workbook that information gets copied to, a masterworkbook if you will.
Inbetween copying data to the masterworkbook, I check on it to ensure it performed this accurately.
The code to copy the data is working on the activesheet, rather than rely on me remembering to switch back, I am looking for a way to set a certain workbook as the source workbook. The masterworkbook is named in the code, but since the source workbooks change names every single time, it is error prone to rely on the activesheet.
That is why....

I had to postpone work on this for a few days due to other things, Mdmackillop has been consulting me on this and I have an idea of collecting the name via an inputbox and then pass the name along to the variable... but have not had time to test...

edit:
And Xld has provided a suggestion that I am going to try out too, which will not require switching.... but I think will be difficult to manage.... but I will see how it does work.