A button in excel sheet does not work for the first time

Question

I created the code listed below for a button and added it to an Excel spreadsheet. I also have a form (userform1). The listbox2 and textbox16 do not change when I click the button for the first time; but, when I click it again, they do. Do I have any issues with my code? (I inserted "load userform1" or other codes, but this is the first time that they haven't worked.)

Private Sub CommandButton1_Click()

UserForm1.MultiPage1.Value = 2 

UserForm1.Show

UserForm1.ListBox2.Selected(1) = True 

UserForm1.TextBox16.Value = 84

End Sub

Answer 1

When you press the Display button, the form is shown in modal mode, which means it has the focus and you cannot return to Excel at that time (or any other open form). As a result, the Sub is likewise cut off.

Even if the form is closed or hidden, it keeps running. So, you must show the form before specifying the values for the listbox and textbox (and the user closed it again). The fields appear to have been correctly set when the code is run a second time, however this is false; instead, the fields received the values from the initial call. Setting different values for every call will demonstrate that.

Solution: Move the Show command at the end of your sub, after you have set all form fields.

As an alternative, display the form in non-modal mode: False for UserForm1. In that situation, even if the form is already displayed, the Sub will be fully run. The user will be able to move between Excel and the Form if the form is displayed non-modally, which could be useful. Nevertheless, you must carefully consider what should happen if the user pushes the button on the sheet once again.