UserForm

[RJ]

I have the following code for when a userform loads. The userform is opened
using a command button in the workbook. The code is supposed to take all of
the worksheets names in the workbook and load them into a combo box for the
user to select from. The first name in the combo box should be the fourth
sheet and every subsequesnt sheet in the in workbook thereafter. When I first
open the workbook the code seems to work fine...however if I add new
worksheets and open the userform using the command button the new worksheets
are not being displayed in the combobox. Any ideas? I have tried to clear the
array and clear the combo box up initialize, but neither attempt has worked.

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!!

 

[Dave Peterson]

You have a response at your other post.

I have the following code for when a userform loads. The userform is opened
using a command button in the workbook. The code is supposed to take all of
the worksheets names in the workbook and load them into a combo box for the
user to select from. The first name in the combo box should be the fourth
sheet and every subsequesnt sheet in the in workbook thereafter. When I first
open the workbook the code seems to work fine...however if I add new
worksheets and open the userform using the command button the new worksheets
are not being displayed in the combobox. Any ideas? I have tried to clear the
array and clear the combo box up initialize, but neither attempt has worked.

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!!