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

Realized I have forgotten to upvote you :) +1

bonCodigo
– bonCodigo

2013-01-22 18:54:36 +00:00
Commented Jan 22, 2013 at 18:54 — bonCodigo
– bonCodigo, Commented Jan 22, 2013 at 18:54

bonCodigo · Accepted Answer · 2013-01-22 17:05:25Z

2

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

edited Jan 22, 2013 at 17:05

answered Jan 22, 2013 at 16:57

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

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

3 Comments

Yulia V Over a year ago

Changing the textBox value from VBA (using TextBox_MynDate.Value = line) does not seem to change the cell value, otherwise I would have had an infinite recursion I guess.

Yulia V Over a year ago

worksheet_change event seems to be the only way to avoid hardcoding the cell address, but I have ended up hardcoding it because, in my case, it is simpler.

bonCodigo Over a year ago

@YuccaV to solve the infinite loop with Sheet change event you may set Events to false.

Harry S · Accepted Answer · 2017-03-01 07:00:33Z

   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

Collectives™ on Stack Overflow

How to force ControlSource updating (excel userforms)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related