CODEWITHSUNDEEP
vbaexcel
Sarath kumar
Sarath kumar

Reputation: 35

VBA code to popup msgbox and return to the previous value

I need a VBA code that pops up error/warning message when the cell value is exceeds more than 100.

For example, if value entered in B2, F2 cells updates automatically. Higher the B2 value will lead F2 value cross 100 and sheet becomes error.

So, I need to give message here to uses, when F2 goes beyond 100 and B2 should return to last value.

My current code is very basic. Please help me to get thru this.

If Sheets("200L_50°C").Range("L2").Value < 100 Then
MsgBox "No further pressure drop at higher operating temperatures",   vbOKOnly, "High temperature"

Update, this is the code I put in my worksheet. I get error on the first line:

Public G1Value As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("G1") Then
    If Range("L2") > 100 Then
        MsgBox "Error. Invalid value for G1"
        Range("G1") = G1Value
    Else: End If
Else: End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("G1") Then
    G1Value = Range("G1")
Else: End If
End Sub

Upvotes: 0

Views: 1159

Answers (2)

takanuva15
takanuva15

Reputation: 1688

Hold up, I think I have a better understanding of your question now. When a user puts something in B2, if that change causes cell F2 to go higher than 100, then you want to output an error message and return cell B2 to the value that it was. Here's how you can do that easily:

Go to the view code of the worksheet you are on by right-clicking the worksheet tab name:

view code of worksheet

Then add in this code (if a duplicate method exists, then comment out the old code or replace it if it's unimportant).

Public B2Value As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B2") Then
        If Range("L2") > 100 Then
            MsgBox "Error. Invalid value for B2"
            Range("B2") = B2Value
        Else: End If
    Else: End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target = Range("B2") Then
        B2Value = Range("B2")
    Else: End If
End Sub

Upvotes: 1

takanuva15
takanuva15

Reputation: 1688

I'm not sure if I get your question, but it seems you could use data validation in cell B2 to prevent users from inputting values higher than 100. Click on cell B2, go to the "Data" tab, and click on "Data Validation". Modify the conditions like the image below, and on the "Error Alert" tab, put in the message you want them to see when they try to enter a value higher than 100.

data validation picture error alert msg picture

Upvotes: 1

Related Questions