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.

Answer 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

Answer 2

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.