Retaining Values in Userform when called a second time
 

I have a userform which I load values into via a combobox as follows below.
I want to load these values the first time the user calls the form up. After
load the form, the user then picks an item from the box and I pass this to a
public variable via the combobox change event. The user is then prompted
away from this form and then brought back later. Now when the user comes
back to this form again, I want to bypass the loading of the combobox but
instead retain the value from the public variable. How do I do this? I am
assuming an If stmt which tests to see if this is the first time the form has
been called or would you use a class module for this?



Private Sub UserForm_Initialize()
Dim pg As Control
Dim sht As Worksheet

With UserForm1.ComboBox1
.AddItem "Black"
.AddItem "Blue"
.AddItem "Red"
.AddItem "Green"
.AddItem "Yellow"
.AddItem "Orange"
.AddItem "White"
End With
End Sub

Private Sub ComboBox1_Change()
cmblistvalue = ComboBox1.Value
UserForm2.Label1.Caption = cmblistvalue
End Sub

Retaining Values in Userform when called a second time
 

I would think that using UserForm.Hide instead of Unload UserForm would allow
its immediate use without reinitializing the controls. Check out the Hide
method in VBA help.

"ExcelMonkey" wrote:

I have a userform which I load values into via a combobox as follows below.
I want to load these values the first time the user calls the form up. After
load the form, the user then picks an item from the box and I pass this to a
public variable via the combobox change event. The user is then prompted
away from this form and then brought back later. Now when the user comes
back to this form again, I want to bypass the loading of the combobox but
instead retain the value from the public variable. How do I do this? I am
assuming an If stmt which tests to see if this is the first time the form has
been called or would you use a class module for this?



Private Sub UserForm_Initialize()
Dim pg As Control
Dim sht As Worksheet

With UserForm1.ComboBox1
.AddItem "Black"
.AddItem "Blue"
.AddItem "Red"
.AddItem "Green"
.AddItem "Yellow"
.AddItem "Orange"
.AddItem "White"
End With
End Sub

Private Sub ComboBox1_Change()
cmblistvalue = ComboBox1.Value
UserForm2.Label1.Caption = cmblistvalue
End Sub

Retaining Values in Userform when called a second time
 

Yup that did it. THanks

"JLGWhiz" wrote:

I would think that using UserForm.Hide instead of Unload UserForm would allow
its immediate use without reinitializing the controls. Check out the Hide
method in VBA help.

"ExcelMonkey" wrote:

I have a userform which I load values into via a combobox as follows below.
I want to load these values the first time the user calls the form up. After
load the form, the user then picks an item from the box and I pass this to a
public variable via the combobox change event. The user is then prompted
away from this form and then brought back later. Now when the user comes
back to this form again, I want to bypass the loading of the combobox but
instead retain the value from the public variable. How do I do this? I am
assuming an If stmt which tests to see if this is the first time the form has
been called or would you use a class module for this?



Private Sub UserForm_Initialize()
Dim pg As Control
Dim sht As Worksheet

With UserForm1.ComboBox1
.AddItem "Black"
.AddItem "Blue"
.AddItem "Red"
.AddItem "Green"
.AddItem "Yellow"
.AddItem "Orange"
.AddItem "White"
End With
End Sub

Private Sub ComboBox1_Change()
cmblistvalue = ComboBox1.Value
UserForm2.Label1.Caption = cmblistvalue
End Sub