Refreshing User Form Textbox with VBA

Question

I have a UserForm that has a textbox that is populated from the =Now() function. However the data I am tracking runs on a 3 shift schedule, so "3rd shift" will technically be entering their data on next day. I have adjusted for this using an if statement, but my problem is getting my textbox to update on the change of a combo box that allows you to select what shift you're entering for. I tried the DoEvents function at the suggestion from another helpsite, but it did not work. Thanks in advance!

Private Sub date_txtb_Change() If shift_cbox.Text = "Shift 1" Then date_txtb.Text = Format(Now(), "MM/DD/YY") 'Current Date DoEvents

ElseIf shift_cbox.Text = "Shift 2" Then
date_txtb.Text = Format(Now(), "MM/DD/YY") 'Current Date
DoEvents

ElseIf shift_cbox.Text = "Shift 3" Then
date_txtb.Text = Format(Now() - 1, "MM/DD/YY") 'Current Date -1
DoEvents

Else
'do nothing'

End If

End Sub.

have you tried any of the solution below ? any feedback ?

Shai Rado
– Shai Rado

2017-05-31 18:59:07 +00:00
Commented May 31, 2017 at 18:59 — Shai Rado
– Shai Rado, Commented May 31, 2017 at 18:59

Shai Rado · Accepted Answer · 2017-05-31 13:05:56Z

1

You need to enter your code on the Change event of your Combo-Box, also you can use Select Case to have a clearer and shorter code, which will also allow you more versatility to add more scenarios in the future (if you needed to).

Private Sub shift_cbox_Change()

Select Case shift_cbox.Value
    Case "Shift 1", "Shift 2"
        date_txtb.Text = Format(Now(), "MM/DD/YY") 'Current Date

    Case "Shift 3"
        date_txtb.Text = Format(Now() - 1, "MM/DD/YY") 'Current Date -1

    Case Else
        'do nothing , or something for future scenario

End Select

End Sub

answered May 31, 2017 at 13:05

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

Comments

Plagon · Accepted Answer · 2017-05-31 13:02:24Z

0

You are using the wrong object. Instead of Private Sub date_txtb_Change() you should use Private Sub shift_cbox_Change().

answered May 31, 2017 at 13:02

Plagon

3,1651 gold badge14 silver badges27 bronze badges

1 Comment

accortdr Over a year ago

Thank you very much that worked! Still new to VBA so I appreciate the help

Collectives™ on Stack Overflow

Refreshing User Form Textbox with VBA

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