Art,
I see what you're looking for now. I can think of only a few options, and I'm not sure whether they will meet your needs exactly. However, here theyare in no particular order:
1. Have your macro save the XLSX file as Read Only. This way users that wish to make changes will have to save them to a new book and the original book continues to display the first sheet by default.
2. Add a hyperlink to the Disclaimer tab on each sheet within the workbook (in cell A1 for example). Then, use a Data Validation formula that always equates to False in order to prevent changes to the link (i.e. "=1=0").I use this form of hyperlink formula:
=HYPERLINK("#" & CELL("address", Disclaimer!A1), "Click here for important Disclaimer")
3. Maintain a list of the file paths and file names generated by your macrowithin the macro workbook and use that list to run the macros below whenever the workbook(s) change. For example, you could create a list where column A contains the file paths, column B contains the file names, column C contains the (User-Defined) formula "=FileLastModified(A2&B2)" and cell F1 contains the date of the most recent update. You can then either run the "UpdateAll" macro below to move the Disclaimer tab to the front of every sheet on the list, or "UpdateSelected" to only update files that have changed since the last time the macro ran. (Code for all of the above is copied atthe end of this post).
4. Finally, you could include disclaimer language on each sheet in the Header or Footer (or even within each cell through either comments or data validation input messages).
These are the best options I could think of that would not require macros to be stored within the destination workbook. Perhaps none is ideal, but hopefully one or more will help you out.
Ben
Code:
Public Function FileLastModified(strFileORFolderName As String) As Date
On Error GoTo Errr
FileLastModified = FileDateTime(strFileORFolderName)
Exit Function
Errr:
FileLastModified = 0
End Function
Sub MoveSheet(sWB As String, sFP As String)
Dim WB As Workbook 'Workbook
If BookOpen(sWB) = False Then _
Workbooks.Open sFP & sWB, False, False
Set WB = Workbooks(sWB)
WB.Sheets("Disclaimer").Move Before:=WB.Sheets(1)
WB.Sheets("Disclaimer").Activate
WB.Close True
End Sub
Function BookOpen(WBk As String) As Boolean
'Checks whether a workbook is open
BookOpen = False
On Error GoTo NotOpen
Application.DisplayAlerts = False
Workbooks(WBk).Activate
BookOpen = True
NotOpen:
Err.Clear
End Function
Sub UpdateAll()
Dim rPath As Range 'Range with file paths
Dim rName As Range 'Range with file names
Dim rCell As Range
Dim l As Long
Set rPath = Sheet1.Range("A2:A" & Sheet1.Range("A50000").End(xlUp).Row)
Set rName = Sheet1.Range("B2:B" & Sheet1.Range("A50000").End(xlUp).Row)
l = 1
'On Error Resume Next
For Each rCell In rName
Call MoveSheet(rName.Range("A" & l).Value, rPath.Range("A" & l).Value)
l = l + 1
Next rCell
End Sub
Sub UpdateSelected()
Dim rDate As Range 'Date last updated
Dim rCell As Range
Dim dDate As Date 'Last update
Set rDate = Sheet1.Range("C2:C" & Sheet1.Range("A50000").End(xlUp).Row)
dDate = Sheet1.Range("F1").Value
'On Error Resume Next
For Each rCell In rDate
If rCell.Value = 0 Then GoTo NextRC
If rCell.Value < dDate Then
Call MoveSheet(rCell.Offset(0, -1).Value, rCell.Offset(0, -2).Value)
End If
NextRC:
Next rCell
Sheet1.Range("F1").Value = Now()
End Sub
