Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Userform to enter values and shown in same userform in list | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming | |||
Access from add_in userform to main template userform.... | Excel Programming |