Dynamic Userform on Double Click

[LegendOfTheFall]

Hello everyone.

First, I hope I'm posting in the right place. I'm new to the forum.

I have a spreadsheet that opens an input box when I double click a row within column F. The contents of the input box is then placed into the corresponding row of column AA when I click OK. (i.e. If I'm in row 12, my comments will be placed in AA12). This works fine.

What I'm trying to do is use a Userform to do the same thing. I can get the Userform to show on double click and even submit the textbox contents into a specific cell, but how do I do this for dynamic rows within a specific column as mentioned above.

I'm relatively new to VBA.

Thank you all for your assistance.

 

[My Aswer Is This]

Working on this

 

[My Aswer Is This]

I think you need a command like this in your UserForm Command button:

Private Sub CommandButton1_Click()
ActiveCell.Rows.Columns(27).Value = TextBox1.Value
Me.Hide
End Sub

 

[LegendOfTheFall]

This works. Thank you...

I was pretty close. Any idea on how I can get the Userform from popping up when I delete a cell?
So, I want to be able to delete a cell in F12 and automatically delete the comments in AA12 without the
Userform appearing.

 

[My Aswer Is This]

No action should take place unless you double click a cell.
Show me your sheet script that activates the UserForm popping up.

 

[LegendOfTheFall]

Ok.

Let me try to provide a better scenario for what I'm attempting to do since I'm receiving some error handling issues.
I have five columns

A = Full Name
B = Phone
C = Schooll

G= Calling Hours
H= Degree Details

When someone enters data into column B (Phone Hours), I would like a Userform to appear (on change) that prompts the user
to enter comments into a userform textbox reagrding the best time to reach them. When the usere clicks OK, I would like that data
to be placed into column G (calling Hours) of the same row. I would like a similar form to do the same for column C(School) and place the degree info
into column H (Degree Details). In addition, when I double click on columns B(Phone) or C(School), I would like the G(Calling Hours) or H(Degree Details)
to be retrieved into the textbox, respectively.

I know I can use a single form, but for for reason I won't go into, I need seperate Userforms.

I can get all of this to work. Where i'm having trouble is:
-my userform is appearing if I edit any cell on the worksheet. I only want it to affect a specific range (e.g. B3:B20)
-I need the textbox setfocused and cleared when I go to column H(Degree Hours)

Thank you in advance.

IamLegend

 

[My Aswer Is This]

Sounds as if the only help you need now is this:
This restricts the actions from only happening within your requested range:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3:B20")) Is Nothing Then

 

[My Aswer Is This]

One additional unsolicited bit of advice. I see one of your fields is "Full Name". I highly recommend you always have separate fields for all your data. And if need be later you could have a field that combines two fields. I see many times here on Mr. Excel people asking for help separating out First and Last Names for them which can be difficult. Combining First and last names is much easier then separating Full names. You may not think it's important now but a few years down the road you may wish you had. And the same applies to address and street name and city state and zip.

if you need more help let me know

 

[LegendOfTheFall]

Thanks for the assistance.

So, am I supposed to Call the change subroutine within the on click subroutine of the OK Button?
I'm confused.

Your suggestion is what I had when I was using the input box, which I'm tempted to just revert back to since I had everything working.

 

[My Aswer Is This]

No. The change event code has to be in the sheet. You said you had it working but just wanted it to only work in this range. So add this to the sheet change event:
If Not Intersect(Target, Range("B3:B20")) Is Nothing Then.

As far as to the rest of your needs. I'm confused myself. Your asking for several different Useforms to pop up and on and on. I'm just here to help you but to be honest I don't like your plan. I would think you need just one Userform and have the user fill in certain textboxes. and then just have the UserForm fill in all your data. I would not be using a sheet change event. Just provide a button to start the process.