I'm trying to set the Control Source of all form controls using a macro at
form initialization.
But my attempt below doesn't populate the ControlSource property of the form
control (textbox in this example). How can I fix this?


Sub SetControlSource()
Const RowNo As Integer = 21
Const ColNo As Integer = 46
Dim wsSheet1 As Worksheet
Dim Col As Variant

Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")

' Convert column number to text
' (e.g. ColNo = 46 refers to column AT)
Col = Left(wsSheet1.Cells(1, ColNo).Address(0, 0), _
1 - (wsSheet1.Cells(1, ColNo).Column 26))

' Set the form control's ControlSource
UserForm1.Controls("txtCol" & Col & "Row" & RowNo).ControlSource = _
"'Sheet1'!" & Col & RowNo

' Messagebox check
MsgBox "txtCol" & Col & "Row" & RowNo & " control source = " & _
"'Sheet1'!" & Col & RowNo
End Sub