Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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("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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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("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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation note to comment note | Excel Discussion (Misc queries) | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Sort Without User Input | Excel Discussion (Misc queries) | |||
How do I make note cards from an inventory in excel? | Excel Discussion (Misc queries) |