Forum Discussion

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

Excel_World_Champion
Jul 06, 2019
Hey Greg Bonaparte

Its probably because you have not added an "End With" statement

Excel_World_Champion

Brass Contributor

Jul 06, 2019

HeyGreg Bonaparte

Just use an Else statement and indent what you have written that must happen when "=vbNo"

.......

Call ComboMacroLessThan4k

Else

ActiveWindow.ScrollRow = 624

........

If that does not work, just use an ElseIf statement

........

Call Combo_MacroLessThan4k

ElseIf If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbNo

ActiveWindow.ScrollRow = 624

........

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