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

[Kjohno]

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

 

[Joe4]

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:

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]

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!

 

[Joe4]

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.