Untested but something like this:
1. Add a new Class module to your project, change its name to CTextboxHandler, then paste this code in:
Option Explicit
Private WithEvents m_tb As MSForms.Textbox
Private m_rngLink As Excel.Range
Public Property Set Textbox(tb As MSForms.Textbox)
Set m_tb = tb
End Property
Public Property Get Textbox() As MSForms.Textbox
Set Textbox = m_tb
End Property
Public Property Set CellLink(rng As Excel.Range)
Set m_rngLink = rng
End Property
Private Sub m_tb_Change()
m_rngLink.Value = m_tb.Value
End Sub
Now add this to your Userform code:
at the top of the code module before any routines:
Now in the Userform_Initialize code you need:
Private Sub Userform_Initialize()
Dim oHandler as CTextboxHandler
Dim x As Long
set colTBs = New Collection
For x = 1 To 15
Set oHandler = New CTextboxHandler
set oHandler.Textbox = Me.Controls("txtPTno" & x)
Set oHandler.CellLink = Sheets("Personal Tax").Range("C" & x).Offset(x - 2)
colTBs.add oHandler
Next x
End Sub
Bookmarks