Prevent or disable coded pop-up message if cell is blank or 0

Kjohno

New Member
Joined
Jun 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I've created various sheets all of which have their own finalised percentage - as part of this, I was able to create a pop-up message that when the percentage exceeds 100% (or in the case '1' as I couldn't establish how to get it to recognise 100% on the VBA) a pop-up would be generated notifying them that they have exceeded 100%. My issue is however, if the cell is blank (or not yet typed on) if you select ANY cell on the sheet, but don't type anything, the pop-up message comes up. As soon as the cell is greater than blank or 0, the pop-up message doesn't return until after 100% has been exceeded.

Is there anyway I can amend the below code so that it disables the pop up if the percentage is 0 or blank but re-enables once it is over 100%?

The coding is as follows;

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Me.Range("AC9")
If Target.Value > 1 Then
MsgBox "You exceeded 100%, please leave a note confirming your reasoning"
End If
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,070
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Firstly, in "Worksheet_Change" event procedures, you do not set the "Target" range. That is already pre-defined as the range that was just updated that triggers the code to run.

So your code should look something like:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("AC9").Value > 1 Then
    MsgBox "You exceeded 100%, please leave a note confirming your reasoning"
End If

End Sub
The Message Box will only appear if the value in AC9 is greater than 1, so it should not pop up if AC9 is 0 or blank.
 

Kjohno

New Member
Joined
Jun 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Joe,

Thanks :). Unfortunately the issue still prevails, if the cell becomes blank or the person using the sheet selects '0' as the scoring (creating a percentage of 0) the pop-up message appears. If data is applied and it too exceeds 100, the pop-up message appears again and if the person using the sheet then cleared all of the contents (not including the background formulas to create the percentage etc) the pop-up message appears yet again.

I have however worked out that the IFERROR formulas i've used to ignore 0 and change it to blank was the actual cause of this so i've inadvertently fixed it by just allowing the cells to show 0 rather than blank!

Thanks for your help and pointing out the flaws in my previous code it's greatly appreciated!
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,070
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

It sounds like there may be some details or other cells involved that aren't included in the specifics of the original question that may be coming into play here, as the message box should NEVER appear if the value in AC9 is less than 1, but as long as you got it working the way you need, I guess that is what is important.
 

Forum statistics

Threads
1,141,613
Messages
5,707,394
Members
421,508
Latest member
Jalayne

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