Frequent workbooks

  • Thread starter Thread starter widman
  • Start date Start date
W

widman

There are about 10 workbooks that I open every day, usually several times a
day. Excel remembers the last 9 files, not the 10 most wanted.

is there an easy way to put these at my fingertips (like the links across
the top of Firefox, or some drop-down menu where I can put what I want on).
 
In 2003 check under menu 'File'..> Save as WorkSpace
In 2007 check for 'Save WorkSpace' tab under 'View'

If this post helps click Yes
 
No, I rarely want all ten open at a time, and some are templates. I only
have 4 GB of RAM. There are several that will only open in a certain order
without giving VISTA or Excel 2003 an error message that there are not enough
resources and destroy my links and formulas.
 
OK. If there is an order, why dont you try placing a link to the 10th
worksheet in the 9th .

If this post helps click Yes
 
I can't have all 10 open at once. Not enough memory. but I need all 10 or so
available without navigating all over the place each time I need them.

I'm looking for a one stop way to have them available
 
You can write/record a macro to open each file. Then you can assign it to a
button on a toolbar (you can make your own tool bar).
 
Thank you Tanya............ Perfect

Tanya M said:
You can write/record a macro to open each file. Then you can assign it to a
button on a toolbar (you can make your own tool bar).
Click to expand...
 
If you would like to create custom menus; please find the below procedure to
do that.
1. Open a new workbook.
2. Rename sheet3 to 'MenuDetails' and copy the below data with headers in
row 1. The data range is A1: E11

Menu ID Caption Menu Type Parent ID Macro
1 Worksheet1 1 0 Macro1
2 Worksheet2 1 0 Macro2
3 Worksheet3 1 0 Macro3
4 Worksheet4 1 0 Macro4
5 Worksheet5 1 0 Macro5
6 Worksheet6 1 0 Macro6
7 Worksheet7 1 0 Macro7
8 Worksheet8 1 0 Macro8
9 Worksheet9 1 0 Macro9
10 Worksheet10 1 0 Macro10

3. Launch VBE using Alt+F11 and insert module and copy the below 3
procedures named Mainmacro, Macro1, CreateMenu.
4. Save and get back to workbook.
5. From tools macro run Mainmacro.

This will create a custom menu with the 10 sheets. Workbook1 will open the
..xls mentioned in MAcro1. You can have different macros like macro2 , 3 etc;
for opening different workbooks...

Sub MainMacro()
CreateMenu "MyNewMenu", ActiveWorkbook.Sheets("MenuDetails")
End Sub

Sub Macro1()
Workbooks.Open "c:\workbook1.xls"
End Sub

Sub CreateMenu(strMainMenu As String, wsMenu As Worksheet)
'Procedure to create an Excel Menu and multiple levels of sub menus
'-------------------Arguments-----------------------------
'strMainMenu - The Main menu caption to be passed
'wsMenu - Worksheet in which menu details are stored(5 fields)
'Unique MenuID, Caption, Menu type,Parent Menu ID, Macro

Dim lngRow As Long 'Start Row
Dim intMenuID As Integer 'Unique menu ID
Dim intMenuPID As Integer 'Parent menu ID
Dim intHelpMenu As Integer 'Help menu index
Dim varMenuType As Variant 'Menu type (1,10)
Dim strMacroName As String 'Macro to be assigned
Dim strMenuCaption As String 'Menu captions
Dim cbMainMenuBar As CommandBar 'Command Bar
Dim arrCBC() As CommandBarControl 'Command Bar control Array

lngRow = 2
ReDim arrCBC(0)
'Remove if the menu already exists
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(strMainMenu).Delete
On Error GoTo 0
'Identify menu location just before Help menu
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
intHelpMenu = cbMainMenuBar.Controls("Help").Index
'Create main menu
Set arrCBC(0) = cbMainMenuBar.Controls.Add(Type:=10, Before:=intHelpMenu)
arrCBC(0).Caption = strMainMenu

'Create sub menus
Do While wsMenu.Range("A" & lngRow) <> ""
intMenuID = wsMenu.Range("A" & lngRow)
ReDim Preserve arrCBC(intMenuID)
strMenuCaption = wsMenu.Range("B" & lngRow)
varMenuType = wsMenu.Range("C" & lngRow)
intMenuPID = wsMenu.Range("D" & lngRow)
strMacroName = wsMenu.Range("E" & lngRow)

Set arrCBC(intMenuID) = arrCBC(intMenuPID).Controls.Add(Type:=varMenuType)
arrCBC(intMenuID).Caption = strMenuCaption
If intMenuPID > 0 Then
arrCBC(intMenuID).OnAction = strMacroName
End If

lngRow = lngRow + 1
Loop

End Sub


If this post helps click Yes
 
Back
Top