Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Sheet names in a MenuBar menu
Thank you everyone for chiming in. My original arrangement is still the best
for my (admittedly limited) needs, so I will likely stick with it, but I'm glad it stimulated some discussion and I've learned a few new things along the way as well. And if my contribution helps someone else, so much the better. Long live newsgroups. Petr "Petr Danes" píše v diskusním příspěvku ... I recently started teaching in a software technical school, and my first course was in advanced Excel. Naturally, I prepared a workbook with lots of examples on various worksheets, but not until I started lecturing did I realize that lots of sheets mean lots of scrolling left and right to locate the correct sheet. I'd never needed a workbook with many sheets for any of my own projects, nor those I had created for others. The course went fairly well anyway, but I wanted a better way to navigate between sheets. Part of the idea came from ASAP Utilities' function of creating a separate index sheet with clickable hyperlinks for all sheets, and part of the code from John Walkenbach's Excel Power Programming book. (My thanks to you both.) The clickable index sheet function is handy, but solves only half of the problem - it quickly locates a sheet 'somewhere', but getting back to the index sheet for another shot out into the myriad of sheets is still a hassle. I decided a menu bar approach suited my needs better. The code reads in all the sheet names and creates a dropdown menu of sheet names. Originally it included a sort as well, but then I decided that I prefer the menu in the same order as the sheets in the workbook, and my naming and ordering scheme results in the names being in order anyway. The sort is still there, just the call commented out, in case someone wants to use it that way. There is no error checking and it takes no account of hidden properties of sheets, since I don't use that, but it could be added easily enough. The code is called from the Workbook_Open routine, so that the menu is freshly rebuilt every time the workbook opens. Probably not completely necessary, but the execution time is trivial and it makes for ease of use. I initially added a call from the Workbook_NewSheet event, but abandoned that since the code adds the default sheet name in use at the instant of creation (Sheet1 or some such), which is not very useful, since I always rename the sheet to something sensible, but by then the sheet has already been added to the menu with default name. If there were Workbook_SheetRename and Workbook_SheetDelete events, I would add calls there, as well as the NewSheet event. That would make it completely automatic and always current, but this works well enough. The first item on the menu, before the sheet names is a refresh call, so I can keep the menu up to date manually without having to close and re-open the workbook or switch to the VBA editor. There are two versions of the menu code, one creates a simple dropdown menu list, suitable for up to approximately thirty sheets on my machine (a bigger monitor would allow more), the second creates a two-stage list, grouped by the first character in the sheet name. I name my sheets by course day, so that "1.n xxxxx" is a sheet for the first day of the course, "2.n xxxxx" for the second day and so on. "0.n xxxxx" sheets are general sheets, not intended for any specific day. The second method obviously better handles a very large number of sheets, at the cost of a fraction of a second delay while the second tier unfolds. At the moment, my code creates both versions. I'll have to use them for a while to see which one I prefer for classroom use. Pete -- This e-mail address is fake, to keep spammers and their auto-harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. Start code (watch for line wrap!) ================================================== == Option Explicit Dim AEb As CommandBarButton Dim AEp As CommandBarPopup Dim AEp2 As CommandBarPopup Dim SheetNames() As String Sub SheetMenu() Dim i& ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count) ' Fill array with sheet names For i = 1 To UBound(SheetNames) SheetNames(i) = Sheets(i).Name Next i ' Sort the array in ascending order ' Not used in current implementation, ' but simply uncomment line below to order sheet names. 'BubbleSort SheetNames ' Build the sheet menus MakeMenuItem ' simple menu MakeMenuItem2 ' 2-tier menu End Sub Private Sub MakeMenuItem() ' Create the menu bar addition Dim i& With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With Next i End With End With End Sub Private Sub MakeMenuItem2() ' Create the menu bar addition Dim i&, nmhld$ ' Only one character is used for grouping nmhld = "!" With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets2").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets2" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) If nmhld < Left$(SheetNames(i), Len(nmhld)) Then Set AEp2 = .Controls.Add(msoControlPopup) AEp2.Caption = Left$(SheetNames(i), InStr(SheetNames(i), ".")) nmhld = Left$(SheetNames(i), Len(nmhld)) End If With AEp2 Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With End With Next i End With End With End Sub Sub BubbleSort(List() As String) ' Sorts the List array in ascending order Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub Public Sub GoToSheet() ' Action routine called by the menu ' Passed parameter is either the sheet name to call or the refresh command If CommandBars.ActionControl.Parameter = "(...Refresh...)" Then SheetMenu Else ActiveWorkbook.Sheets(CommandBars.ActionControl.Pa rameter).Activate End If End Sub |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Sheet names in a MenuBar menu
Hi Petr,
I had also developed one addins "workbook navigation" for my needs. If you are interested you can download it from below site. Some features: 1. Index of all Sheet Names in separate worksheet with hyperlink. 2. Sort sheets ascending or descending, Create\remove multiple windows. 3. Synchronies all the sheets to view same area in the worksheet. 4. Copy activesheet without truncated of cells having more then 255 characters. ...more + image in the webpage. http://in.geocities.com/shahshaileshs/ Regards, Shailesh Shah http://in.geocities.com/shahshaileshs/ If You Can't Excel with Talent, Triumph with Effort. Free Addins Office Menu-2003 for Office-2007 http://in.geocities.com/shahshaileshs/menuaddins "Petr Danes" wrote in message ... Thank you everyone for chiming in. My original arrangement is still the best for my (admittedly limited) needs, so I will likely stick with it, but I'm glad it stimulated some discussion and I've learned a few new things along the way as well. And if my contribution helps someone else, so much the better. Long live newsgroups. Petr "Petr Danes" píše v diskusním příspěvku ... I recently started teaching in a software technical school, and my first course was in advanced Excel. Naturally, I prepared a workbook with lots of examples on various worksheets, but not until I started lecturing did I realize that lots of sheets mean lots of scrolling left and right to locate the correct sheet. I'd never needed a workbook with many sheets for any of my own projects, nor those I had created for others. The course went fairly well anyway, but I wanted a better way to navigate between sheets. Part of the idea came from ASAP Utilities' function of creating a separate index sheet with clickable hyperlinks for all sheets, and part of the code from John Walkenbach's Excel Power Programming book. (My thanks to you both.) The clickable index sheet function is handy, but solves only half of the problem - it quickly locates a sheet 'somewhere', but getting back to the index sheet for another shot out into the myriad of sheets is still a hassle. I decided a menu bar approach suited my needs better. The code reads in all the sheet names and creates a dropdown menu of sheet names. Originally it included a sort as well, but then I decided that I prefer the menu in the same order as the sheets in the workbook, and my naming and ordering scheme results in the names being in order anyway. The sort is still there, just the call commented out, in case someone wants to use it that way. There is no error checking and it takes no account of hidden properties of sheets, since I don't use that, but it could be added easily enough. The code is called from the Workbook_Open routine, so that the menu is freshly rebuilt every time the workbook opens. Probably not completely necessary, but the execution time is trivial and it makes for ease of use. I initially added a call from the Workbook_NewSheet event, but abandoned that since the code adds the default sheet name in use at the instant of creation (Sheet1 or some such), which is not very useful, since I always rename the sheet to something sensible, but by then the sheet has already been added to the menu with default name. If there were Workbook_SheetRename and Workbook_SheetDelete events, I would add calls there, as well as the NewSheet event. That would make it completely automatic and always current, but this works well enough. The first item on the menu, before the sheet names is a refresh call, so I can keep the menu up to date manually without having to close and re-open the workbook or switch to the VBA editor. There are two versions of the menu code, one creates a simple dropdown menu list, suitable for up to approximately thirty sheets on my machine (a bigger monitor would allow more), the second creates a two-stage list, grouped by the first character in the sheet name. I name my sheets by course day, so that "1.n xxxxx" is a sheet for the first day of the course, "2.n xxxxx" for the second day and so on. "0.n xxxxx" sheets are general sheets, not intended for any specific day. The second method obviously better handles a very large number of sheets, at the cost of a fraction of a second delay while the second tier unfolds. At the moment, my code creates both versions. I'll have to use them for a while to see which one I prefer for classroom use. Pete -- This e-mail address is fake, to keep spammers and their auto-harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. Start code (watch for line wrap!) ================================================== == Option Explicit Dim AEb As CommandBarButton Dim AEp As CommandBarPopup Dim AEp2 As CommandBarPopup Dim SheetNames() As String Sub SheetMenu() Dim i& ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count) ' Fill array with sheet names For i = 1 To UBound(SheetNames) SheetNames(i) = Sheets(i).Name Next i ' Sort the array in ascending order ' Not used in current implementation, ' but simply uncomment line below to order sheet names. 'BubbleSort SheetNames ' Build the sheet menus MakeMenuItem ' simple menu MakeMenuItem2 ' 2-tier menu End Sub Private Sub MakeMenuItem() ' Create the menu bar addition Dim i& With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With Next i End With End With End Sub Private Sub MakeMenuItem2() ' Create the menu bar addition Dim i&, nmhld$ ' Only one character is used for grouping nmhld = "!" With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets2").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets2" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) If nmhld < Left$(SheetNames(i), Len(nmhld)) Then Set AEp2 = .Controls.Add(msoControlPopup) AEp2.Caption = Left$(SheetNames(i), InStr(SheetNames(i), ".")) nmhld = Left$(SheetNames(i), Len(nmhld)) End If With AEp2 Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With End With Next i End With End With End Sub Sub BubbleSort(List() As String) ' Sorts the List array in ascending order Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub Public Sub GoToSheet() ' Action routine called by the menu ' Passed parameter is either the sheet name to call or the refresh command If CommandBars.ActionControl.Parameter = "(...Refresh...)" Then SheetMenu Else ActiveWorkbook.Sheets(CommandBars.ActionControl.Pa rameter).Activate End If End Sub |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Sheet names in a MenuBar menu
Thank you Shah, it looks good, but my needs are fairly limited and the menu
bar approach I developed still suits me best. Nice job, though, you've obviously put some effort into it. If I may offer one small criticism, though, go easy on the color. It's easy to overdo it and lead people to assume that a difference in color has some additional significance, beyond what is visible in, for instance, a button's caption. Also, some color combinations make the text harder to read, especially on a lower-quality monitor. Petr "Shah Shailesh" píše v diskusním příspěvku ... Hi Petr, I had also developed one addins "workbook navigation" for my needs. If you are interested you can download it from below site. Some features: 1. Index of all Sheet Names in separate worksheet with hyperlink. 2. Sort sheets ascending or descending, Create\remove multiple windows. 3. Synchronies all the sheets to view same area in the worksheet. 4. Copy activesheet without truncated of cells having more then 255 characters. ..more + image in the webpage. http://in.geocities.com/shahshaileshs/ Regards, Shailesh Shah http://in.geocities.com/shahshaileshs/ If You Can't Excel with Talent, Triumph with Effort. Free Addins Office Menu-2003 for Office-2007 http://in.geocities.com/shahshaileshs/menuaddins "Petr Danes" wrote in message ... Thank you everyone for chiming in. My original arrangement is still the best for my (admittedly limited) needs, so I will likely stick with it, but I'm glad it stimulated some discussion and I've learned a few new things along the way as well. And if my contribution helps someone else, so much the better. Long live newsgroups. Petr "Petr Danes" píše v diskusním příspěvku ... I recently started teaching in a software technical school, and my first course was in advanced Excel. Naturally, I prepared a workbook with lots of examples on various worksheets, but not until I started lecturing did I realize that lots of sheets mean lots of scrolling left and right to locate the correct sheet. I'd never needed a workbook with many sheets for any of my own projects, nor those I had created for others. The course went fairly well anyway, but I wanted a better way to navigate between sheets. Part of the idea came from ASAP Utilities' function of creating a separate index sheet with clickable hyperlinks for all sheets, and part of the code from John Walkenbach's Excel Power Programming book. (My thanks to you both.) The clickable index sheet function is handy, but solves only half of the problem - it quickly locates a sheet 'somewhere', but getting back to the index sheet for another shot out into the myriad of sheets is still a hassle. I decided a menu bar approach suited my needs better. The code reads in all the sheet names and creates a dropdown menu of sheet names. Originally it included a sort as well, but then I decided that I prefer the menu in the same order as the sheets in the workbook, and my naming and ordering scheme results in the names being in order anyway. The sort is still there, just the call commented out, in case someone wants to use it that way. There is no error checking and it takes no account of hidden properties of sheets, since I don't use that, but it could be added easily enough. The code is called from the Workbook_Open routine, so that the menu is freshly rebuilt every time the workbook opens. Probably not completely necessary, but the execution time is trivial and it makes for ease of use. I initially added a call from the Workbook_NewSheet event, but abandoned that since the code adds the default sheet name in use at the instant of creation (Sheet1 or some such), which is not very useful, since I always rename the sheet to something sensible, but by then the sheet has already been added to the menu with default name. If there were Workbook_SheetRename and Workbook_SheetDelete events, I would add calls there, as well as the NewSheet event. That would make it completely automatic and always current, but this works well enough. The first item on the menu, before the sheet names is a refresh call, so I can keep the menu up to date manually without having to close and re-open the workbook or switch to the VBA editor. There are two versions of the menu code, one creates a simple dropdown menu list, suitable for up to approximately thirty sheets on my machine (a bigger monitor would allow more), the second creates a two-stage list, grouped by the first character in the sheet name. I name my sheets by course day, so that "1.n xxxxx" is a sheet for the first day of the course, "2.n xxxxx" for the second day and so on. "0.n xxxxx" sheets are general sheets, not intended for any specific day. The second method obviously better handles a very large number of sheets, at the cost of a fraction of a second delay while the second tier unfolds. At the moment, my code creates both versions. I'll have to use them for a while to see which one I prefer for classroom use. Pete -- This e-mail address is fake, to keep spammers and their auto-harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. Start code (watch for line wrap!) ================================================== == Option Explicit Dim AEb As CommandBarButton Dim AEp As CommandBarPopup Dim AEp2 As CommandBarPopup Dim SheetNames() As String Sub SheetMenu() Dim i& ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count) ' Fill array with sheet names For i = 1 To UBound(SheetNames) SheetNames(i) = Sheets(i).Name Next i ' Sort the array in ascending order ' Not used in current implementation, ' but simply uncomment line below to order sheet names. 'BubbleSort SheetNames ' Build the sheet menus MakeMenuItem ' simple menu MakeMenuItem2 ' 2-tier menu End Sub Private Sub MakeMenuItem() ' Create the menu bar addition Dim i& With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With Next i End With End With End Sub Private Sub MakeMenuItem2() ' Create the menu bar addition Dim i&, nmhld$ ' Only one character is used for grouping nmhld = "!" With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets2").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets2" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) If nmhld < Left$(SheetNames(i), Len(nmhld)) Then Set AEp2 = .Controls.Add(msoControlPopup) AEp2.Caption = Left$(SheetNames(i), InStr(SheetNames(i), ".")) nmhld = Left$(SheetNames(i), Len(nmhld)) End If With AEp2 Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With End With Next i End With End With End Sub Sub BubbleSort(List() As String) ' Sorts the List array in ascending order Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub Public Sub GoToSheet() ' Action routine called by the menu ' Passed parameter is either the sheet name to call or the refresh command If CommandBars.ActionControl.Parameter = "(...Refresh...)" Then SheetMenu Else ActiveWorkbook.Sheets(CommandBars.ActionControl.Pa rameter).Activate End If End Sub |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Sheet names in a MenuBar menu
Petr, Thanks for the comment. BTW it has also popup menubar to see the list
of workbook & sheet name to activate in addition to userform. Regards, Shailesh "Petr Danes" wrote in message ... Thank you Shah, it looks good, but my needs are fairly limited and the menu bar approach I developed still suits me best. Nice job, though, you've obviously put some effort into it. If I may offer one small criticism, though, go easy on the color. It's easy to overdo it and lead people to assume that a difference in color has some additional significance, beyond what is visible in, for instance, a button's caption. Also, some color combinations make the text harder to read, especially on a lower-quality monitor. Petr "Shah Shailesh" píše v diskusním příspěvku ... Hi Petr, I had also developed one addins "workbook navigation" for my needs. If you are interested you can download it from below site. Some features: 1. Index of all Sheet Names in separate worksheet with hyperlink. 2. Sort sheets ascending or descending, Create\remove multiple windows. 3. Synchronies all the sheets to view same area in the worksheet. 4. Copy activesheet without truncated of cells having more then 255 characters. ..more + image in the webpage. http://in.geocities.com/shahshaileshs/ Regards, Shailesh Shah http://in.geocities.com/shahshaileshs/ If You Can't Excel with Talent, Triumph with Effort. Free Addins Office Menu-2003 for Office-2007 http://in.geocities.com/shahshaileshs/menuaddins "Petr Danes" wrote in message ... Thank you everyone for chiming in. My original arrangement is still the best for my (admittedly limited) needs, so I will likely stick with it, but I'm glad it stimulated some discussion and I've learned a few new things along the way as well. And if my contribution helps someone else, so much the better. Long live newsgroups. Petr "Petr Danes" píše v diskusním příspěvku ... I recently started teaching in a software technical school, and my first course was in advanced Excel. Naturally, I prepared a workbook with lots of examples on various worksheets, but not until I started lecturing did I realize that lots of sheets mean lots of scrolling left and right to locate the correct sheet. I'd never needed a workbook with many sheets for any of my own projects, nor those I had created for others. The course went fairly well anyway, but I wanted a better way to navigate between sheets. Part of the idea came from ASAP Utilities' function of creating a separate index sheet with clickable hyperlinks for all sheets, and part of the code from John Walkenbach's Excel Power Programming book. (My thanks to you both.) The clickable index sheet function is handy, but solves only half of the problem - it quickly locates a sheet 'somewhere', but getting back to the index sheet for another shot out into the myriad of sheets is still a hassle. I decided a menu bar approach suited my needs better. The code reads in all the sheet names and creates a dropdown menu of sheet names. Originally it included a sort as well, but then I decided that I prefer the menu in the same order as the sheets in the workbook, and my naming and ordering scheme results in the names being in order anyway. The sort is still there, just the call commented out, in case someone wants to use it that way. There is no error checking and it takes no account of hidden properties of sheets, since I don't use that, but it could be added easily enough. The code is called from the Workbook_Open routine, so that the menu is freshly rebuilt every time the workbook opens. Probably not completely necessary, but the execution time is trivial and it makes for ease of use. I initially added a call from the Workbook_NewSheet event, but abandoned that since the code adds the default sheet name in use at the instant of creation (Sheet1 or some such), which is not very useful, since I always rename the sheet to something sensible, but by then the sheet has already been added to the menu with default name. If there were Workbook_SheetRename and Workbook_SheetDelete events, I would add calls there, as well as the NewSheet event. That would make it completely automatic and always current, but this works well enough. The first item on the menu, before the sheet names is a refresh call, so I can keep the menu up to date manually without having to close and re-open the workbook or switch to the VBA editor. There are two versions of the menu code, one creates a simple dropdown menu list, suitable for up to approximately thirty sheets on my machine (a bigger monitor would allow more), the second creates a two-stage list, grouped by the first character in the sheet name. I name my sheets by course day, so that "1.n xxxxx" is a sheet for the first day of the course, "2.n xxxxx" for the second day and so on. "0.n xxxxx" sheets are general sheets, not intended for any specific day. The second method obviously better handles a very large number of sheets, at the cost of a fraction of a second delay while the second tier unfolds. At the moment, my code creates both versions. I'll have to use them for a while to see which one I prefer for classroom use. Pete -- This e-mail address is fake, to keep spammers and their auto-harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. Start code (watch for line wrap!) ================================================== == Option Explicit Dim AEb As CommandBarButton Dim AEp As CommandBarPopup Dim AEp2 As CommandBarPopup Dim SheetNames() As String Sub SheetMenu() Dim i& ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count) ' Fill array with sheet names For i = 1 To UBound(SheetNames) SheetNames(i) = Sheets(i).Name Next i ' Sort the array in ascending order ' Not used in current implementation, ' but simply uncomment line below to order sheet names. 'BubbleSort SheetNames ' Build the sheet menus MakeMenuItem ' simple menu MakeMenuItem2 ' 2-tier menu End Sub Private Sub MakeMenuItem() ' Create the menu bar addition Dim i& With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With Next i End With End With End Sub Private Sub MakeMenuItem2() ' Create the menu bar addition Dim i&, nmhld$ ' Only one character is used for grouping nmhld = "!" With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .Controls("Sheets2").Delete On Error GoTo 0 Set AEp = .Controls.Add(msoControlPopup) With AEp .Caption = "Sheets2" Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = "(...Refresh...)" .OnAction = "GoToSheet" .Parameter = "(...Refresh...)" End With For i = 1 To UBound(SheetNames) If nmhld < Left$(SheetNames(i), Len(nmhld)) Then Set AEp2 = .Controls.Add(msoControlPopup) AEp2.Caption = Left$(SheetNames(i), InStr(SheetNames(i), ".")) nmhld = Left$(SheetNames(i), Len(nmhld)) End If With AEp2 Set AEb = .Controls.Add(msoControlButton) With AEb .Caption = SheetNames(i) .OnAction = "GoToSheet" .Parameter = SheetNames(i) End With End With Next i End With End With End Sub Sub BubbleSort(List() As String) ' Sorts the List array in ascending order Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub Public Sub GoToSheet() ' Action routine called by the menu ' Passed parameter is either the sheet name to call or the refresh command If CommandBars.ActionControl.Parameter = "(...Refresh...)" Then SheetMenu Else ActiveWorkbook.Sheets(CommandBars.ActionControl.Pa rameter).Activate End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet names in a MenuBar menu | Excel Programming | |||
Sheet names in a MenuBar menu | Excel Programming | |||
Sheet names in a MenuBar menu | Excel Programming | |||
Menu of sheet names | Excel Programming | |||
How to add menu button to exisiting menubar | Excel Programming |