VB Code tweak -Make users to enter text

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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top