major errors with macro code - VB question constantly doing "ELSE" part of if statement

A

amorrison2006

Hello

I have this part of my macro - No matter whether I click on yes to the
question or no it does not work........it is constanly running the
else macro whether or not you click "Yes" or "No"....

I've only just realised and it has caused me major problems.


If resp = MsgBox("Do you want to run macro1?", vbYesNo +
vbQuestion) = vbYes Then
Application.Run "'Workbook1 - Test.xls'!Macro1"
Else
Application.Run "'Workbook1 - Test.xls'!Macro2"
End If
Application.ScreenUpdating = True
If resp = MsgBox("Do you want to run macro3?", vbYesNo +
vbQuestion) = vbYes Then
Application.ScreenUpdating = False
Application.Run "'Workbook1 - Test.xls'!Macro3"
Else
Application.Run "'Workbook1 - Test.xls'!Macro4"
End If


In the above example it is always macros2 and 4 that are being
run.....

Please please please someone help,

I've spent so many hours working on this entire process for it to fail
on me......

thanks in advance for any help you may be able to provide,

Andrea
 
T

Tom Ogilvy

If MsgBox("Do you want to run macro1?", _
vbYesNo + vbQuestion) = vbYes Then
Application.Run "'Workbook1 - Test.xls'!Macro1"
Else
Application.Run "'Workbook1 - Test.xls'!Macro2"
End If

Application.ScreenUpdating = True
If MsgBox("Do you want to run macro3?", _
vbYesNo + vbQuestion) = vbYes Then
Application.ScreenUpdating = False
Application.Run "'Workbook1 - Test.xls'!Macro3"
Else
Application.Run "'Workbook1 - Test.xls'!Macro4"
End If
 
J

JE McGimpsey

You're comparing the variable 'resp' to a comparision between the result
of the MsgBox and vbYes. The latter comparison returns True/False, and
the former then is always False.

Try either:

If MsgBox("Do you want to run macro1?", _
vbYesNo + vbQuestion) = vbYes Then
Application.Run "Macro1"
Else
Application.Run "'Macro2"
End If

Or

Dim resp As Long
resp = MsgBox("Do you want to run macro1?", _
vbYesNo + vbQuestion)
If resp = vbYes Then
Application.Run "Macro1"
Else
Application.Run "'Macro2"
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top