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

A

Adam Ronalds

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
 
P

Per Jessen

Adam Ronalds said:
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("A1:D10")
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
 
A

Adam Ronalds

I'm getting an error as follows:

"Complie Error:

Ambiguous name detected: Worksheet_change"

What to do?

Thanks!!!

Adam
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top