Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
I have a form which has a number of TextBoxes that have their ControlSource
property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
You could try to set the control source property for each textbox after the
row is inserted. (.Insert shift=:xlDown) so that row 2 is blank. load userform now set the control source for each textbox with code and finally load & show the form or You could also try "Userform1.Repaint" which Updates the display by redrawing the form or page. -- Regards, Rocky McKinley wrote in message ... I have a form which has a number of TextBoxes that have their ControlSource property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
This is the standard Excel behaviour. If you insert a
row, then all references get adjusted automatically. Don't set the rowsource ...do it in the form's start up procedure AFTER you've inserted the row... MyText.Rowsource = "Sheet1!B2") Patrick Molloy Microsoft Excel MVP -----Original Message----- I have a form which has a number of TextBoxes that have their ControlSource property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
Doublecheck your code and your property assignments. It's not really a
timing issue, and there's no need to refresh anything. Something like this should work every time: Sub DoForm() Dim MyRange As Range Set MyRange = Worksheets("MySheet").Rows(2) MyRange.Insert shift:=xlDown UserForm1.Show End Sub Even if you delay inserting a new blank row until you intialize the form, it should still work: Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Worksheets("MySheet").Rows(2) MyRange.Insert shift:=xlDown End Sub wrote in message ... I have a form which has a number of TextBoxes that have their ControlSource property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
I'm not sure exactly what code and property assignments I should check.
Everything is working properly except that when the form comes up it is displaying values from the wrong row (row 3). If I change the value of a text box the new value is placed into its proper cell in row 2. Therefore the row source property is correct. The code you are saying should work properly every time is what is not working. Sub EnterGift() Dim i As Long Dim s As String If Range("G!B2") < "" Then 'Insert new line for new donation Worksheets("G").Rows("2:2").Insert Shift:=xlDown Worksheets("G").Rows("2:2").Font.Bold = False Range("G!E2:CH2").NumberFormat = "0.00" End If Worksheets("G").Calculate 'Failing attempt to get around problem Worksheets("G").Range("F2").Formula = "=SUM(G2:CH2)" i = CurMbrRow With EnterDonation .Header.Caption = Range(mbrNo & i) & " - " & Range(mbrName & i) .gDate = DefaultDate Range("G!A2") = Int(Range(mbrNo & i)) Range("G!B2") = Range(mbrName & i) Application.Calculate 'Another failed attempt to solve problem End With Call SetDesignationsVisible 'This makes some controls visible EnterDonation.Show 'This shows values from row 3 End Sub On Tue, 13 Jan 2004 03:20:57 -0500, "C Petrin" wrote: Doublecheck your code and your property assignments. It's not really a timing issue, and there's no need to refresh anything. Something like this should work every time: Sub DoForm() Dim MyRange As Range Set MyRange = Worksheets("MySheet").Rows(2) MyRange.Insert shift:=xlDown UserForm1.Show End Sub Even if you delay inserting a new blank row until you intialize the form, it should still work: Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Worksheets("MySheet").Rows(2) MyRange.Insert shift:=xlDown End Sub wrote in message .. . I have a form which has a number of TextBoxes that have their ControlSource property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
Your solution of re-setting ControlSource before showing the form works!
However, I would like to find another solution, if possible" because there are 163 of these text boxes and the form is being redisplayed many times during each session. I'm afraid that would slow things down too much. Repainting the form does not help. neither does explicitly setting the cells in row 2 to a value just prior to showing the form. Your solution may be the only one, but if you (or anyone) can think of another it would be very helpful. On Mon, 12 Jan 2004 23:39:56 -0800, "Rocky McKinley" wrote: You could try to set the control source property for each textbox after the row is inserted. (.Insert shift=:xlDown) so that row 2 is blank. load userform now set the control source for each textbox with code and finally load & show the form or You could also try "Userform1.Repaint" which Updates the display by redrawing the form or page. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
Form additional "playing around" I've done, it's clear that it works like
you say, at least "sort of". The reason I say, "Sort of" is because if I enter a value into a TextBox the value is placed into the correct cell of row 2 and then Excel seems to use row 2 for that TextBox but not for other unaltered text boxes. If the references were truly updated, how could it be storing stuff in row 2? I placed a breakpoint on the "form.Show" statement (which is after the row is inserted) and used the immediate window to to print TextBoxName.ControlSource and it showed row 2. Confusing to me. On Tue, 13 Jan 2004 00:13:57 -0800, "Patrick Molloyu" wrote: This is the standard Excel behaviour. If you insert a row, then all references get adjusted automatically. Don't set the rowsource ...do it in the form's start up procedure AFTER you've inserted the row... MyText.Rowsource = "Sheet1!B2") Patrick Molloy Microsoft Excel MVP -----Original Message----- I have a form which has a number of TextBoxes that have their ControlSource property set to various cells in row 2 of a worksheet. The routine which displays the form begins by inserting a new row as row 2 (.Insert shift=:xlDown) so that row 2 is blank. It then does Application.Calculate (should not be necessary) and finally shows the form. The first time the form is displayed the TextBoxes show the values that are in row 3 rather than the blank values that are in row two. If I change a TextBox the corresponding in cell in row 2 changes as it should. Why is the form displaying values in row 3 in TextBoxes whose ControlSource is set to row 2? How can I fix it. MANY thanks for any help on this one. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
The code you supplied works for me, at least on a simple userform (my
test form had 20 textboxes sourced to different values and formulas). But I had to strip it down a bit (removed references to "mbrNo" and "mbrName", and replaced your SetDesignationsVisible procedure by directly setting the Visible property of various textboxes true/false), so have a closer look there. Hard to say what's in SetDesignationsVisible, but you might want to try running it from the form's Initialize event instead, and see if that makes a difference. wrote in message ... I'm not sure exactly what code and property assignments I should check. Everything is working properly except that when the form comes up it is displaying values from the wrong row (row 3). If I change the value of a text box the new value is placed into its proper cell in row 2. Therefore the row source property is correct. The code you are saying should work properly every time is what is not working. Sub EnterGift() Dim i As Long Dim s As String If Range("G!B2") < "" Then 'Insert new line for new donation Worksheets("G").Rows("2:2").Insert Shift:=xlDown Worksheets("G").Rows("2:2").Font.Bold = False Range("G!E2:CH2").NumberFormat = "0.00" End If Worksheets("G").Calculate 'Failing attempt to get around problem Worksheets("G").Range("F2").Formula = "=SUM(G2:CH2)" i = CurMbrRow With EnterDonation .Header.Caption = Range(mbrNo & i) & " - " & Range(mbrName & i) .gDate = DefaultDate Range("G!A2") = Int(Range(mbrNo & i)) Range("G!B2") = Range(mbrName & i) Application.Calculate 'Another failed attempt to solve problem End With Call SetDesignationsVisible 'This makes some controls visible EnterDonation.Show 'This shows values from row 3 End Sub On Tue, 13 Jan 2004 03:20:57 -0500, "C Petrin" wrote: Doublecheck your code and your property assignments. It's not really a timing issue, and there's no need to refresh anything. Something like this should work every time: Sub DoForm() Dim MyRange As Range Set MyRange = Worksheets("MySheet").Rows(2) MyRange.Insert shift:=xlDown UserForm1.Show End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TextBox & ControlSource - Please Help
Thanks to all the folks who took the time to answer this. Setting the
control source property after the data has been updated solved the problem and was very fast. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with TAB key and Textbox controls | Excel Programming | |||
Controlsource | Excel Programming | |||
TextBox SetFocus Problem | Excel Programming | |||
ControlSource problem | Excel Programming | |||
Excel textbox problem | Excel Programming |