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

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 Befo=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

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


On Monday, September 24, 2012 5:19:45 PM UTC-4, Ben McClave wrote:
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 Befo=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

Ben,

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

Art