Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Warn when a cell value has changed
I have a user entered value in cell A1. What I want to do is have a reminder
to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#2
|
|||
|
|||
How about a message box? Paste the following macro into the sheet module
for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#3
|
|||
|
|||
Thanks Otto, just what I was looking for. Les.
"Otto Moehrbach" wrote: How about a message box? Paste the following macro into the sheet module for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#4
|
|||
|
|||
Now, if there is a second user entered value in B1 how would I amend the
formula to give the reminder if either value is changed. Also, is it possible to prevent the msgbox alert when the initial value is entered? Les. "LesLdh" wrote: Thanks Otto, just what I was looking for. Les. "Otto Moehrbach" wrote: How about a message box? Paste the following macro into the sheet module for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#5
|
|||
|
|||
I don't follow what you want but let me give it a try. You want the message
box to pop up if an entry is made into either of the two cells but only if the other cell is already occupied (not blank). Is that correct? You first asked to include B1. IOW, if either cell is changed. That code would be: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0,0)="B1" Then _ MsgBox "Don't forget to do this and that also." End Sub Also, let me ask you a question. Do you want this message box to appear if the user changes the content of the cell to blank? Or just if he changes the content but not to blank? IOW, ignore the change if the changed cell is now blank? If you want the message box if either cell is changed, but not to blank, and only if the other cell is already occupied, then the following will do that: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0, 0) = "B1" Then If Application.CountA(Range("A1:B1")) = 2 Then _ MsgBox "Don't forget to do this and that also." End If End Sub HTH Otto "LesLdh" wrote in message ... Now, if there is a second user entered value in B1 how would I amend the formula to give the reminder if either value is changed. Also, is it possible to prevent the msgbox alert when the initial value is entered? Les. "LesLdh" wrote: Thanks Otto, just what I was looking for. Les. "Otto Moehrbach" wrote: How about a message box? Paste the following macro into the sheet module for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#6
|
|||
|
|||
Well, I didn't think you could do that! Thanks Otto, you were correct in
assuming that I wanted the msgbox just if he changes the content but not to blank? Many thanks for your time, Les. "Otto Moehrbach" wrote: I don't follow what you want but let me give it a try. You want the message box to pop up if an entry is made into either of the two cells but only if the other cell is already occupied (not blank). Is that correct? You first asked to include B1. IOW, if either cell is changed. That code would be: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0,0)="B1" Then _ MsgBox "Don't forget to do this and that also." End Sub Also, let me ask you a question. Do you want this message box to appear if the user changes the content of the cell to blank? Or just if he changes the content but not to blank? IOW, ignore the change if the changed cell is now blank? If you want the message box if either cell is changed, but not to blank, and only if the other cell is already occupied, then the following will do that: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0, 0) = "B1" Then If Application.CountA(Range("A1:B1")) = 2 Then _ MsgBox "Don't forget to do this and that also." End If End Sub HTH Otto "LesLdh" wrote in message ... Now, if there is a second user entered value in B1 how would I amend the formula to give the reminder if either value is changed. Also, is it possible to prevent the msgbox alert when the initial value is entered? Les. "LesLdh" wrote: Thanks Otto, just what I was looking for. Les. "Otto Moehrbach" wrote: How about a message box? Paste the following macro into the sheet module for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
#7
|
|||
|
|||
Glad it worked for you. Otto
"LesLdh" wrote in message ... Well, I didn't think you could do that! Thanks Otto, you were correct in assuming that I wanted the msgbox just if he changes the content but not to blank? Many thanks for your time, Les. "Otto Moehrbach" wrote: I don't follow what you want but let me give it a try. You want the message box to pop up if an entry is made into either of the two cells but only if the other cell is already occupied (not blank). Is that correct? You first asked to include B1. IOW, if either cell is changed. That code would be: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0,0)="B1" Then _ MsgBox "Don't forget to do this and that also." End Sub Also, let me ask you a question. Do you want this message box to appear if the user changes the content of the cell to blank? Or just if he changes the content but not to blank? IOW, ignore the change if the changed cell is now blank? If you want the message box if either cell is changed, but not to blank, and only if the other cell is already occupied, then the following will do that: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Address(0, 0) = "A1" Or Target.Address(0, 0) = "B1" Then If Application.CountA(Range("A1:B1")) = 2 Then _ MsgBox "Don't forget to do this and that also." End If End Sub HTH Otto "LesLdh" wrote in message ... Now, if there is a second user entered value in B1 how would I amend the formula to give the reminder if either value is changed. Also, is it possible to prevent the msgbox alert when the initial value is entered? Les. "LesLdh" wrote: Thanks Otto, just what I was looking for. Les. "Otto Moehrbach" wrote: How about a message box? Paste the following macro into the sheet module for that sheet. You do this by right-clicking on the sheet tab, select View Code, and paste this macro into the displayed module. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address(0, 0) = "A1" Then _ MsgBox "Don't forget to do this and that also." End Sub "LesLdh" wrote in message ... I have a user entered value in cell A1. What I want to do is have a reminder to update other cells flash up in cell A2 when the value in A1 is changed Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changed Cell Address | Excel Discussion (Misc queries) | |||
How do I get one cell to record the time another cell was changed. | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |