PDA

View Full Version : Elseif ?



YellowLabPro
05-30-2007, 09:21 AM
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?

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")


Thanks,
YLP

mdmackillop
05-30-2007, 09:51 AM
Hi Doug

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

YellowLabPro
05-30-2007, 10:09 AM
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
05-30-2007, 10:12 AM
Oops
I missed one line of code....
Let me re-work this

YellowLabPro
05-30-2007, 10:14 AM
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
05-30-2007, 10:38 AM
How do you identify the "correct" workbook?

YellowLabPro
05-30-2007, 10:41 AM
By answering Yes to the vbYesNoCancel

Bob Phillips
05-30-2007, 10:45 AM
Give them a dropdown list of the books available and activate the selected one.

YellowLabPro
05-30-2007, 11:05 AM
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
05-30-2007, 11:14 AM
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

Bob Phillips
05-30-2007, 11:29 AM
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
05-30-2007, 12:11 PM
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
05-30-2007, 08:10 PM
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
06-01-2007, 08:04 AM
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
06-01-2007, 09:56 AM
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.