Assumming your userform is being shown as vbModeless, (which it would need to be in order for manual changes to be made while it's showing) you can use the worksheet change event like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
.Caption = Cells(Rows.Count, 7).End(xlUp).Value
It would probably be a good idea to use the initialize event like Norie showed you too.