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

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

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?
 
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
 
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 said:
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
Click to expand...
 
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 said:
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".
Click to expand...
 
the userform only opens when a change in column 5 (E) is made .... as per
the original ...

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


JLGWhiz said:
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".
Click to expand...
 
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
 
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!
 
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
 
Back
Top