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

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


"Sam Kuo" <(E-Mail Removed)> wrote in message
news:1966F454-67F7-4FFA-9F3A-(E-Mail Removed)...
>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

Mmm..I see
Thanks Tim :-)


"Tim Zych" wrote:

> 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
>
>
> "Sam Kuo" <(E-Mail Removed)> wrote in message
> news:1966F454-67F7-4FFA-9F3A-(E-Mail Removed)...
> >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
>
>
>