wayneboardman
New Member
- Joined
- Feb 1, 2005
- Messages
- 2
Hi Guys and Girls....
I have data validation on a column when the users chooses Yes I want an input box to force them to enter a comment with the results put in the next cell...
Now I hve searched the board and found some very use code..However instead of creating a comment I want the code to be tweaked so the text from the input box goes into the cell just as text !! NOT a comment Does that make sense ??
Here it is and thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:A10000")
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Target = "T" Then GoTo line1
If Target = "J" Then GoTo line1
If Target = "t" Then GoTo line1
If Target = "j" Then GoTo line1
GoTo line2
line1:
p1 = InputBox("You must enter a comment in the below input box")
If p1 = "" Then GoTo line3
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment
Target.Offset(0, 1).Comment.Visible = False
Target.Offset(0, 1).Comment.Text Text:=p1 & Chr(10) & ""
Target.Select
GoTo line2
line3:
MsgBox ("You Must enter a comment. Column A has been deleted")
Target.ClearContents
GoTo line2
line2:
End If
End Sub
I have data validation on a column when the users chooses Yes I want an input box to force them to enter a comment with the results put in the next cell...
Now I hve searched the board and found some very use code..However instead of creating a comment I want the code to be tweaked so the text from the input box goes into the cell just as text !! NOT a comment Does that make sense ??
Here it is and thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:A10000")
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Target = "T" Then GoTo line1
If Target = "J" Then GoTo line1
If Target = "t" Then GoTo line1
If Target = "j" Then GoTo line1
GoTo line2
line1:
p1 = InputBox("You must enter a comment in the below input box")
If p1 = "" Then GoTo line3
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment
Target.Offset(0, 1).Comment.Visible = False
Target.Offset(0, 1).Comment.Text Text:=p1 & Chr(10) & ""
Target.Select
GoTo line2
line3:
MsgBox ("You Must enter a comment. Column A has been deleted")
Target.ClearContents
GoTo line2
line2:
End If
End Sub