Multiple VBA IF/Then Statements

E

Emm100

Hi

I'm still getting to grips with VBA and would be grateful for some hel
with some code i'm working on. My range of statements and functions i
still rather limited but what i am attempting to do here is this -

If the user selects YES to the "WARNING!" msgbox ALL the code will b
executed. If they select NO to the "WARNING!" msgbox the code will ski
to the following line and then the subroutine will end:


Code
-------------------
Jan1Entryform.Sho
-------------------


I have no doubt i'm using the wrong function for this and confusing VB
with the number of If's and End If's. I'm pretty sure i should be usin
GOTO but i've tried that and cannot seem to get it working. Any help i
regard to firstly which function i should be using and secondly how bes
to use it would be GREATLY appriciated. I'm starting to think myself i
circles with this one.





Code
-------------------
Private Sub CmdJan_Click()
Dim X As Workbook
Dim Range_40DE As String
MsgBox ("WARNING! If 'Update All' is selected all automatically gathered data will be replaced with the most recent full month data. Are you sure you wish to continue?"), vbExclamation + vbYesNo, "WARNING! POSSIBLE DATA LOSS!"
If result = vbYes Then
On Error Resume Next
Set X = Workbooks("Consolidated performance report both sites")
If Err = 0 Then
MsgBox "You already have the dashboard open. Attempting to run this command while the dashboard is already open will corrupt your file. Close the dashboard and retry", vbCritical, "Unable to Proceed"
End
Else
End If
Workbooks.Open Filename:="M:\Supply_Chain\Supply Chain Reporting\Consolidated performance report both sites", ReadOnly:=True
Application.Run "'Consolidated performance report both sites.xls'!OEtab"
Workbooks("consolidated performance report both sites").Sheets("oe").Range("C4:D4").Select
Workbooks("consolidated performance report both sites").Sheets("oe").Range("C4:D4").Value = " "
Workbooks("consolidated performance report both sites").Sheets("oe").Range("f4:k4").Select
Workbooks("consolidated performance report both sites").Sheets("oe").Range("f4:k4").Value = "All"
Workbooks("Direct KPI Worksheets").Sheets("overview").Select
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f7").Value = Workbooks("consolidated performance report both sites").Sheets("oe").Range("R59").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f13") = Workbooks("consolidated performance report both sites").Sheets("oe").Range("R67").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f22") = Workbooks("consolidated performance report both sites").Sheets("oe").Range("R53").Value
Workbooks("consolidated performance report both sites").Close False
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor (YR)").Sheets("Bowling Chart References").Range("B2").Value
Workbooks("Direct KPI Worksheets").Worksheets("overview").Range("aa6:al6").Value = Workbooks("40DE Suppliers Rollout Monitor (SH)").Sheets("Bowling Chart").Range(Range_40DE).Value
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor (YR)").Sheets("Bowling Chart References").Range("B3").Value
Workbooks("Direct KPI Worksheets").Worksheets("overview").Range("AA6:AL6").Value = Workbooks("40DE Suppliers Rollout Monitor (SH)").Sheets("Bowling Chart").Range(Range_40DE).Value
Workbooks("40DE Suppliers Rollout Monitor (YR)").Close False
Else
Jan1Entryform.Show
End If
End Su
 
T

Tom Ogilvy

Private Sub CmdJan_Click()
Dim X As Workbook
Dim Range_40DE As String
MsgBox ("WARNING! If 'Update All' is selected all automatically gathered
data will be replaced with the most recent full month data. Are you sure
you wish to continue?"), vbExclamation + vbYesNo, "WARNING! POSSIBLE DATA
LOSS!"
If result = vbYes Then
On Error Resume Next
Set X = Workbooks("Consolidated performance report both sites")
If Err = 0 Then
MsgBox "You already have the dashboard open. Attempting to run
this command while the dashboard is already open will corrupt your file.
Close the dashboard and retry", vbCritical, "Unable to Proceed"
Exit Sub
End If
Workbooks.Open Filename:="M:\Supply_Chain\Supply Chain
Reporting\Consolidated performance report both sites", ReadOnly:=True
Application.Run "'Consolidated performance report both sites.xls'!OEtab"
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("C4:D4").Select
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("C4:D4").Value = " "
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("f4:k4").Select
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("f4:k4").Value = "All"
Workbooks("Direct KPI Worksheets").Sheets("overview").Select
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f7").Value =
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R59").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f13") =
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R67").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f22") =
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R53").Value
Workbooks("consolidated performance report both sites").Close False
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers
Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor
(YR)").Sheets("Bowling Chart References").Range("B2").Value
Workbooks("Direct KPI
Worksheets").Worksheets("overview").Range("aa6:al6").Value = Workbooks("40DE
Suppliers Rollout Monitor (SH)").Sheets("Bowling
Chart").Range(Range_40DE).Value
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers
Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor
(YR)").Sheets("Bowling Chart References").Range("B3").Value
Workbooks("Direct KPI
Worksheets").Worksheets("overview").Range("AA6:AL6").Value = Workbooks("40DE
Suppliers Rollout Monitor (SH)").Sheets("Bowling
Chart").Range(Range_40DE).Value
Workbooks("40DE Suppliers Rollout Monitor (YR)").Close False
Else
Jan1Entryform.Show
End If
End Sub

--
Regards,
Tom Ogilvy


Emm100 said:
Hi

I'm still getting to grips with VBA and would be grateful for some help
with some code i'm working on. My range of statements and functions is
still rather limited but what i am attempting to do here is this -

If the user selects YES to the "WARNING!" msgbox ALL the code will be
executed. If they select NO to the "WARNING!" msgbox the code will skip
to the following line and then the subroutine will end:


Code:
--------------------
Jan1Entryform.Show
--------------------


I have no doubt i'm using the wrong function for this and confusing VBA
with the number of If's and End If's. I'm pretty sure i should be using
GOTO but i've tried that and cannot seem to get it working. Any help in
regard to firstly which function i should be using and secondly how best
to use it would be GREATLY appriciated. I'm starting to think myself in
circles with this one.





Code:
--------------------
Private Sub CmdJan_Click()
Dim X As Workbook
Dim Range_40DE As String
MsgBox ("WARNING! If 'Update All' is selected all automatically gathered
Click to expand...
data will be replaced with the most recent full month data. Are you sure
you wish to continue?"), vbExclamation + vbYesNo, "WARNING! POSSIBLE DATA
LOSS!"
If result = vbYes Then
On Error Resume Next
Set X = Workbooks("Consolidated performance report both sites")
If Err = 0 Then
MsgBox "You already have the dashboard open. Attempting to run this
Click to expand...
command while the dashboard is already open will corrupt your file. Close
the dashboard and retry", vbCritical, "Unable to Proceed"
End
Else
End If
Workbooks.Open Filename:="M:\Supply_Chain\Supply Chain
Click to expand...
Reporting\Consolidated performance report both sites", ReadOnly:=True
Application.Run "'Consolidated performance report both sites.xls'!OEtab"
Workbooks("consolidated performance report both sites").Sheets("oe").Range("C4:D4").Select
Workbooks("consolidated performance report both
Click to expand...
sites").Sheets("oe").Range("C4:D4").Value = " "
Workbooks("consolidated performance report both sites").Sheets("oe").Range("f4:k4").Select
Workbooks("consolidated performance report both
Click to expand...
sites").Sheets("oe").Range("f4:k4").Value = "All"
Workbooks("Direct KPI Worksheets").Sheets("overview").Select
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f7").Value
Click to expand...
= Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R59").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f13") =
Click to expand...
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R67").Value
Workbooks("Direct KPI Worksheets").Sheets("overview").Range("f22") =
Click to expand...
Workbooks("consolidated performance report both
sites").Sheets("oe").Range("R53").Value
Workbooks("consolidated performance report both sites").Close False
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers
Click to expand...
Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor
Click to expand...
(YR)").Sheets("Bowling Chart References").Range("B2").Value
Workbooks("Direct KPI
Click to expand...
Worksheets").Worksheets("overview").Range("aa6:al6").Value = Workbooks("40DE
Suppliers Rollout Monitor (SH)").Sheets("Bowling
Chart").Range(Range_40DE).Value
Workbooks.Open Filename:="M:\Supply_Chain\40 Day Engine\40DE Suppliers
Click to expand...
Rollout Monitor (YR)", ReadOnly:=True
Range_40DE = Workbooks("40DE Suppliers Rollout Monitor
Click to expand...
(YR)").Sheets("Bowling Chart References").Range("B3").Value
Workbooks("Direct KPI
Click to expand...
Worksheets").Worksheets("overview").Range("AA6:AL6").Value = Workbooks("40DE
Suppliers Rollout Monitor (SH)").Sheets("Bowling
Chart").Range(Range_40DE).Value
 
E

Emm100

Hi

Many thanks for the response.

Unfortunately the code you've provided has the same problems mine did.
Both Vbyes and Vbno result in the subroutine skipping to the
Jan1Entryform.Show line rather than VByes conntinuing through the
routine.

I think i may have to use a GOTO function but this seems to have the
same result........any other ideas guys?

Emm100
 
K

Kaak

You should use:

Msg = "WARNING! If 'Update All' is selected all automatically gathered
data will be replaced with the most recent full month data. Are you
sure you wish to continue?"

result = MsgBox (Msg, vbYesNo)
 
T

Tom Ogilvy

I missed the fact, you never assigned a value to result.

you should have

result = MsgBox ("WARNING! If 'Update . . .
 

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

Similar Threads

If -Then in VBA Code 4
VBA to create series of workbooks need to add code to skip creating areport if no lines meet criteri 2
FWT Newsletter - Weekly - October 18, 2004 0
FWT Newsletter - Weekly - March 22, 2004 3
WTD: Nvidia Videocard for 4x AGP Slot 0
FWT Newsletter - Weekly - August 23, 2004 6
FWT Newsletter - Weekly - September 13, 2004 1
FWT Newsletter - Weekly - August 16, 2004 1

Top