Disable message box in macro

[Shadkng]

Hi, the below code displays a message box on sheet "TABLE PROD" when that sheet is selected. The code is in "This Workbook" module. I would like to disable the message box (or have the code answer "yes") when the 2nd macro (TABLE PROD COPYROW) below is run. This macro is one of many macros called in a process and the message box interrupts it. I only need the message box after the group of macros are run. I have been poking around online for a solution, but no luck. One common suggestion "Application.DisplayAlerts = False" doesn't seem to work. Any help is appreciated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If IsEmpty(Sheets("QUOTE").Range("L9").Value) = False Then
    On Error Resume Next
    If Sh.name = "TABLE PROD" Then
        MsgBox "JIM SEE NOTES:  " & Worksheets("quote").Range("L9")
    End If
    End If
End Sub

********************

Sub TABLE_PROD_COPYROW()
    Application.ScreenUpdating = False
    Sheets("TABLE PROD").Activate
    Range("A38:Y5000").ClearContents
    Range("b2:b2").ClearContents
    Range("AA37:AB37").Font.Size = 28 '5/31/20 Steve added this line to increase the size of the barcode
    Rows("37:37").Copy
    Rows("37:" & Range("A1").Value).Select
    Selection.PasteSpecial xlFormulas
    Selection.PasteSpecial xlFormats
    'Rows("37:" & Range("A1").Value).RowHeight = 36 '5/31/20 May use this line to increase row height due to barcode spacing
    Cells.EntireColumn.AutoFit
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
        Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
        Next i
        Application.CutCopyMode = False
        ActiveSheet.Columns("b").ColumnWidth = 14
        ActiveSheet.Columns("c").ColumnWidth = 20
        ActiveSheet.Columns("aa:ab").AutoFit
        ActiveSheet.Columns("ab").ColumnWidth = 24
        ActiveSheet.Columns("ac:ad").ColumnWidth = 12
        ActiveSheet.Columns("aE:af").ColumnWidth = 20
        Range("A33").Select
        COUNT_CRITERIA
        Call TableProdColumnHide  '1/11/20 STEVE ADDED
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
 End Sub

 

[Zot]

Why not just remark out that msgbox if not required?

 

[johnnyL]

Or that entire sub?

 

[StephenCrump]

I suggest you simply don't activate the sheet. It's rarely necessary to use .Activate, .Select, Selection, .ActiveSheet etc, and your code will be less error prone if you refer to sheets and cells directly.

So I'd rewrite:

Sub TABLE_PROD_COPYROW()
    
    Application.ScreenUpdating = False
    
    With Sheets("TABLE PROD")
        .Range("A38:Y5000").ClearContents
        .Range("B2").ClearContents
        '...
        
    End With

Other possible alternatives:
- Set Application.EnableEvents = False temporarily while you're doing stuff to Sheets("TABLE PROD")
- Show the message box subject to an IF condition based on a public variable boolean flag which your code could turn on/off.

 

[Shadkng]

I suggest you simply don't activate the sheet. It's rarely necessary to use .Activate, .Select, Selection, .ActiveSheet etc, and your code will be less error prone if you refer to sheets and cells directly.

So I'd rewrite:

Sub TABLE_PROD_COPYROW()
   
    Application.ScreenUpdating = False
   
    With Sheets("TABLE PROD")
        .Range("A38:Y5000").ClearContents
        .Range("B2").ClearContents
        '...
       
    End With

Other possible alternatives:
- SetApplication.EnableEvents = False temporarily while you're doing stuff to Sheets("TABLE PROD")
- Show the message box subject to an IF condition based on a public variable boolean flag which your code could turn on/off.

The "application

I suggest you simply don't activate the sheet. It's rarely necessary to use .Activate, .Select, Selection, .ActiveSheet etc, and your code will be less error prone if you refer to sheets and cells directly.

So I'd rewrite:

Sub TABLE_PROD_COPYROW()
   
    Application.ScreenUpdating = False
   
    With Sheets("TABLE PROD")
        .Range("A38:Y5000").ClearContents
        .Range("B2").ClearContents
        '...
       
    End With

Other possible alternatives:
- Set Application.EnableEvents = False temporarily while you're doing stuff to Sheets("TABLE PROD")
- Show the message box subject to an IF condition based on a public variable boolean flag which your code could turn on/off.

The application events seems to work. As far as your suggestion not to use "activate" sheet, so I can achieve the same result from the macro by using with sheets? I have dozens of programs that work in a similar way using activate sheets. I will look to modify them if there would be no unexpected results.

To answer the other question about remarking out the message box...I need the message box to function, but only after a series of macros run which activate that sheet.

Thanks

 

[MARK858]

One common suggestion "Application.DisplayAlerts = False" doesn't seem to work.

Where did you see this for not showing a message box DisplayAlerts refers to alerts i.e. warnings you that you haven't saved a workbook before closing it or that you are going to delete a large amount of data etc. not message boxes or anything else that you have designed.

 

[Shadkng]

Where did you see this for not showing a message box DisplayAlerts refers to alerts i.e. warnings you that you haven't saved a workbook before closing it or that you are going to delete a large amount of data etc. not message boxes or anything else that you have designed.

I searched online but I may have misunderstood it's function, but I tried it anyway.

 

[StephenCrump]

As far as your suggestion not to use "activate" sheet, so I can achieve the same result from the macro by using with sheets? I have dozens of programs that work in a similar way using activate sheets. I will look to modify them if there would be no unexpected results.

It's not necessarily about using a With statement. Rather it's about addressing objects directly (rather than relying on ActiveWorkbook, ActiveSheet, ActiveCell, Selection) which makes for more efficient and succinct code

Compare:

'Macro recorder code (flabby!)
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.Value = "Hello"
Sheets("Sheet1").Select
    
'with the direct:
Sheets("Sheet2").Range("A1").Value = "Hello"

Using a With statement makes for more efficient and succinct code if you're doing several things with the same object, e.g.

With Sheets("Sheet2").Range("A1")
    .Value = "Hello"
    .Font.Size = 16
    .Font.Bold = True
    .Font.Underline = True
End With

'rather than
Sheets("Sheet2").Range("A1").Value = "Hello"
Sheets("Sheet2").Range("A1").Font.Size = 16
Sheets("Sheet2").Range("A1").Font.Bold = True
Sheets("Sheet2").Range("A1").Font.Underline = True