Open to different individual worksheets from the program menu?

[Prashant Ujjainiya]

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?

 

[Jim Gordon MVP]

Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook, so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Geoff Lilley]

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:
http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.html

 

[Jim Gordon MVP]

Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:
http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.html

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Geoff Lilley]

Thank you, Jim. I'm really glad to have helped. BTW, the other
workaround I use to navigate through multiple worksheets is to
right-click on the sheet navigator control on the lower-right hand
corner; that gives me a list.

Cheers
Geoff

Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:
http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.html

Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook, so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP




Quoting from "Prashant Ujjainiya" <[email protected]>, in article
(e-mail address removed), on [DATE:

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Barry Wainwright [MVP]]

Working on the basis that VBA is a dying art in Office for Mac, here's an
AppleScript example

tell application "Microsoft Excel"
set sheetNames to name of every worksheet of active workbook
make new worksheet at beginning of active workbook ¬
with properties {name:"Worksheets"}
repeat with i from 1 to (count sheetNames)
set value of cell ("a" & i) to item i of sheetNames
end repeat
end tell

This will create a new worksheet (called "Worksheets") that contains in
column A the name of all the other worksheets, in order.

--
Barry Wainwright
Microsoft MVP (see http://mvp.support.microsoft.com for details)
The Entourage User's WebLog has moved!
For hints, tips and troubleshooting go to <http://www.barryw.net/weblog/>

From: "vikas.acharya" <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
Date: Thu, 4 Jan 2007 07:17:01 -0800
Conversation: Open to different individual worksheets from the program menu?
Subject: Re: Open to different individual worksheets from the program menu?

I have the same query. I want names of approx 100 sheets in a .xls file, be
listed in a sheet in the same .xls file, to summarise my work.
I tried to download from link given by Jim. But failed to extract the .dmg
file.
So, query is still unresolved. If you have got any clue, please share.

Thank you, Jim. I'm really glad to have helped. BTW, the other
workaround I use to navigate through multiple worksheets is to
right-click on the sheet navigator control on the lower-right hand
corner; that gives me a list.

Cheers
Geoff

Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP


Quoting from "Geoff Lilley" <[email protected]>, in article
(e-mail address removed), on [DATE:

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:

http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.htm>>>>
l

Jim Gordon MVP wrote:
Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook, so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have
not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP




Quoting from "Prashant Ujjainiya" <[email protected]>, in article
(e-mail address removed), on [DATE:

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Geoff Lilley]

Vikas:

You'll want to re-post your question in
microsoft.public.excel.programming; this group is for people using
Microsoft Excel for the Macintosh.

Best of luck.

Cheers
Geoff

Further, to clarify, I'm using Window XP as OS.

Working on the basis that VBA is a dying art in Office for Mac, here's an
AppleScript example

tell application "Microsoft Excel"
set sheetNames to name of every worksheet of active workbook
make new worksheet at beginning of active workbook ¬
with properties {name:"Worksheets"}
repeat with i from 1 to (count sheetNames)
set value of cell ("a" & i) to item i of sheetNames
end repeat
end tell

This will create a new worksheet (called "Worksheets") that contains in
column A the name of all the other worksheets, in order.

--
Barry Wainwright
Microsoft MVP (see http://mvp.support.microsoft.com for details)
The Entourage User's WebLog has moved!
For hints, tips and troubleshooting go to <http://www.barryw.net/weblog/>

From: "vikas.acharya" <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
Date: Thu, 4 Jan 2007 07:17:01 -0800
Conversation: Open to different individual worksheets from the program menu?
Subject: Re: Open to different individual worksheets from the program menu?

I have the same query. I want names of approx 100 sheets in a .xls file, be
listed in a sheet in the same .xls file, to summarise my work.
I tried to download from link given by Jim. But failed to extract the .dmg
file.
So, query is still unresolved. If you have got any clue, please share.


:

Thank you, Jim. I'm really glad to have helped. BTW, the other
workaround I use to navigate through multiple worksheets is to
right-click on the sheet navigator control on the lower-right hand
corner; that gives me a list.

Cheers
Geoff

Jim Gordon MVP wrote:
Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP


Quoting from "Geoff Lilley" <[email protected]>, in article
(e-mail address removed), on [DATE:

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:
http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.htm>>>>
l
Jim Gordon MVP wrote:
Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook, so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have
not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP




Quoting from "Prashant Ujjainiya" <[email protected]>, in article
(e-mail address removed), on [DATE:

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info
--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Barry Wainwright [MVP]]

In that case, here are the steps:

Step 1: Go and buy a mac! (this is an Excel for Mac group, and AppleScript
doesn't run under XP

--
Barry Wainwright
Microsoft MVP (see http://mvp.support.microsoft.com for details)
The Entourage User's WebLog has moved!
For hints, tips and troubleshooting go to <http://www.barryw.net/weblog/>

From: "vikas.acharya" <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
Date: Thu, 4 Jan 2007 08:27:01 -0800
Conversation: Open to different individual worksheets from the program menu?
Subject: Re: Open to different individual worksheets from the program menu?

Further, to clarify, I'm using Window XP as OS.

Working on the basis that VBA is a dying art in Office for Mac, here's an
AppleScript example

tell application "Microsoft Excel"
set sheetNames to name of every worksheet of active workbook
make new worksheet at beginning of active workbook ¬
with properties {name:"Worksheets"}
repeat with i from 1 to (count sheetNames)
set value of cell ("a" & i) to item i of sheetNames
end repeat
end tell

This will create a new worksheet (called "Worksheets") that contains in
column A the name of all the other worksheets, in order.

--
Barry Wainwright
Microsoft MVP (see http://mvp.support.microsoft.com for details)
The Entourage User's WebLog has moved!
For hints, tips and troubleshooting go to <http://www.barryw.net/weblog/>

From: "vikas.acharya" <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
Date: Thu, 4 Jan 2007 07:17:01 -0800
Conversation: Open to different individual worksheets from the program menu?
Subject: Re: Open to different individual worksheets from the program menu?

I have the same query. I want names of approx 100 sheets in a .xls file, be
listed in a sheet in the same .xls file, to summarise my work.
I tried to download from link given by Jim. But failed to extract the .dmg
file.
So, query is still unresolved. If you have got any clue, please share.


:

Thank you, Jim. I'm really glad to have helped. BTW, the other
workaround I use to navigate through multiple worksheets is to
right-click on the sheet navigator control on the lower-right hand
corner; that gives me a list.

Cheers
Geoff

Jim Gordon MVP wrote:
Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP


Quoting from "Geoff Lilley" <[email protected]>, in article
(e-mail address removed), on [DATE:

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:
http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.htm>>l

Jim Gordon MVP wrote:
Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook,
so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would
be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have
not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP




Quoting from "Prashant Ujjainiya" <[email protected]>, in article
(e-mail address removed), on [DATE:

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[JE McGimpsey]

I have the same query. I want names of approx 100 sheets in a .xls file, be
listed in a sheet in the same .xls file, to summarise my work.

One way:

Public Sub ListWorksheetsOnNewSheet()
Dim arr As Variant
Dim i As Long
With ActiveWorkbook.Worksheets
ReDim arr(1 To .Count, 1 To 1)
For i = 1 To .Count
arr(i, 1) = .Item(i).Name
Next i
.Add _
Before:=.Item(1), _
Type:=xlWorksheet
With .Item(1)
On Error Resume Next
.Name = "List of Worksheets"
On Error GoTo 0
With .Cells(1, 1)
.Value = "Worksheets"
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
.Cells(2, 1).Resize(UBound(arr, 1)).Value = arr
End With
End With
End Sub

 

[Jim Gordon MVP]

Hi,

Geoff helps again, but I think he made a slight mistake (at least it seems
that way to me).

Excel has a built-in feature that displays a selectable pop-up list of all
the worksheets in a workbook.

Right-click or control-click on the worksheet navigation arrows near the
lower left corner of a worksheet window (Geoff said the lower right - but
it's the lower left corner).

Another selection feature is "Select All Sheets" which you will find by
right-clicking or control-clicking on any individual sheet tab.

-Jim Gordon
Mac MVP

I have the same query. I want names of approx 100 sheets in a .xls file, be
listed in a sheet in the same .xls file, to summarise my work.
I tried to download from link given by Jim. But failed to extract the .dmg
file.
So, query is still unresolved. If you have got any clue, please share.

Thank you, Jim. I'm really glad to have helped. BTW, the other
workaround I use to navigate through multiple worksheets is to
right-click on the sheet navigator control on the lower-right hand
corner; that gives me a list.

Cheers
Geoff

Wow, Geoff!

Thanks for the sample code and especially for the link to Mac Excel
Expander. It's free and what a great add-in.

-Jim Gordon
Mac MVP


Quoting from "Geoff Lilley" <[email protected]>, in article
(e-mail address removed), on [DATE:

Jim:

I hope you and the rest of the group will find this code beneficial to
that end. I wrote this when I was experimenting with custom lists and
arrays and userforms:

Option Explicit
Public bytNumSheets As Byte
Private Sub cmdbtnShowSheetNames_Click()
Sheets(cboSheetNames.ListIndex + 1).Activate
frmSheetNames.Hide
End Sub
Private Sub UserForm_Activate()
For bytNumSheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(bytNumSheets).Activate
cboSheetNames.AddItem (Sheets(bytNumSheets).Name)
Next
End Sub

The userform is a combo box, with each item on the combo box being a
sheet name; the command button just says "Go To Worksheet."

On the Autpen event of my Personal Macro Workbook, I load a toolbar
that has a couple of buttons - one of them activates this form.

If you see any way I could improve on this model, let me know.

Thanks
Geoff

PS - I believe the Mac Excel Expander does something similar, along
with other functionality. (I could be wrong.) Check it out, see what
you think:

http://www.apple.com/downloads/macosx/business_finance/macexcelexpander.htm>>>>
l

Jim Gordon MVP wrote:
Hi Prashant,

I'm not sure I would want to have all of the worksheets listed on a file
menu. Excel has no limit to the number of worksheets within a workbook, so
there could be millions of sheets.

Some sort of limit needs to be set. How many sheets do you think would be
reasonable to have in the menu? If there are more than that number, how
would you like to pick and choose among the sheets?

I've thought about making an add-in that organizes worksheets, but have
not
enough time to make it. Perhaps MacBU will consider making one.

-Jim Gordon
Mac MVP




Quoting from "Prashant Ujjainiya" <[email protected]>, in article
(e-mail address removed), on [DATE:

Can we enlist all the worksheets of a excel(*.xls) file in through the
program menu? How?


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info


--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[JE McGimpsey]

Right-click or control-click on the worksheet navigation arrows near the
lower left corner of a worksheet window (Geoff said the lower right - but
it's the lower left corner).

I assign a keyboard shortcut for this dialog in my startup macro. During
startup I run this to assign the shortcut CMD-OPT-g:

Public Sub SelectSheetsKey()
Application.OnKey "*%g", "SelectSheetsDialog"
End Sub

then in a regular code module I have:

Public Sub SelectSheetsDialog()
With Application.CommandBars.Add(Temporary:=True)
.Visible = False
.Controls.Add(Id:=957).Execute
.Delete
End With
End Sub

 

[Geoff Lilley]

OK, JE, that's way easier than mine. Jim, you're right, I did mean
lower-left. D'OH! Thanks, fellas.

Cheers
Geoff

 

[Jim Gordon MVP]

No problem! Please keep those postings coming.

-Jim Gordon
Mac MVP

OK, JE, that's way easier than mine. Jim, you're right, I did mean
lower-left. D'OH! Thanks, fellas.

Cheers
Geoff

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info