Using VBA to make a user input a note when making changes to a cel

How can I program in VBA to require that a user inputs a note (I want a blank
note to pop up) when they change a number in a cell?

For example, if I have cell B10 = 1,500 or =trend(B1:B9,A1:A9,B10:B20) and
someone decides to type in a different number into cell B10, I want Excel to
first open a pop up window asking 'What is the reason for the change?",
second not allow the person to continue until they input a reason in the pop
up box and finally save the note in the cell for future users to read.

Thank you.

Adam

"Adam Ronalds" skrev i meddelelsen
...
How can I program in VBA to require that a user inputs a note (I want a
blank
note to pop up) when they change a number in a cell?

For example, if I have cell B10 = 1,500 or =trend(B1:B9,A1:A9,B10:B20) and
someone decides to type in a different number into cell B10, I want Excel
to
first open a pop up window asking 'What is the reason for the change?",
second not allow the person to continue until they input a reason in the
pop
up box and finally save the note in the cell for future users to read.

Thank you.

Adam

Hi Adam

This will promt for a reason, to allow the change. If no reason is entered
the old value will be restored.

The code is to be copied into the codesheet for the desired sheet. Change
the TargetRange to suit your needs.

Dim OldVal As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Set TargetRange = Range("A110")
Set isect = Intersect(Target, TargetRange)
If Not isect Is Nothing Then
Comm = InputBox("What is the reason for this change", "Regards, Per
Jessen")
If Trim(Comm) = "" Then
Application.EnableEvents = False
Target.Value = OldVal
Application.EnableEvents = True
Else
Target.AddComment
Target.Comment.Visible = False
Target.Comment.Text Text:=Application.UserName & ":" & Chr(10) & Comm
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal = Target.Value
End Sub

Regards,
Per

I'm getting an error as follows:

"Complie Error:

Ambiguous name detected: Worksheet_change"

What to do?

Thanks!!!

Adam

"Per Jessen" wrote:


"Adam Ronalds" skrev i meddelelsen
...
How can I program in VBA to require that a user inputs a note (I want a
blank
note to pop up) when they change a number in a cell?

For example, if I have cell B10 = 1,500 or =trend(B1:B9,A1:A9,B10:B20) and
someone decides to type in a different number into cell B10, I want Excel
to
first open a pop up window asking 'What is the reason for the change?",
second not allow the person to continue until they input a reason in the
pop
up box and finally save the note in the cell for future users to read.

Thank you.

Adam

Hi Adam

This will promt for a reason, to allow the change. If no reason is entered
the old value will be restored.

The code is to be copied into the codesheet for the desired sheet. Change
the TargetRange to suit your needs.

Dim OldVal As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Set TargetRange = Range("A110")
Set isect = Intersect(Target, TargetRange)
If Not isect Is Nothing Then
Comm = InputBox("What is the reason for this change", "Regards, Per
Jessen")
If Trim(Comm) = "" Then
Application.EnableEvents = False
Target.Value = OldVal
Application.EnableEvents = True
Else
Target.AddComment
Target.Comment.Visible = False
Target.Comment.Text Text:=Application.UserName & ":" & Chr(10) & Comm
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal = Target.Value
End Sub

Regards,
Per

You can have only one Worksheet_Change event in a sheet.

Per gave you worksheet_change event and a worksheet_selectionchange event.

These should co-exsist and behave nicely.

Do you have another worksheet_change event in the sheet?


Gord Dibben MS Excel MVP

On Tue, 24 Jun 2008 09:38:01 -0700, Adam Ronalds
wrote:

I'm getting an error as follows:

"Complie Error:

Ambiguous name detected: Worksheet_change"

What to do?

Thanks!!!

Adam

"Per Jessen" wrote:


"Adam Ronalds" skrev i meddelelsen
...
How can I program in VBA to require that a user inputs a note (I want a
blank
note to pop up) when they change a number in a cell?

For example, if I have cell B10 = 1,500 or =trend(B1:B9,A1:A9,B10:B20) and
someone decides to type in a different number into cell B10, I want Excel
to
first open a pop up window asking 'What is the reason for the change?",
second not allow the person to continue until they input a reason in the
pop
up box and finally save the note in the cell for future users to read.

Thank you.

Adam

Hi Adam

This will promt for a reason, to allow the change. If no reason is entered
the old value will be restored.

The code is to be copied into the codesheet for the desired sheet. Change
the TargetRange to suit your needs.

Dim OldVal As Long
Private Sub Worksheet_Change(ByVal Target As Range)
Set TargetRange = Range("A110")
Set isect = Intersect(Target, TargetRange)
If Not isect Is Nothing Then
Comm = InputBox("What is the reason for this change", "Regards, Per
Jessen")
If Trim(Comm) = "" Then
Application.EnableEvents = False
Target.Value = OldVal
Application.EnableEvents = True
Else
Target.AddComment
Target.Comment.Visible = False
Target.Comment.Text Text:=Application.UserName & ":" & Chr(10) & Comm
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal = Target.Value
End Sub

Regards,
Per