If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Using VBA to make a user input a note when making changes to a cel
"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 |
#3
|
|||
|
|||
Using VBA to make a user input a note when making changes to a
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 |
#4
|
|||
|
|||
Using VBA to make a user input a note when making changes to a
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 |
Thread Tools | |
Display Modes | |
|
|