UserForm help
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 |
UserForm help
your code works fine for me. What else can you tell us?
Bob Umlas "RJ" wrote in message ... 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 |
UserForm help
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) "RJ" wrote in message ... 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 |
All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com