• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code Popup Problem following Reference Cell

T

Tman

New Member
Hi all,
I am trying to insert a vba code to allow a pop up message to appear if a value is entered into a reference cell. So far I can make this happen using

Code: 
Private Sub Worksheet_Calculate()
If Range("G18").Value > 0 Then MsgBox "required message here"
End Sub

unfortunately once the reference cell (G18 in this instance) has a value the pop up appears every time anything additional is added anywhere in spreadsheet and if additional lines added above it it looses the reference.

How can I make it only activate once and stay pointing to the correct cell if lines are added or deleted?

Many thanks

P.S. I think I may have posted this in the wrong Forum if so I am sorry and i cannot work out how to delete it.
 
Last edited:
Hi Tman,

Try putting this code in Private Sub Worksheet_Change() module.

As this will code will be carried out every time the sheet calculates.

Regards,
 
Somendra Misra said:
Hi Tman,

Try putting this code in Private Sub Worksheet_Change() module.

As this will code will be carried out every time the sheet calculates.

Regards,
Click to expand...

Many thanks for this unfortunately (assuming i did this correctly) it is not working i am getting A Compile Error - Procedure does not match description of event or procedure having the same name

Code i have inputted is below

Code: 
Private Sub Worksheet_Change()
If Range("G18").Value > 0 Then MsgBox "Required Message Here"
End Sub
 
@Tman

I hope the value in G18 is entered manually, if so try below code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G18")) Is Nothing Then
    If Range("G18").Value > 0 Then
        MsgBox "Required Message Here"
    End If
End If

End Sub

Regards,
 
Hi SM,

many thanks for your help with this and you have nearly solved it for me.

The code you have supplied is now working and has solved part 1 of my problem and now each time i enter data i do not get a repetitive pop up box. THANKS!!

Part 2 of the problem (sorry i obviously did not explain it clearly in the original post) is that I am referencing "G18" (into which the data is manually entered in answer to your question) but at times I have to add lines to the spreadsheet in a position above "G18" before I enter the data into "G18" . This then makes that reference cell now different and I obviously lose the reference. Are you able to help with this?

Thank you so much for your time
Tman
 
Hi There just to let anyone who is interested know I solved the last part of this problem by naming the cell i was trying to follow and it is all working. To expand i called my "G18" cell "Reference" and used the following (thanks to #Somendra Misra)

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Reference")) Is Nothing Then

If Range("Reference").Value > 0 Then MsgBox "ENTER REQUIRED MESSAGE HERE"


End If


End Sub
 
You must log in or register to reply here.
Back
Top