View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Sam Kuo[_3_] Sam Kuo[_3_] is offline
external usenet poster
 
Posts: 86
Default ControlSource question

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