Thread: 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
tr00per03@yahoo.com

Is the userform being shown modally? If so, why not just load the list each
time you show the form?

Tim


"tr00per" <tr00per03@yahoo.com> wrote in message
news:1139117445.615059.286330@o13g2000cwo.googlegroups.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
> tr00per03@yahoo.com
>

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" <tr00per03@yahoo.com> wrote in message
news:1139117445.615059.286330@o13g2000cwo.googlegroups.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
> tr00per03@yahoo.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
tr00per03@yahoo.com

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" <tr00per03@yahoo.com> wrote in message
news:1139165661.497624.4490@g43g2000cwa.googlegroups.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
> tr00per03@yahoo.com
>

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
tr00per03@yahoo.com

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.ListBox1)
''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
tr00per03@yahoo.com

Oops...bad news: i found an additional problem. The good news is that
i'll post the solution.

THE PROBLEM:
The code above works fine to update the list of worksheets when the
user adds or deletes worksheets or changes the order of existing
worksheets. However, the code does not function as expected when the
user changes the name of existing worksheets. For example, if the user
was to right click the worksheet tab and choose rename then rename the
sheet...or if the user just double-clicks the worksheet tab, renames
the sheet, and presses enter. In those examples, the listbox would not
be updated to reflect the sheet name change. In fact, no further
updates will occur. For whatever reason it seems like changing the
sheet name in those ways kills the OnTime calls.

THE SOLUTION:
***(Add this function to a cell on the worksheet)***
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

***(Add this Function to Module1)***
Public Function PagesHaveChanged() As Boolean
Dim tempChange As Boolean
Dim LB_List As Variant
Dim arrayIndex As Integer

''set Change = True (default)
tempChange = 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.ListBox1)

' PrintArrayToMsgBox (LB_List)

''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
tempChange = False
Else ''if any value is not equal, set change to True & Exit
For
tempChange = True
Exit For
End If
Next arrayIndex
End If

PagesHaveChanged = tempChange
End Function

***(Add this Sub to each worksheet code page)***
Private Sub Worksheet_Calculate()
If Module1.PagesHaveChanged Then
Application.OnTime Now + TimeValue("00:00:02"),
"ThisWorkbook.DoStuff"
End If
End Sub

***(Change Sub DoStuff() in ThisWorkbook to the following)***
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 (Module1.PagesHaveChanged)
change = Module1.PagesHaveChanged

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

And that oughta do it. How it works: each time the sheet name is
changed, the sheet now has a cell that updates to reflect the sheet
name. The auto-update of that cell fires the Worksheet_Calculate
event. So, Worksheet_Calculate calls DoStuff() to continue the updates
of the listbox.

If anyone has questions about this code, please let me know. I can
send an example file with the functioning code.

Troy
tr00per03@yahoo.com