forcing specific sheet opening upon launch of .xlsx file

C

c1802362

Need some help....

I have a simple macro running on Win7/Excel 2007 that inserts different versions of legal disclaimers as the first tab of a target workbook.

The macro is in a separate macro-enabled file due to detailed instructions that the user must read before launching the form that guides the user into selecting the correct disclaimer for insertion on the first tab of the target workbook.

I've been asked to ensure that when the completed target file is opened, the legal disclaimer page is always the first sheet visible. However, the target file must be saved in a .xlsx, not .xlsm format.

Any suggestions on how to affect this type action without embedding VBA code within the target workbook?

Art
 
B

Ben McClave

Hi Art,

The following macros should work. Basically, it will take the target workbook file path and name and check to see if it is open. If not, it will open the workbook. Next, it will look for a sheet called "Disclaimer" and move it to the front of the workbook. You can adjust this macro to fit your needs. For example, you could link the file path and/or workbook name to cells in your macro workbook.
Hope this helps,
Ben

Sub MoveSheet()
Dim sWB As String 'Workbook Name with extension
Dim sFP As String 'Filepath
Dim WB As Workbook 'Workbook

sWB = "MyWorkbook.xlsx" 'Range("A1")
sFP = "C:\Desktop\" 'Range("A2")

If BookOpen(sWB) = False Then _
Workbooks.Open sFP & sWB, False, False
Set WB = Workbooks(sWB)
WB.Sheets("Disclaimer").Move Before:=WB.Sheets(1)

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
 
C

c1802362

Ben,

thanks for the response. However, my code works as advertised. What I was asked to make happen is ensuring that anyone who opens up the target file once my macro has updated it will always see the first 'disclaimer' sheet regardless of what state they left the workbook in during their previous session - and the workbook can't have embedded code in it.

Is there any way to set a specific sheet to the opening view in Excel as the default?

Art
 
B

Ben McClave

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
 
C

c1802362

Ben,

thanks for the guidance - I'll try your suggestions in a few days when I'm back at my workstation

Art
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top