Thread: Multiple VBA IF/Then Statements
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Multiple VBA IF/Then Statements
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" wrote in
message ...

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
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 Sub
--------------------


Thanks in advance.


--
Emm100


------------------------------------------------------------------------
Emm100's Profile:
http://www.excelforum.com/member.php...o&userid=23236
View this thread: http://www.excelforum.com/showthread...hreadid=383549



Reply With QuoteReply With Quote