Automatic update

[Ron99]

Hi,

I have this code

Sub status_update()
Dim COM As String
On Error GoTo veryEnd
If Range("E1").Text = P Then
COM = MsgBox("Update % Column")
veryEnd:
End If
End Sub

The above code works fine, but I have problem,

I want the code to give out the message automatically when the word P is entered in E1, rather than running the macro then getting a message.

Thanks

 

[Joe4]

Place your code into an Event Procedure, specifically the Worksheet_Change event (see here for more on event procedures: http://www.cpearson.com/excel/Events.aspx).

I would start your code with:

If Target.Address = "$E$1" and Target.Text = "P" Then
...

 

[Ron99]

Hi,

I am really sorry, I do not understand coding...

Can you please correct the same code and send it..

Thank you

 

[Joe4]

I was under the mistaken impression that you wrote the code.
As is written, I don't think it will work because you don't have quotes around the letter P.

In our to get this to wirk automatically, you must place the following code in a Sheet module instead of a Standard Module. In order to do this, just go to the page you would like to run it against. Then, right click on the Sheet tab name at the bottom of the screen and select "View Code". Then place this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Count = 1 And Target.Address = "$E$1" Then
        If Target.Text = "P" Then
            MsgBox "Update % Column"
        End If
    End If
 
End Sub

Now this code will run anytime anyone manually types an upper case "P" in cell E1 on that sheet.