|
Multiple VBA IF/Then Statements
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 ------------------- Thanks in advance -- Emm10 ----------------------------------------------------------------------- Emm100's Profile: http://www.excelforum.com/member.php...fo&userid=2323 View this thread: http://www.excelforum.com/showthread.php?threadid=38354 |
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 |
Multiple VBA IF/Then Statements
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 -- Emm100 ------------------------------------------------------------------------ Emm100's Profile: http://www.excelforum.com/member.php...o&userid=23236 View this thread: http://www.excelforum.com/showthread...hreadid=383549 |
Multiple VBA IF/Then Statements
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) -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=383549 |
Multiple VBA IF/Then Statements
I missed the fact, you never assigned a value to result.
you should have result = MsgBox ("WARNING! If 'Update . . . -- Regards, Tom Ogilvy "Emm100" wrote in message ... 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 -- Emm100 ------------------------------------------------------------------------ Emm100's Profile: http://www.excelforum.com/member.php...o&userid=23236 View this thread: http://www.excelforum.com/showthread...hreadid=383549 |
| All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com