Hi,
I have created an addin that returns the list of all the workbooks that have been used/activated during the current Excel session.( That includes the last one :wink: )
The code will eventually add a new Menu Button under the Window Menu in the worksheet main menu to show a sorted list of all previous open workbooks.
The only requirement is that you have to be using Excel2000 or later !
So let's get started:
Open a new Blank workbook.
Insert a UserForm1 in your project and add the following controls in the stated order:
-1 Frame Frame1
-3 Labels inside the frame, namely Label2 and Label3
(Don't worry about the design and sizing of the controls you are adding because the code will take care of that later on !)
-3 other labels Outside the Frame , namely Label4, Label5 and Label6.
-1 CommandButton CommandButton1 outside the frame.
-1 Image Control Image1 also outside the Frame.
After completing the adding of the controls, Double click the Userform1 and paste the following code:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Base</SPAN> 1
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
<SPAN style="color:#00007F">Const</SPAN> FormHeight = 250
<SPAN style="color:#00007F">Const</SPAN> LblsTop = 0
<SPAN style="color:#00007F">Const</SPAN> LblsWidth = 140
<SPAN style="color:#00007F">Const</SPAN> LblsHeight = 60
<SPAN style="color:#00007F">Const</SPAN> CanButnWidth = 1
<SPAN style="color:#00007F">Const</SPAN> CanButnHeight = 2
Me.Label4.Caption = "Workbook Name " & vbCrLf & "---------------" & vbCrLf
Me.Label5.Caption = "Date & Time Activated" & vbCrLf & "-----------------" & vbCrLf
Me.Label6.Caption = "Total Times Activated So Far " & vbCrLf & "-----------------"
arTransp = WorksheetFunction.Transpose(arNvigtDetails)
Label1.Left = 6: Label4.Left = Label1.Left + 1.5 * Frame1.Left
Label2.Left = Label1.Left + LblsWidth: Label5.Left = Label4.Left + LblsWidth
Label3.Left = Label1.Left + (LblsWidth * 2): Label6.Left = Label4.Left + (LblsWidth * 2)
Label1.Caption = ""
Label2.Caption = ""
Label3.Caption = ""
<SPAN style="color:#00007F">For</SPAN> h = <SPAN style="color:#00007F">UBound</SPAN>(arTransp) <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
Label1.Caption = Label1.Caption & arTransp(h, 1) & vbCrLf
Label2.Caption = Label2.Caption & arTransp(h, 2) & vbCrLf
Label3.Caption = Label3.Caption & arTransp(h, 3) & vbCrLf
<SPAN style="color:#00007F">Next</SPAN> h
<SPAN style="color:#00007F">With</SPAN> Me
.Width = FormWidth
.Height = FormHeight
.Caption = "Workbooks Navigation Log"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cntr <SPAN style="color:#00007F">In</SPAN> Me.Controls
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> Cntr <SPAN style="color:#00007F">Is</SPAN> Msforms.Label <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">With</SPAN> Cntr
.AutoSize = <SPAN style="color:#00007F">True</SPAN>
.WordWrap = <SPAN style="color:#00007F">True</SPAN>
.TextAlign = fmTextAlignCenter
.Font.Bold = <SPAN style="color:#00007F">True</SPAN>
.ForeColor = vbRed
.Top = Frame1.Top - .Height
.Width = LblsWidth
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cntr <SPAN style="color:#00007F">In</SPAN> Me.Frame1.Controls
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> Cntr <SPAN style="color:#00007F">Is</SPAN> Msforms.Label <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">With</SPAN> Cntr
.AutoSize = <SPAN style="color:#00007F">True</SPAN>
.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
.ForeColor = vbBlack
.TextAlign = fmTextAlignCenter
.Top = LblsTop
.Width = LblsWidth
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">With</SPAN> Frame1
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = Label1.Height
.Height = Me.InsideHeight / 3
.Width = LblsWidth * 3
.Left = Me.InsideWidth / 2
.Caption = ""
Me.Width = .Width + LblsWidth * 3 / 4
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> CommandButton1
.Top = Me.InsideHeight - 3 / 2 * (.Height)
.Left = (Me.InsideWidth / 2) - (.Width / 2)
.Width = Label1.Width * 3 / 4
.Height = Me.Height / 12
.Accelerator = "C"
.Caption = "Cancel"
.Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
stdole.StdFunctions.SavePicture NewCtrl.Picture, "Iconbmp"
<SPAN style="color:#00007F">With</SPAN> Image1
.Picture = LoadPicture("Iconbmp")
.Left = 1
.Top = 1
.BorderStyle = fmBorderStyleNone
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
Now, add the following code in the ThisWorkbook Module:
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> app <SPAN style="color:#00007F">As</SPAN> Application
<SPAN style="color:#00007F">Public</SPAN> WndwActivations <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> app_WorkbookActivate(<SPAN style="color:#00007F">ByVal</SPAN> Wb <SPAN style="color:#00007F">As</SPAN> Workbook)
<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> arNvigtDetails(1 <SPAN style="color:#00007F">To</SPAN> 3, 1 <SPAN style="color:#00007F">To</SPAN> WndwActivations + 2)
<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> arCountTimes(1 <SPAN style="color:#00007F">To</SPAN> WndwActivations + 2)
arCountTimes(WndwActivations + 2) = <SPAN style="color:#00007F">CStr</SPAN>(Wb.Name)
arNvigtDetails(1, WndwActivations + 2) = Wb.Name: arNvigtDetails(2, WndwActivations + 2) = _
Format(Date, "dd-mmm-yy") & " @ " & Time: arNvigtDetails(3, WndwActivations + 2) = _
<SPAN style="color:#00007F">UBound</SPAN>(Filter(arCountTimes, Wb.Name)) + 1
WndwActivations = WndwActivations + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
Application.CommandBars. _
FindControl(ID:=30009).Reset
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
<SPAN style="color:#00007F">Set</SPAN> Me.app = Application
WndwActivations = 1
<SPAN style="color:#00007F">Set</SPAN> NewCtrl = Application.CommandBars. _
FindControl(ID:=30009).Controls.Add(Type:=msoControlButton, Temporary:=True)
<SPAN style="color:#00007F">With</SPAN> NewCtrl
.Style = msoButtonIconAndCaption
.Caption = "Workbooks Navigation Log"
.OnAction = "ShowForm"
.FaceId = 577
.BeginGroup = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
Finally insert a Standard Module in your project and add the following code:
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> arNvigtDetails() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Public</SPAN> arCountTimes() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Public</SPAN> arTransp() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Public</SPAN> NewCtrl <SPAN style="color:#00007F">As</SPAN> CommandBarControl
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ShowForm()
UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
Now all you have to do is press Alt+F11 to go back to the Excel window click on SaveAs under the File Menu , give a name to the workbook, save the workbook as an Addin by selecting the FileFormat from the bottom DropDown List and Close Excel.
When you open Excel again, Click on Tools¦Add-ins¦Browse to locate your new Add-in and click Ok.This will install the Add-In so it is always available when you start Excel.
Once installed,Click on the Window Menu and you will find a Custom Menu that says "Workbooks Navigation Log".Click on this custom menu and a Form will show with some nice details about all the workbooks that are and were ! running during the current Excel session.
Experiment by adding new workbooks and navigating through them.
Let me know if any probs.