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

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.

"Sam Kuo" wrote:

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

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


"Sam Kuo" wrote in message
...
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

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?


"JLGWhiz" wrote:

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.

"Sam Kuo" wrote:

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

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


"Tim Zych" wrote:



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


"Sam Kuo" wrote in message
...
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

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)


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


"Sam Kuo" wrote in message
...
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