Add cell values to a userform

detriez

Board Regular
Joined
Sep 13, 2011
Messages
139
Office Version
  1. 365
Platform
  1. Windows
I am using this to populate a userform field with the value from the clicked cell and it works fine


VBA Code:
Sub showform()
    UserForm1.Show

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then
            Call showform
        End If
    End If
End Sub

Code:
Private Sub UserForm_Activate()

TextBox3 = ActiveCell.value

End Sub

I need to also populate another

Code:
Private Sub UserForm_Activate()

Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Contacts")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        Me.TextBox3.value = .Cells(lRow, 1).value
        Me.TextBox14.value = .Cells(lRow, 2).value
    End With

End Sub


I need to also populate another userform field with the value from another column
This did not work. No error, it just does not populate the userform fields

Code:
Private Sub UserForm_Activate()

Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Contacts")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        Me.TextBox3.value = .Cells(lRow, 1).value
        Me.TextBox14.value = .Cells(lRow, 2).value
    End With

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,160
Office Version
  1. 2016
Platform
  1. Windows
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row is the last occupied row
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row is the next row after last occupied row

I think that is why your Me.TextBox3 and TextBox4 are blanks.
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
139
Office Version
  1. 365
Platform
  1. Windows
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row is the last occupied row
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row is the next row after last occupied row

I think that is why your Me.TextBox3 and TextBox4 are blanks.
Thanks Zot.. your reply helped me to realize I was referencing the wrong sheet..
I appreciate your direction

Here is what finally worked

VBA Code:
Private Sub UserForm_Activate()

Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Companies")
    lRow = ActiveCell.Row
    With ws
        Me.TextBox3.value = .Cells(lRow, 1).value
        Me.TextBox14.value = .Cells(lRow, 5).value
    End With


End Sub
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,160
Office Version
  1. 2016
Platform
  1. Windows
Glad you had it worked. Sometimes alternating between Range and Cells can lead to mistake. :)
Instead of Cells(lRow, 1) and Cells(lRow, 5), it is easier just to write it as Cells(lRow, "A") and Cells(lRow, "E") respectively. No need to count 😄
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top