VBA Message Box if Cell Blank

srj1359

New Member
Joined
Mar 5, 2015
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
Hello there!

Can someone please help me create a VBA code for a message box to appear if cell O16 has been left blank?

This is part of a larger macro to transfer data from the tab "Pricing Sheet" onto a tab called "Quote Summary" so I am wanting to put this coding at the very beginning and then if cell O16 is blank, the macro stops, and if it is not blank, it can proceed and copy all data as it is supposed to.

Thank you in advance!!

Here is what I have right now:

'message box for blank start date
With Sheets("Pricing Sheet")
If .Range("O16") = "" Then
.Select
.Range("O16").Select
MsgBox "Please enter the start date in cell O16"
Cancel = True
End If
End With
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe something like
VBA Code:
With Sheets("Pricing Sheet")
If .Range("O16") = "" Then
MsgBox "Please enter the start date in cell O16"
Sheets("Pricing Sheet").Activate
.Range("O16").Select
End If
End With
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Typo
VBA Code:
Sub MM1()
With Sheets("Pricing Sheet")
    If .Range("O16") = "" Then
        MsgBox "Please enter the start date in cell O16"
        .Activate
        .Range("O16").Select
    End If
End With
End Sub
 

srj1359

New Member
Joined
Mar 5, 2015
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
@Michael M thank you very much for your quick reply!

The message box is appearing as it should, but it's not halting the entire VBA at that point if the cell is blank. Is that possible to do?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, sorry...
But keep in mind, if you have any disableevents = false OR sceenupdating =false they need to be returned to TRUE before the Exit Sub line
VBA Code:
Sub MM1()
With Sheets("Pricing Sheet")
    If .Range("O16") = "" Then
        MsgBox "Please enter the start date in cell O16"
        .Activate
        .Range("O16").Select
       Exit Sub
    End If
End With
End Sub
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad to help... (y) (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,139
Messages
5,628,930
Members
416,354
Latest member
JojoMaque

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