Embed Optional Userform In Active Row?

[croon]

I am hoping to embed an optional userform in a spreadsheet that will be used for some but not all records, and would only be used after other data has already been directly entered into that record. I am new to userforms in general and all of the tutorials I am finding seem to work from an 'insert data into the first blank row' type of structure, which would not work for what I am trying to do.

Could someone point me to a tutorial/example code that would fit this approach? If it's not clear from the above, here is a rough example of my columns:

ID# Age Name UserformButton Score 1 Score 2 Score 3 Score 4

Basically, I would like to hand enter ID#, Age, and Name, and then have a button to pull up a userform that would be used to enter all of the subsequent scores (there are actually about 15 scores if that matters) for that specific record. The spreadsheet is actually much more complex than this and, although it probably looks like the easy solution from this rough example, putting the initial ID, age, etc. values in the userform would not work.

Thanks for any assistance/links.

 

[My Aswer Is This]

I'm assuming:

ID# Age Name​

Are being entered in column A B and C

And you plan to enter the other values into textbox's on your userform

Well we could use a button you would click on to open the user Form or you could double click on column D to have the userform open.

Now then we would need to know the names of your UserForm Textboxes.

It's always best to use the default names of textboxes. This way we can write a script to loop through them

So Textbox1. value goes into column 4 Textbox2.value goes into column 5 and on and on.

So then when you have all you textbox's filled in click on a userform button and the script runs filling in your values on the sheet.

Now how would we know what row you want these values entered into.
And if not on the active sheet what would the sheet name be.

 

[My Aswer Is This]

Assuming you want to double click on column 4 to open your userform.
And your userform is named UserForm1
And you want your new values entered on the active row starting in column 4
My script loops through 5 textboxes named Textbox1 Textbox2 and so on
If you need 15 textboxes then change 5 to 15 in the script.
And the script assigned to Command Button 2 when run will load all your textbox values into active cell row
starting in column 4 on the active sheet.

You will need these three scripts.

Put this script in any Module on your active workbook.

Option Explicit
Public r As Long '~~> at the very top of the module outside any sub
Public Sub Anysub()
r = ActiveCell.Row
End Sub

And put this script in your userform. Change button name if needed and change 5 to 15 if needed depending on how many textboxes you have.

Private Sub CommandButton2_Click()
Dim i As Long
For i = 1 To 5
Cells(r, i + 3).Value = Controls("Textbox" & i).Value
Next
End Sub

And put this script in your active sheet.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Cancel = True
Call Anysub
UserForm1.Show
End If
End Sub

Which will open your userform when you double click on any cell in column 4