Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlSource question
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlSource question
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ControlSource and Localization Issue?? (run-time error '380' Could not set the ControlSource property) | Excel Programming | |||
.ControlSource | Excel Programming | |||
ControlSource | Excel Programming | |||
question about the ControlSource of a ComboBox | Excel Programming | |||
Basic Userform Controlsource question | Excel Programming |