My List box data does not show when I open the user form

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
I created a user form and inserted a list box. When I open my user form, the data in the list box does not appear unless I click the "Cancel" button. I am unsure where I went wrong because I followed the instructions on Excel Easy

Here is my code:
Private Sub UserForm1_Initialize()
'code to empty textboxes, fill listboxes, uncheck checkboxes

'empty Date textbox1
TextBox1.Value = ""

'empty Code textbox2
TextBox2.Value = ""

'empty serial no. textbox3
TextBox3.Value = ""

'empty issues listbox1
ListBox1.Clear

'empty repair listbox2
ListBox2.Clear

'fill problems/issues listbox1
With ListBox1
.AddItem "Calibrate"
.AddItem "Unable to Alternate"
.AddItem "Broken solenoid head"
.AddItem "Faulty Motor"
.AddItem "Low Pressure"
.AddItem "Damaged Casing"
.AddItem "Buttons unable to be pressed"
.AddItem "Damaged Screw tracts"
.AddItem "Loose air outlet"
.AddItem "Loose ON/OFF panel"
.AddItem "Damaged back panel"
.AddItem "Damaged ON/OFF mains'"
.AddItem "Loose ON/OFF mains"
.AddItem "Conduct PM"
.AddItem "No Issue"
.AddItem "Others"
End With

'fill repair listbox2
With ListBox2
.AddItem "Calibrate"
.AddItem "Reconnect pressure tube"
.AddItem "Replace solenoid"
.AddItem "Replace solenoid head"
.AddItem "Replace motor"
.AddItem "Relace casing"
.AddItem "Readjust Buttons"
.AddItem "Hot Glue screw tracts"
.AddItem "Replace front panel"
.AddItem "Replace back panel"
.AddItem "Tightened air outlet"
.AddItem "Secure ON/OFF with hot glue"
.AddItem "Replace back casing"
.AddItem "Replace ON/OFF mains"
.AddItem "Conduct PM and ESA test"
.AddItem "No Issue"
.AddItem "Others"
End With

'empty remarks textbox5
TextBox5.Value = ""

'uncheck engineer checkboxes
CheckBox1.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
CheckBox5.Value = False
CheckBox6.Value = False
CheckBox7.Value = False


End Sub

Private Sub CommandButton1_Click()
'code to transfer userform data inputs to service_records excel sheet


End Sub

Private Sub CommandButton2_Click()
'code to clear userform

Call UserForm1_Initialize

End Sub

Private Sub CommandButton3_Click()
'code to cancel userform

Unload Me

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
I think it should be:
Private Sub UserForm_Initialize()

Not
Private Sub UserForm1_Initialize()

Take out the 1
 
Solution

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hello, thank you for your reply! I tried your suggestion and it worked! Thank you! But I am curious why my code does not work if the "1" is in it. I have multiple user forms so and they are labelled "UserForm1" etc. so shouldn't the "1" be necessary when programming the particular userform?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
Hello, thank you for your reply! I tried your suggestion and it worked! Thank you! But I am curious why my code does not work if the "1" is in it. I have multiple user forms so and they are labelled "UserForm1" etc. so shouldn't the "1" be necessary when programming the particular userform?
I think it's because this is the active Userform so no need naming userform.
But I don't always know why?

And why on opening the Userform do you think you need to clear the Checkboxes.
If you close the userform like you showed:
Unload Me

All those checkboxes are automatically unchecked.
When you unload a userform all the controls are cleared. No need running code to clear userform
 

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
I think it's because this is the active Userform so no need naming userform.
But I don't always know why?

And why on opening the Userform do you think you need to clear the Checkboxes.
If you close the userform like you showed:
Unload Me

All those checkboxes are automatically unchecked.
When you unload a userform all the controls are cleared. No need running code to clear userform
I uncheck the checkboxes so that the users can check it when filling out the user form
 

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
I think it should be:
Private Sub UserForm_Initialize()

Not
Private Sub UserForm1_Initialize()

Take out the 1
After removing the "1", I am now unable to clear the contents in my user form when I click the "Clear" button
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
Well like I said before if you close the userform using Unload you do not need to clear the userform

Did you try removing all that clearing code and see what happens.
All those controls clear automatically.

And when you open a userform all the checkboxes are not checked.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
If you want to use the code you got from Excel Easy and do all the clearing then that's OK with me It's not for me to tell you what to do. But I gave you the answer that worked for you. If you now have clearing code that does not work. That's another issue. I have never tried to clear out a userform and then unload it. And like Call UserForm1_Initialize

I have never used such code so have no ideal what that would do.

I just unload the form and move on. Now if you use Hide to hide the userform that way does not clear all the controls.
 

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
If you want to use the code you got from Excel Easy and do all the clearing then that's OK with me It's not for me to tell you what to do. But I gave you the answer that worked for you. If you now have clearing code that does not work. That's another issue. I have never tried to clear out a userform and then unload it. And like Call UserForm1_Initialize

I have never used such code so have no ideal what that would do.

I just unload the form and move on. Now if you use Hide to hide the userform that way does not clear all the controls.
I am not quite sure I understand the part about using "Hide" to hide the user form. Could you explain to me?

I used a clearing code to allow users to click a "Clear" command button to clear the user form. In my user form, I have 3 buttons: "OK", "CLEAR", and "CANCEL". The "OK" command button is to send the data input by user in the user form to another worksheet (no issue with this). The "CLEAR" command button is to allow users to clear the contents they have typed in if there is a need to. Then the "CANCEL" command button is to allow user to close the user form regardless. Both "OK" and "CANCEL" command buttons work, its just the "CLEAR" command button that does not work. I hope my explanation makes sense
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
You will need to use:
Private Sub UserForm_Initialize()

For both codes not
Private Sub UserForm1_Initialize()
 

Forum statistics

Threads
1,176,198
Messages
5,901,861
Members
434,925
Latest member
sanket6220

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top