Q Re: userform txtbox population with row data from cell clicked to access userform.

[OTOTO]

In my worksheet I created code and a module so that when I double-click cells in column N a userform will open up. I need to pull information from the other columns in the same row that was used to access the form to populate text boxes in the userform. I am new to VBA and Excel Userforms so replies in some detail are more helpful. Thanks in advance for the help.

 

[tweedle]

Hi, Welcome to the forum.


Here's a way...


Open the IDE [ALT+F11]
Insert a Module [Right Click on ThisWorkbook, Insert, Module]


Then declare public variables to track where you are in the workbook

Public currSheet As Worksheet
Public currRow As Long

Then, In your Worksheet_BeforeDoubleClick event;
set the currSheet object and currRow variable

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    If Target.Address Like "$N*" Then
        currRow = Target.Cells(1, 1).Row
        Set currSheet = Target.Worksheet
        Load UserForm1
        UserForm1.Show
    End If


End Sub

Now they are exposed to the UserForm


Now in your Userform code, you can reference those elements to pull information.

Private Sub UserForm_Initialize()
    
    Me.TextBox1.MultiLine = True
    Me.TextBox1.Text = currSheet.Cells(currRow, "B") & vbLf & _
    currSheet.Cells(currRow, "C")
    
End Sub

 

[OTOTO]

This is working.

I swear it feels like I'm driving while asleep and then arriving at my destination.
Thanks so much for taking the wheel tweedle dee

 

[tweedle]

No Problem.
You just have to get into the mind-set that everything is an object, and every object has events(actions that happen to it), properties (attributes) and methods (actions it can do) - then the world becomes more clear.

ALT+F11, F2 opens the object browser - a very useful place to peruse.

 

[OTOTO]

The only issue that I'm having now is that when the information from this text box is submitted into its destination the value is repeated. For instance if the value that is pulled from the origin sheet is the number 6, I see "6" in the userform's textbox but when I submit it into the destination sheet I see 6 (Paragraph) 6, stacked on top of each other, but the cell identifies that its value is "6"? I've never seen this before

 

[OTOTO]

Bump*****

 

[tweedle]

Can you post the code you're using

 

[OTOTO]

Can you post the code you're using

The VBA Code for the worksheet looks like this:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address Like "$N*" Then
currRow = Target.Cells(1, 1).row
Set currSheet = Target.Worksheet
Load frmEditEntry
frmEditEntry.Show
End If
End Sub
-----------------------------------------------------------------
The VBA to shoot the form information into the appropriate worksheet and cells looks like this:
Private Sub cmdSubmitEdit_Click()
Dim RowCount As Long
Dim ctl As Control
If Me.cboNameEdit.Value = "" Then
MsgBox "Please enter your name in the appropriate dropdown box.", vbExclamation, "EditEntryForm"
Exit Sub
End If
RowCount = Worksheets("Existing Risk Changes").Range("A15").CurrentRegion.Rows.Count
With Worksheets("Existing Risk Changes").Range("A15")
.Offset(RowCount, 0).Value = Me.txtRiskIDEdit
.Offset(RowCount, 1).Value = Me.cboRiskCategoryEdit
.Offset(RowCount, 2).Value = Me.cboMissionObjectiveEdit
.Offset(RowCount, 3).Value = Me.txtRiskDescriptionEdit
.Offset(RowCount, 4).Value = Me.cboFrequencyEdit
.Offset(RowCount, 5).Value = Me.cboConsequenceEdit
.Offset(RowCount, 8).Value = Me.txtExistingMitigationEdit
.Offset(RowCount, 9).Value = Me.cboeffectivenessedit
.Offset(RowCount, 11).Value = Me.txtFurtherActionsCommentsEdit
.Offset(RowCount, 13).Value = Me.cboNameEdit
.Offset(RowCount, 15).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
.Offset(RowCount, 14).Value = UserName()
End With
Dim LResponse As Integer

LResponse = MsgBox("The information you entered was submitted. Thank you.", vbOKOnly)

If LResponse = vbOK Then
Unload Me
End If
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


Thanks for the help Tweedle

 

[tweedle]

OK - Is there a Form _Initialize that's loading the data to the form?
Which control are we talking about?

 

[OTOTO]

OK - Is there a Form _Initialize that's loading the data to the form?
Which control are we talking about?

The first bit of code that I pasted above was the VBA Code for the worksheet that works with this Module:

Sub OpenEditEntryForm()
frmEditEntry.Show vbModeless
End Sub

As for which control we're talking about: the bottom bit of code was copied from another form. It clears the data in all the controls upon submission but it is not applicable to this form. I deleted: For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl

...and the problem remained.