UserForm help

[RJ]

I have a workbook that creates additional worksheets (added on at the end of
the worksheets.) The first 3 worksheets in the workbook are used in the code
to create the additional worksheets.

I have a userform that is used to export the additional worksheets. This
userform contains a combo box that is filled with the additional worksheets
(sheets 4 and on.) When the userform loads, I have worksheets 4 and and
beyond's names saved to an array which fills the combo box. However after I
add new worksheets and launch the userform the additional worksheets are not
showing up on the combo box. If I save the workbook and re-open it then the
combo box will recognize the new worksheet names. I have attached my code
below. Any help is much appreciated....

Private Sub UserForm_Initialize()
Dim sheetnames() As String
Dim totalsheets As Integer
Dim sheet As Worksheet
Dim i As Integer

totalsheets = ActiveWorkbook.Worksheets.Count - 3
ReDim sheetnames(totalsheets)

For i = 1 To totalsheets
sheetnames(i - 1) = ActiveWorkbook.Worksheets(i + 3).Name
Next

For i = 0 To totalsheets - 1
UserForm1.ComboBox1.AddItem sheetnames(i)
Next
End Sub

Thanks,
Ray

 

[Bob Umlas]

your code works fine for me. What else can you tell us?
Bob Umlas

 

[Chip Pearson]

RJ,

The problem may well be that you have the code in the Initialize event of
the user form. This procedure is run when the form is loaded into memory. If
you subsequently Hide the form and then Show it again (without Unloading the
form between the Hide and Show), it is already in memory and therefore the
Initialize event won't run again. I would take the code out of Initialize
and move it to a Public procedure within the form, say "LoadComboBox", and
then call that method prior to Showing the form.

E.g., in the code that Shows the form:

UserForm1.LoadComboBox()
UserForm1.Show

In the form's code module:

Public Sub LoadComboBox() ' Must be Public, not Private
Me.ComboBox1.Clear
' code to load the ComboBox1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)