Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Hello everyone. Thanks for all your help in the past, and hopefully
someone will be able to get me pointed in the right direction with this problem too. I have a userform containing a listbox. the listbox is loaded with the sheetnames of the workbook. How can I know when/if the user changes the worksheet names, so that I will then know to update the values of the listbox so that the listbox always contains the current list of sheetnames? Any help is greatly appreciated. Thanks in advance, and happy programming! Troy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Is the userform being shown modally? If so, why not just load the list each
time you show the form? Tim "tr00per" wrote in message oups.com... Hello everyone. Thanks for all your help in the past, and hopefully someone will be able to get me pointed in the right direction with this problem too. I have a userform containing a listbox. the listbox is loaded with the sheetnames of the workbook. How can I know when/if the user changes the worksheet names, so that I will then know to update the values of the listbox so that the listbox always contains the current list of sheetnames? Any help is greatly appreciated. Thanks in advance, and happy programming! Troy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Hi Troy,
Add the following code to your UserForm_Initialize(). Everytime you open the userform the listbox will updated with current worksheet names. Enjoy... Rick Option Explicit Private Sub UserForm_Initialize() Dim wsht As Worksheet Me.ListBox1.Clear '' clear old ListBox '' reintialize listbox For Each wsht In Worksheets Me.ListBox1.AddItem wsht.Name Next wsht End Sub "tr00per" wrote in message oups.com... Hello everyone. Thanks for all your help in the past, and hopefully someone will be able to get me pointed in the right direction with this problem too. I have a userform containing a listbox. the listbox is loaded with the sheetnames of the workbook. How can I know when/if the user changes the worksheet names, so that I will then know to update the values of the listbox so that the listbox always contains the current list of sheetnames? Any help is greatly appreciated. Thanks in advance, and happy programming! Troy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Thanks for the code suggestions. Those are great ways to initially add
the worksheet names to the listbox, and i use similar code. However, since i'm using vbModeLess when showing the UserForm, the user has the ability to interact with the Excel workbook and/or the UserForm as the user chooses. Unfortunately, this is where the problem arises. By allowing the user to interact with the Excel workbook, the user can of course choose to change a worksheet name. I haven't found any sort of worksheet or workbook event that fires for this particular action (changing the worksheet name). I've even tried using a function in a worksheet cell (name: sheetname_cell) that calculates the worksheet name each time it's changed. And again, I didn't really find a good use for that becuase, although the cell value would change to the new worksheet name correctly, no event would fire as a result of the cell changing its value. Or at least...even when the calculate event would fire, i had no way of knowing which cell on the worksheet had actually calculated (the Worksheet_Calculate event doesn't give a Target parameter.) Worksheet_Change provides a Target parameter, but in this case, the Worksheet_Change event doesn't fire, since i have a function in the cell - ahh.....the ol' Catch-22. To solve this problem, as is, i think i need one of two things: a) I need a Worksheet_NameChanged event (of sorts), or a reasonable work-around b) I need to be able to target cells after they calculate (like the Worksheet_Change event does). I just thought of this too: Is there a way to run a sub/function when the user returns focus to the UserForm? If so, then maybe each time focus is returned from the workbook to the UserForm, i could update the values of listbox then - instead of after each Worksheet_Calculate event. One thing that i want to avoid is cross-checking the sheetname_cell with the actual sheet name each time a Worksheet_Calculate event is fired - because i do lots and lots of calculations in this particular project. That would greatly increase the operating time of the code. Then again, i haven't tested this yet, so maybe that's the next thing to try. Maybe the time required to cross-check the sheetname_cell with the actualy sheet name won't even be noticeable to the user between each calculation. The other thing i could do - make the UserForm modal - not allow access to the workbook until the user is done with the UserForm. Then again, that's not really how i originally intended for this project to work. Phew...I hope this additional information is helpful. Again, thank you all for your posts and emails. Fingers-crossed that we can come up with a good-enough solution. I'm going to go work on the "update listbox when focus is returned to the UserForm" idea. Happy programming. Troy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Since there's no event for changing a sheet name you're stuch with using a
workaround. You might consider running the "update sheet names" routine repeatedly - maybe using application.ontime - every 10 seconds or so. Only refesh the list if one of the names is different (store the names in an array as well as in the listbox). Shouldn't be noticeable to the user. Tim. "tr00per03" wrote in message ps.com... Thanks for the code suggestions. Those are great ways to initially add the worksheet names to the listbox, and i use similar code. However, since i'm using vbModeLess when showing the UserForm, the user has the ability to interact with the Excel workbook and/or the UserForm as the user chooses. Unfortunately, this is where the problem arises. By allowing the user to interact with the Excel workbook, the user can of course choose to change a worksheet name. I haven't found any sort of worksheet or workbook event that fires for this particular action (changing the worksheet name). I've even tried using a function in a worksheet cell (name: sheetname_cell) that calculates the worksheet name each time it's changed. And again, I didn't really find a good use for that becuase, although the cell value would change to the new worksheet name correctly, no event would fire as a result of the cell changing its value. Or at least...even when the calculate event would fire, i had no way of knowing which cell on the worksheet had actually calculated (the Worksheet_Calculate event doesn't give a Target parameter.) Worksheet_Change provides a Target parameter, but in this case, the Worksheet_Change event doesn't fire, since i have a function in the cell - ahh.....the ol' Catch-22. To solve this problem, as is, i think i need one of two things: a) I need a Worksheet_NameChanged event (of sorts), or a reasonable work-around b) I need to be able to target cells after they calculate (like the Worksheet_Change event does). I just thought of this too: Is there a way to run a sub/function when the user returns focus to the UserForm? If so, then maybe each time focus is returned from the workbook to the UserForm, i could update the values of listbox then - instead of after each Worksheet_Calculate event. One thing that i want to avoid is cross-checking the sheetname_cell with the actual sheet name each time a Worksheet_Calculate event is fired - because i do lots and lots of calculations in this particular project. That would greatly increase the operating time of the code. Then again, i haven't tested this yet, so maybe that's the next thing to try. Maybe the time required to cross-check the sheetname_cell with the actualy sheet name won't even be noticeable to the user between each calculation. The other thing i could do - make the UserForm modal - not allow access to the workbook until the user is done with the UserForm. Then again, that's not really how i originally intended for this project to work. Phew...I hope this additional information is helpful. Again, thank you all for your posts and emails. Fingers-crossed that we can come up with a good-enough solution. I'm going to go work on the "update listbox when focus is returned to the UserForm" idea. Happy programming. Troy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
Well, that's not really how i intended for the project to play out. In
addition to all that i said in my last post, i suppose this is one more thing that i want to avoid - using a timer that would have to occur quite frequently in order to keep the listbox entries as accurate as i'd like. It just seems that there must be at least a workaround for this sort of problem. I put a lot of time into a workaround solution last night, and i think i may be on to something. I look forward to future suggestions, and hopefully -if not me- someone will eventually post a suitable solution. Thank again, Troy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to update Listbox of sheetnames if user changes sheetnames?
SOLUTION CODE:
ThisWorkbook: Private Sub Workbook_Open() ''show UserForm1 when workbook opens UserForm1.Show End Sub Public Sub DoStuff() Dim RCGB As Boolean ''used as local RUN_CODE_GLOBAL_BOOLEAN value Dim LB_List As Variant ''used as array of items in UserForm1.ListBox1 Dim Change As Boolean ''used to express whether worksheet pages have changed Dim arrayIndex As Integer ''used to increment through LB_List and sheet names Dim listIndex As Integer ''used to store users current ListBox1.ListIndex ''get value for RCGB (ThisWorksheet.RUN_CODE_GLOBAL_BOOLEAN) RCGB = Module1.getRCGB ''set Change = True (default) Change = True ''check to see if worksheet pages (count,order,etc) have changed since last update '' only if the count of sheets = count of items in LB_List b/c otherwise, '' a change has definitely occurred If UserForm1.ListBox1.ListCount = Sheets.Count Then ''fill LB_List with values in UserForm1.ListBox LB_List = Module1.PopulateArrayWithListBox(UserForm1.ListBox 1) ''increment through the array items and compare each value with sheet name For arrayIndex = 0 To UserForm1.ListBox1.ListCount - 1 ''if values are equal, set change to false and check remaining values If LB_List(arrayIndex) = Sheets(arrayIndex + 1).name Then Change = False Else ''if any value is not equal, set change to True & Exit For Change = True Exit For End If Next arrayIndex End If ''To reduce number of updates, '' only update ListBox1 when RCGB = True If RCGB = True Then '' also, only update ListBox1 when Change = True If Change = True Then ''get user's current ListBox1.listIndex - if none select, then select 0 If UserForm1.ListBox1.listIndex < 0 Then listIndex = 0 Else listIndex = UserForm1.ListBox1.listIndex End If 'clear ListBox1 UserForm1.ListBox1.Clear 'add sheet names to ListBox1 For arrayIndex = 0 To Sheets.Count - 1 UserForm1.ListBox1.AddItem (Sheets(arrayIndex + 1).name) Next arrayIndex ''re-select user's selection '' this code could be better, suggest: use value instead of index UserForm1.ListBox1.listIndex = listIndex UserForm1.ListBox1.Selected(listIndex) = True End If ''call DoStuff again to detect future changes user may make Application.OnTime Now + TimeValue("00:00:02"), "ThisWorkbook.DoStuff" End If End Sub UserForm1 CODE: Private Sub MultiPage1_Change() Dim page As Integer page = MultiPage1.value ''start DoStuff if page = 1 If page = 1 Then Module1.setRCGB (True) Application.OnTime Now, "ThisWorkbook.DoStuff" ''stop DoStuff if page < 1 Else Module1.setRCGB (False) Application.OnTime Now, "ThisWorkbook.DoStuff", False End If End Sub Private Sub UserForm_Initialize() ''start MultiPage1 on page 0 (first page tab) MultiPage1.value = 0 ''set RUN_CODE_GLOBAL_BOOLEAN = True (ThisWorkbook.) Module1.setRCGB (True) End Sub Module1 CODE: Dim RUN_CODE_GLOBAL_BOOLEAN As Boolean Public Sub setRCGB(tf As Boolean) RUN_CODE_GLOBAL_BOOLEAN = tf End Sub Public Function getRCGB() As Boolean getRCGB = RUN_CODE_GLOBAL_BOOLEAN End Function Public Function PopulateArrayWithListBox(inListBox As Variant) Dim arrayIndex As Integer Dim tempArray As Variant ReDim tempArray(inListBox.ListCount) For arrayIndex = 0 To UBound(tempArray) - 1 tempArray(arrayIndex) = inListBox.List(arrayIndex) Next arrayIndex PopulateArrayWithListBox = tempArray End Function Description: I created a userform (UserForm1) that contains a MultiPage (MultiPage1) with two pages (Page1 & Page2). Page1 contains nothing. Page2 has a ListBox control (ListBox1). Some of the listbox properties are very important: MultiSelect = 0 - fmMultiSelectSingle. With this code, the UserForm will display upon opening the workbook. When the user selects Page2, the listbox is updated with the list of current workbook sheet names. As long as Page2 is selected, listbox will continue to update (approximately every 2 seconds...code: Application.OnTime.... To minimize updating, updates will not occur when sheets in the workbook have not changed since last update...code: DoStuff sets change = True if the sheet names, order, etc have changed. There were many challenges in getting this project to work correctly, but as you can see, the code is not necessarily pretty/concise/etc; However, it should suffice as a workaround to the problem of displaying and updating a listbox of workbook page names. If you have any questions, please contact me. Also, if you would like to have a copy of this workbook example emailed to you, I can do that as well. One more solution - so many more problems. Happy programming. Troy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Sheetnames when names are Months | Excel Discussion (Misc queries) | |||
Allow relative referencing for imbedded sheetnames in formulas | Excel Worksheet Functions | |||
read sheetnames with ADO | Excel Programming | |||
can we copy the sheetnames too? | Excel Programming | |||
User form with a listbox | Excel Programming |