When this happens, do that, then do the next thing
 

When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with text.
A button on the Userform will close it and copy the text from the userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the adjascent
cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with
text.
A button on the Userform will close it and copy the text from the userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

Ok, first part working - userform appears and I can fill in the text boxes.
When I hit the command button, I need the code to copy the (up to 10) text
box contents and place them in a comment (not a cell) in the adjacent cell
and close the UserForm :)
--
Traa Dy Liooar

Jock


"JLGWhiz" wrote:

A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

the userform only opens when a change in column 5 (E) is made .... as per
the original ...

If Target.Column = 5 Then

When a date is placed in "E", Userform1 to appear.

plus the button click event ??? what button? the event used is the sheet's
CHANGE event


"JLGWhiz" wrote in message
...
A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

slight amendment to userform code:

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text & textbox2.Text ' and so on
Me.Close
End Sub

"Jock" wrote in message
...
Ok, first part working - userform appears and I can fill in the text
boxes.
When I hit the command button, I need the code to copy the (up to 10) text
box contents and place them in a comment (not a cell) in the adjacent cell
and close the UserForm :)
--
Traa Dy Liooar

Jock


"JLGWhiz" wrote:

A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform
with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

There is a command button the user form.
When this button is clicked, your code will copy the text boxes to an
adjacent cell.
How can this code be modified to create a comment in the adjacent cell and
copy the text box contents to it.
I have been trying this: Target.Offset(0, -1).AddComment = TextBox1.Text & "
" & TextBox2.Text
but am getting compile errors!

--
Traa Dy Liooar

Jock


"Patrick Molloy" wrote:

the userform only opens when a change in column 5 (E) is made .... as per
the original ...

If Target.Column = 5 Then

When a date is placed in "E", Userform1 to appear.

plus the button click event ??? what button? the event used is the sheet's
CHANGE event


"JLGWhiz" wrote in message
...
A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock

When this happens, do that, then do the next thing
 

if you're in the =userform, then Target has no meaning as its only in scope
while in the change event of the sheet.

Hoever, the cell that raised the event is still selected, hence in my
userform i used

Selection.Offset(, -1) = TextBox1.Text

so try changing Target to Selection


"Jock" wrote in message
...
There is a command button the user form.
When this button is clicked, your code will copy the text boxes to an
adjacent cell.
How can this code be modified to create a comment in the adjacent cell and
copy the text box contents to it.
I have been trying this: Target.Offset(0, -1).AddComment = TextBox1.Text &
"
" & TextBox2.Text
but am getting compile errors!

--
Traa Dy Liooar

Jock


"Patrick Molloy" wrote:

the userform only opens when a change in column 5 (E) is made .... as per
the original ...

If Target.Column = 5 Then

When a date is placed in "E", Userform1 to appear.

plus the button click event ??? what button? the event used is the
sheet's
CHANGE event


"JLGWhiz" wrote in message
...
A little modification to Patrick's code:

Option Explicit
Private Sub CommandButton1_Click()
If Selection.Column 1 Then
Selection.Offset(, -1) = TextBox1.Text
End If
End Sub

This avoids an error message when the selection is in Column "A".


"Patrick Molloy" wrote in message
...
use the sheet's Change event to show the userform...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
UserForm1.Show
End If
End Sub


next, the button on the userform ....would drop the text into the
adjascent cell. so the userform code woulkd be like

Option Explicit
Private Sub CommandButton1_Click()
Selection.Offset(, -1) = TextBox1.Text
End Sub

"Jock" wrote in message
...
When a date is placed in "E", Userform1 to appear.
The user can fill in upto four seperate text boxes on the Userform
with
text.
A button on the Userform will close it and copy the text from the
userform
to a comment in the adjacent cell in "D"

Is this do-able?
--
Traa Dy Liooar

Jock