Search code examples
vbauserformsetfocus

Userform .setfocus and _Change events

I created a Userform consisting of a textbox and drop-down menu. The idea is for users to complete the textbox, and hit "save", which pastes the value entered onto a corresponding cell in the excel workbook.

Since the information entered in the drop-down menu can change over time, I formatted the textbox to display when last the field was changed by a user.

To do this, I created a _CHANGE event on the drop-down menu, and specified that if the selection changes, the textbox must apply = NOW, which consequently shows the date and time. This works fine in itself.

The problem arises when the USERFORM is initialized. Its code contains a .SETFOCUS for the drop-down menu to display its existing value. It appears as if the .SETFOCUS on the USERFORM_INITIALISE act as _CHANGE event on the drop-down menu, which causes it to edit the date and time before a user actually changes the field.

How can I go about to change this?

Private Sub drop_down_menu_change()
        textbox.Value = Now
End Sub

Private Sub UserForm_Initialize()

drop_down_menu.AddItem "1"
drop_down_menu.AddItem "2"
drop_down_menu.AddItem "3"
drop_down_menu.AddItem "4"
drop_down_menu.AddItem "5"

drop_down_menu.SetFocus

  End Sub

Below is a screenshot of the userform as well as the code:

enter image description here

Solution

  • During the Initialize event, it is normal to trigger the Change event when the code adds items...

    You can overpass the problem creating a Boolean variable, able to make the event to work only after form initialization. Something like this:

    1. Declare a variable in the top form code module area (declarations area):
    Private noEvent As Boolean
    1. In the initialization event use it in this way:
    Private Sub UserForm_Initialize()
   noEvent = True

   drop_down_menu.AddItem "1"
   drop_down_menu.AddItem "2"
   drop_down_menu.AddItem "3"
   drop_down_menu.AddItem "4"
   drop_down_menu.AddItem "5"

   drop_down_menu.SetFocus
    
    noEvent = False
End Sub
    1. And the event should be transformed in:
    Private Sub drop_down_menu_change()
   if not noEvent Then textbox.Value = Now
End Sub

    Unfortunately, EventsEnabled = False does not work as it should, for the form controls...