How to force ControlSource updating (excel userforms)

Question

On my Excel VBA userform there is a textbox where the users should input the date in dd-mm-yy format. If the input is 09-22-13, it should be updated to 22-09-2013. This textbox has a ControlSource property set to an address of a cell; this cell's value should become 22-09-2013, too.

The problem with all events handlers I have tried is that the value of ControlSource gets updated before the handler is triggered, and I cannot change the value of ControlSource unless I hardcode its address (this is something I'd like to avoid).

Could you help? Thanks.

Private Sub TextBox_MyDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox_MynDate.Value = Format(TextBox_MyDate.Value, "dd/mm/yyyy")
    ' TextBox_MyDate.ControlSource.Value = TextBox_MyDate.Value does not compile
    DoEvents
End Sub

Answer 1

Here something to consider, controlsource update and event order can't be changed it seems, so you may try to add worksheet_change event before the textbox event as the former fires before textbox event exists..

Reference:

• It says, if you use ContolSource then the Textbox will refresh each time the relevant cell changes, but be aware, the reverse is true. Change a TextBox & the cell will change

Answer 2

   Private Function GetCtrlSourceStr$()
        On Error Resume Next
        GetCtrlSourceStr = wCtrl.ControlSource
   End Function

' ' if Form is unloaded and the values in Sheet FoFiCriteria changed by editing the worksheet ' the form Textboxes or Checkboxes will not have linked to these changes .. ' a Form Showing or hidden will still synchronize with its control sources ' to work when the Form is open but FoFiCriteria is not the active sheet ' these ControlSource strings need to be as ,, SHeetName!B14 .. FoFiCriteria!L9 ' as from ' RaAdd = ra(3, Ci).Address(False, False, , True) ' .ControlSource = Mid(RaAdd, InStr(RaAdd, "]") + 1)

' so editing on form should link to the range of the control source on its sheet ' so we need '

Sub ReGetCtrlSources()
   For Each wCtrl In frd.Controls
        If GetCtrlSourceStr <> "" Then
       wCtrl.Value = Range(wCtrl.ControlSource).Value
    wCtrl.BackColor = Range(wCtrl.ControlSource).Interior.Color
       End If

   Next wCtrl

End Sub

' add to activate Private Sub UserForm_Activate()

   ReGetCtrlSources

   End Sub