Combo Box not clearing

[bluewaterfree]

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

 

[Michael M]

Have you tried using

.SelectedMonth.value = ""

 

[bluewaterfree]

.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]

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]

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]

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]

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

That solved it!!!

 

[Zot]

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]

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.