Excel UserForm textbox not updating when ControlSource cell changes

Question

I have a user form with various textboxes which have the ControlSource property set to cells in Sheet1. There is also a CommandButton.

Clicking the CommandButton retrieves data from a SQL Server database and populates Sheet1. However the textboxes are not refreshing unless I close the user form and reopen it. I have tried UserForm1.Repain as the last line in the CommandButton Click event but it is still not working.

Just a guess but have you tried reassigning the ControlSource property? I.e. remove it and then reapply it. #MightWork — markblandford
– markblandford, Commented May 8, 2012 at 10:53
That works, but I have over a hundred text boxes bound to different cells, and I don't want to have to update them all in code. — NiMuSi
– NiMuSi, Commented May 8, 2012 at 12:36

markblandford · Accepted Answer · 2012-05-09 17:23:56Z

1

OK, building on my previous comment, you could do something like this to automate the reloading of the ControlSource of each textbox on the form:

Dim ctl As Control

For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.TextBox Then
        ctl.ControlSource = ctl.ControlSource
    End If
Next ctl

Set ctl = Nothing

answered May 9, 2012 at 17:23

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hassan · Accepted Answer · 2012-05-08 13:02:37Z

0

I dont quite understand... tried to replicate your issue but i have not probs. I used the following code

Private Sub CommandBut_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqls As String
Dim myRec As Integer
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection

cnn.ConnectionString = "UID=***;PWD=***;DSN=***;"

sqls = "select data1, data2 from someRandomTable where data1 = '" & textbox1 & '" and data2 = '" & textbox2 & '"
rs.Open sqls, cnn.ConnectionString, adOpenStatic

 Dim z As Integer
z = 1
If rs.RecordCount > 0 Then
   rs.MoveFirst

  Do While Not rs.EOF
  Cells(z, 1).Value = rs.Fields(0).Value
          Cells(z, 2).Value = rs.Fields(1).Value
              z = z + 1
        End With
    rs.MoveNext
  Loop
  Else
  MsgBox "nothing found", vbCritical
End If
Set rs = Nothing
Set cnn = Nothing
End Sub

I have set ControlSourceProperty of TextBox1,2 to sheet1!A1 and B1.

The CommandBut_Click will read the values of textboxes, then overwrite it with the SQL and it does update the textbox1 and 2 values.

answered May 8, 2012 at 13:02

Hassan

3011 silver badge13 bronze badges

1 Comment

NiMuSi Over a year ago

my code is returning the data correctly to the worksheet. The problem I am having is displaying it to the user in a UserForm. This is the code I am using for the display: txtSurname.ControlSource = "Sheet1!B2" txtTitle.ControlSource = "Sheet1!C2" txtFirstForename.ControlSource = "Sheet1!D2" txtOtherForenames.ControlSource = "Sheet1!E2" and so on (for over a 100 textBoxes). Setting the ControlSource property at design time should obviate the need for this, but it doesn't seem to work.

Collectives™ on Stack Overflow

Excel UserForm textbox not updating when ControlSource cell changes

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related