SOLVED

How to get a pop-up message to show only for the intended column.

%3CLINGO-SUB%20id%3D%22lingo-sub-2811839%22%20slang%3D%22en-US%22%3EHow%20to%20get%20a%20pop-up%20message%20to%20show%20only%20for%20the%20intended%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2811839%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20sheet%20where%20i%20have%203%20different%20pop-up%20messages%20to%20show%20for%20certain%20values%20in%20cells%20in%20a%20certain%20column.%3C%2FP%3E%3CP%3EE.g.%20a%20value%20between%201-5%20generates%20one%20message%2C%20a%20value%20between%206-10%20generates%20a%20different%20message...%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20this%20message%20seems%20to%20pop%20up%20for%20every%20cell%20in%20that%20same%20row%20afterwards.%20How%20can%20I%20change%20the%20code%20to%20get%20it%20to%20pop%20up%20only%20for%20that%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20spend%20enough%20time%20on%20this%20%3Asee_no_evil_monkey%3A%20and%20hope%20someone%20can%20help%20me%20to%20get%20forward.%3C%2FP%3E%3CP%3ERegards%20Anna%3C%2FP%3E%3CP%3EMy%20code%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20total%20As%20Double%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.Calculate%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20total%20%3D%20Sheet1.Range(%22E%22%20%26amp%3B%20Target.Row).Value%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20(total%20%26gt%3B%3D%201)%20And%20(total%20%26lt%3B%3D%205)%20Then%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Call%20MsgBox(%22Message%201-5.%22%2C%20vbOKOnly%2C%20%22Evaluation%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20(total%20%26gt%3B%3D%206)%20And%20(total%20%26lt%3B%3D%2010)%20Then%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Call%20MsgBox(%22Message%206-10.%22%2C%20vbOKOnly%2C%20%22Evaluation%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20(total%20%26gt%3B%3D%2011)%20And%20(total%20%26lt%3B%3D%2015)%20Then%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Call%20MsgBox(%22Message%2011-15.%22%2C%20vbOKOnly%2C%20%22Evaluation%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3EEnd%20Sub%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2811839%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2811979%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20a%20pop-up%20message%20to%20show%20only%20for%20the%20intended%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2811979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175219%22%20target%3D%22_blank%22%3E%40AnnaS550%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20column%20E%20the%20column%20you%20want%20to%20look%20at%3F%20If%20not%2C%20which%20one%3F%3C%2FP%3E%0A%3CP%3EAnd%20does%20this%20column%20contain%20a%20formula%20or%20is%20it%20filled%20in%20by%20the%20user%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2812541%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20a%20pop-up%20message%20to%20show%20only%20for%20the%20intended%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812541%22%20slang%3D%22en-US%22%3EThank%20you%20Hans%20for%20quick%20response%2C%3CBR%20%2F%3EYes%20the%20column%20I%20want%20to%20look%20at%20is%20E%20and%20it%20contains%20the%20formula%20(%3DCx*Dx)%20with%20x%20being%20the%20row...%3CBR%20%2F%3ESo%20column%20C%20and%20D%20is%20filled%20by%20the%20user%20and%20column%20E%20is%20meant%20to%20calculate%20the%20product%20of%20the%20factors%20in%20C%2FD.%3CBR%20%2F%3E%2F%20Anna%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi.

I am working on a sheet where i have 3 different pop-up messages to show for certain values in cells in a certain column.

E.g. a value between 1-5 generates one message, a value between 6-10 generates a different message...

My problem is that this message seems to pop up for every cell in that same row afterwards. How can I change the code to get it to pop up only for that column?

 

I have spend enough time on this and hope someone can help me to get forward.

Regards Anna

My code below:

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim total As Double

    Application.Calculate

    total = Sheet1.Range("E" & Target.Row).Value

    If (total >= 1) And (total <= 5) Then

        Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")

    End If

   

     If (total >= 6) And (total <= 10) Then

        Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")

    End If

   

       If (total >= 11) And (total <= 15) Then

        Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")

    End If

   

End Sub

4 Replies

@AnnaS550 

Is column E the column you want to look at? If not, which one?

And does this column contain a formula or is it filled in by the user?

Thank you Hans for quick response,
Yes the column I want to look at is E and it contains the formula (=Cx*Dx) with x being the row...
So column C and D is filled by the user and column E is meant to calculate the product of the factors in C/D.
/ Anna
best response confirmed by AnnaS550 (New Contributor)
Solution

@AnnaS550 

Here you go:

 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim total As Double
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("C:D"), Target) Is Nothing Then
        Application.Calculate
        Application.EnableEvents = False
        total = Sheet1.Range("E" & Target.Row).Value
        If total >= 1 And total <= 5 Then
            Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")
        ElseIf total <= 10 Then
            Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")
        ElseIf total <= 15 Then
            Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")
        End If
        Application.EnableEvents = True
    End If
End Sub

 

Yes! Thank you so much for your help. Much appreciated!
/ Anna