Problem with TextBox & ControlSource - Please Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
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
 
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
 
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.
 
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.
 
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.
 
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.
 
Back
Top