Disable message box in macro

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
269
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.

VBA Code:
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

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

VBA Code:
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,572
Office Version
  1. 2016
Platform
  1. Windows
Why not just remark out that msgbox if not required?
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Active Member
Joined
Oct 11, 2018
Messages
269

ADVERTISEMENT

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:
VBA Code:
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:
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
One common suggestion "Application.DisplayAlerts = False" doesn't seem to work.
Where did you see this for not showing a message box :oops: 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

Active Member
Joined
Oct 11, 2018
Messages
269
Where did you see this for not showing a message box :oops: 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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
'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.

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

Forum statistics

Threads
1,141,315
Messages
5,705,699
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top