ControlSource question

[Sam Kuo]

I try to programatically specify a textbox's control source property, but the
texbox doesn't change when the cell changes...why?

I found a similar thread Jeff Ciaccio posted here on 6/17/2008 and the
answer (from JLGWhiz, Rick Rothstein and Tim Zych) is that the control source
property should work both ways.

Below is my test example: Cell A1 changes OK whenever textbox TB1 changes.
But when row 1 is deleted the textbox value doesn't clear, except for the
first time only!

I appreciate if anyone can tell me why the textbox only changes when the
cell changes for the first time, and how can I fix this so it works both ways
at ALL TIME?

' Set textbox TB1's ControlSource property at form initialization
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
UserForm1.Controls("TB1").ControlSource = _
ws.Cells(1, 1).Address(External:=True)
End Sub

' Delete row 1 when click CommandButton1
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells(1, 1).EntireRow.Delete Shift:=xlUp
End Sub

 

[Tim Zych]

Try this modification. Sometimes the ControlSource needs to be refreshed.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells(1, 1).EntireRow.Delete Shift:=xlUp

With Me.tb1
.ControlSource = .ControlSource
End With

End Sub

 

[Sam Kuo]

Mmm..I see
Thanks Tim

Try this modification. Sometimes the ControlSource needs to be refreshed.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells(1, 1).EntireRow.Delete Shift:=xlUp

With Me.tb1
.ControlSource = .ControlSource
End With

End Sub

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