How to programmatically set ControlSource?

[Sam Kuo]

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

 

[JLGWhiz]

Don't know if this will help but I put this in the initialize event for the
form and it worked OK.

Private Sub UserForm_Initialize()
UserForm1.Controls("TB1").ControlSource = Range("A1").Address
End Sub

Notice that I used the range address which produces a string value location
in A1 format. That is the only format that the control source will accept.
Your Column and Row reference should be throwing an error as invalid property
value.

 

[Tim Zych]

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

 

[Sam Kuo]

Thanks JLGWhiz!
I used the range address as you suggested and it's working fine.
But somehow when the cell value changes, the textbox doesn't update to
reflect the change...
i.e. In your exaple, if I enter 123 in textbox TB1, then click a button to
run a macro that clears cell A1. Cell A1 is now empty, but textbox TB1 would
still show 123.

Is there a way to ensure the textbox updates to reflect it's ControlSource
in this case?

 

[Sam Kuo]

Hi Tim
Thanks for your reply. But you seem to accidentally post it without any
comment?
Sam

 

[Tim Zych]

You've have a range so go from there.

Const RowNo As Integer = 21
Const ColNo As Integer = 46
UserForm1.Controls("txtCol??").ControlSource = _
Sheet1.Cells(RowNo, ColNo).Address(External:=True)

 

[Sam Kuo]

Hi Tim
I tried using the range address to assign ControlSource to a textbox, as you
and JLGWhiz suggested.

The cell value now changes whenever the textbox value changes, but NOT vice
versa (i.e. the textbox value does not update to reflect any changes made to
the cell, except for the first time that the cell value changes after form
initialization).

I guess this is because the SetControlSource subroutine runs at form
initialization?? Is there a way to ensure the textbox value always updates
whenever its control source value changes?

Sam