Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== change to suit

   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       With Target
           Userform1.Textbox1.Text = .value
       End With
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Couple of points.

The text enterd in the worksheet won't be transferred to the textbox until
it is Entered.

The form must be displayed modeless to allow worksheet input.

C hange the constant WS_RANGE value to your target cell, or a range of
cells.