Combo Box not clearing

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Here's my code....
'REQUIRE ALL VARIABLES TO BE DECLARED
Option Explicit

'
' THIS MODULE CONTAINS MACROS ASSOCIATED WITH THE FINANCIALDISCLOSURE USERFORM
'
'
'......................................................................................................................................
'......................................................................................................................................
Private Sub UserForm_Initialize()
' Used in Financial Disclosure to convert investment data into 278e and 278t Financial Disclosure Reports
' Initiated by Financial DIsclosure Button on Ribbon
'
Dim i As Long ' Loop Counter
'
'......................................................................................................................................
With FinancialDisclosureUserForm
.SelectedMonth.Clear
.SelectedYear.Clear
.Label1.Font.Size = 12
.Label2.Font.Size = 12
For i = 1 To 12
SelectedMonth.AddItem (i) ' Add the month numbers to the Combo Box SelectedMonth
Next
SelectedMonth.AddItem ("Exit") ' Finally, add "Exit" to the Combo Box to allow user to exit
For i = 2020 To 2025
SelectedYear.AddItem (i) ' Add the year numbers to the Combo Box SelectedYear
Next
SelectedYear.AddItem ("Exit") ' Finally, add "Exit" to the Combo Box to allow user to exit
End With

End Sub
Private Sub SelectedMonth_Change()
' Used in Financial Disclosure to convert investment data into 278e and 278t Financial Disclosure Reports
' Initiated by Financial DIsclosure Button on Ribbon
'
'......................................................................................................................................
If SelectedMonth.Text = "Exit" Then End ' If "Exit" was selected, quit the macro
Range("Q1").Value = "Monthly" ' Set cell Q1 to Report Type
Range("Q2").Value = SelectedMonth.Text ' Set cell Q1 to Report Parameter
Range("L1").Value = SelectedMonth.Text ' Set Month in Pivot Table
Range("L2").Value = "2020" ' Set Year in Pivit Table
FinancialDisclosureUserForm.Hide

End Sub
Private Sub SelectedYear_Change()
' Used in Financial Disclosure to convert investment data into 278e and 278t Financial Disclosure Reports
' Initiated by Financial DIsclosure Button on Ribbon
'
'......................................................................................................................................
If SelectedYear.Text = "Exit" Then End ' If "Exit" was selected, quit the macro
Range("Q1").Value = "Annual" ' Set cell Q1 to Report Type
Range("Q2").Value = SelectedYear.Text ' Set cell Q1 to Report Parameter
Range("L1").Value = "(All)" ' Set Month in Pivot Table
Range("L2").Value = SelectedYear.Text ' Set Year in Pivit Table
FinancialDisclosureUserForm.Hide

End Sub



First routine is the Initialization... as you can see the very first to lines are .SelectedMonth.Clear and .SelectedYear.Clear.... the names of the two Combo Boxes on UserForm FinancialDisclosureUserForm.

First time I run it, it works perfectly. The combo boxes load with blank showing... and the list I create below that. I select either a Month or a Year from the respective drop down... it then executes the proper report... life is great.

Second time I run it... the user form comes back with the previously selected value already selected?!?!?!?! What the? If I select a different value... it again executes the new report correctly.... but the old values are "retained"...

After a run, how do I get it to go back to showing "blank"... nothing selected?? My code "works" ... but it looks weird to have those old values being displayed.

Please and thanks.

Mark
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you tried using
VBA Code:
.SelectedMonth.value = ""
 

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
.SelectedMonth.value = ""
Yes... I put it right after the clear statements... no effect.

Just makes no sense to me.... I thought as "Private Subs" that once I "hid them" with the .Hide that all the variables would disappear.... I can't figure out how it's "remembering" to set the combo box to that value.

M
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
600
Office Version
  1. 2016
Platform
  1. Windows
Yes... I put it right after the clear statements... no effect.

Just makes no sense to me.... I thought as "Private Subs" that once I "hid them" with the .Hide that all the variables would disappear.... I can't figure out how it's "remembering" to set the combo box to that value.

M
Why not just re-initialized the userform? Run UserForm_Initialize again
 

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Why not just re-initialized the userform? Run UserForm_Initialize again
Isn't that what happens when I hit the ribbon button again? I guess that would explain what I'm seeing... if it's not running the initialize again.... let me go test that...
 

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Why not just re-initialized the userform? Run UserForm_Initialize again
And you would be correct!!! When I run the combo box the first time... it runs the Initialize procedure... but when run it the second time it does not....

I invoke the userForm by FinancialDisclosureUserForm.Show

I thought that "automatically" went to the Initialization routine... but apparently that's only true the first time. How do you force it to the initialization everytime?
 

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

You led me to the right answer..... instead of the .Hide.... I need to Unload FinancialDisclosureUserForm

That solved it!!!
 
Solution

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
600
Office Version
  1. 2016
Platform
  1. Windows
And you would be correct!!! When I run the combo box the first time... it runs the Initialize procedure... but when run it the second time it does not....

I invoke the userForm by FinancialDisclosureUserForm.Show

I thought that "automatically" went to the Initialization routine... but apparently that's only true the first time. How do you force it to the initialization everytime?

When you show, it will just unhide the userform. You can unload and load again but I guess that is probably not the best was.

I think you just run UserForm_Initialize again instead of FinancialDisclosureUserForm.Show.

I wonder why you initialized the FinancialDisclosureUserForm from UserForm, not from FinancialDisclosureUserForm_Initialize?
 

bluewaterfree

New Member
Joined
Dec 6, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Well... that's likely because I'm new to coding in VBA Excel and the only way I knew to invoke a userform was with the .show...... Load/Unload was something I just learned.

Thanks again for the help.... solved the problem and learned something new.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,203
Messages
5,600,291
Members
414,375
Latest member
Onmyown

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