Forum Discussion

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Jul 06, 2019
Solved

How can I tell macro to perform operations below "= vbNo Then" ?

Sub OPTIMIZE() If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbYes Then Call Combo_MacroLessThan4k "= vbNo T...
excel
Macros and VBA
Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Jul 06, 2019

Excel_World_Champion 

Hello, Thank you for your assistence. I attempted your suggestion with the following code, adding a "cancel" option. However I get compile errors. Please examine and correct:

 

Sub OPTIMIZE()
If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24") = vbYes Then

Call Combo_MacroLessThan4k

Else

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents
End Sub

Excel_World_Champion's avatar
Excel_World_Champion
Brass Contributor
Jul 06, 2019

Hey Greg Bonaparte 

 

I think all you have got to do now is add and "End If" on a line before the "End Sub" statement

 

  • Greg Bonaparte's avatar
    Greg Bonaparte
    Iron Contributor
    Jul 06, 2019

    Thank you than Helped. But "Cancel" and "No" appear to both be running the "Call"  macro. How can I make "Cancel simply abort?

     

    Excel_World_Champion 

    • Excel_World_Champion's avatar
      Excel_World_Champion
      Brass Contributor
      Jul 06, 2019

      Hey Greg Bonaparte 

       

      So, what you have to do is to make an "ElseIf" statement for vbNo, and and "Else" statement for the when you press cancel. 

       

      Here is the code I used (I just used "a" as a variable for the Messagebox):

       

      Sub OPTIMIZE()

      a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

      If a = vbYes Then
      Call Combo_MacroLessThan4k

      ElseIf a = vbNo Then

      ActiveWindow.ScrollRow = 624
      Range("V13:V14").Select
      Selection.Copy
      Range("V640:V641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("W13:W14").Select
      Selection.Copy
      Range("W640:W641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("X13:X14").Select
      Selection.Copy
      Range("X640:X641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("Y13:Y14").Select
      Selection.Copy
      Range("Y640:Y641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("Z13:Z14").Select
      Selection.Copy
      Range("Z640:Z641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("AA13:AA14").Select
      Selection.Copy
      Range("AA640:AA641").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("V13:Z14").Select
      Selection.ClearContents

      Else
      Exit Sub
      End If
      End Sub

       

       

      • Greg Bonaparte's avatar
        Greg Bonaparte
        Iron Contributor
        Jul 06, 2019

        Thank you, Your code ran perfectly. One final question: Why do I get and "Else" Compile error when I place the following code just above your last "Else":

         

        Sub OPTIMIZE()

        a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

        If a = vbYes Then
        Call Combo_MacroLessThan4k

        ElseIf a = vbNo Then

        ActiveWindow.ScrollRow = 624
        Range("V13:V14").Select
        Selection.Copy
        Range("V640:V641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("W13:W14").Select
        Selection.Copy
        Range("W640:W641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("X13:X14").Select
        Selection.Copy
        Range("X640:X641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("Y13:Y14").Select
        Selection.Copy
        Range("Y640:Y641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("Z13:Z14").Select
        Selection.Copy
        Range("Z640:Z641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("AA13:AA14").Select
        Selection.Copy
        Range("AA640:AA641").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("V13:Z14").Select
        Selection.ClearContents

        Application.Calculation = xlAutomatic


        ActiveSheet.Range("$BB$21:$BD$629").AutoFilter Field:=1

        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "aw30:aw629"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B30:BA629")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

         


        Else
        Exit Sub
        End If
        End Sub

        Excel_World_Champion 

Resources