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

0 votes

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
Mar 30, 2023 in Others by Kithuzzz
• 38,000 points
511 views

1 answer to this question.

0 votes
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.
answered Mar 30, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

'Microsoft.Office.Interop.Excel.Range' does not contain a definition for 'get_Default'

You are using C# version 4, the ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,600 points
2,246 views
0 votes
1 answer

Excel VLOOKUP where the key is not in the first column

INDEX/MATCH will do it in any direction of ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,600 points
1,507 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
796 views
0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,000 points
396 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,259 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,696 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
972 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,089 views
0 votes
1 answer

IF statement for a tax sheet does not work

Use vlookup() as that is much easier ...READ MORE

answered Mar 20, 2023 in Others by narikkadan
• 63,600 points
541 views
0 votes
1 answer

JavaScript API does not work for Excel 2013?

Each method in the Office.js APIs is ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,600 points
796 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP