How to make Excel file only open in Excel 2007

[oam]

We have Excel 2007 and 2010 loaded on our computers due to policy and files made containing macros will not run on the version of Excel 2010 that is loaded on our machines.

What I would like to know, is there a macro that would prevent the Excel files from opening in Excel 2010? A formula that would display which version of Excel is opening the file and if it is Excel 2010 then a macro would prompt the user to close and reopen file in Excel 2007.

Thank you for your time and any help you can provide

[GTO]

Greetings oam,

I would preface my comments with clearly stating that I have absolutely no experience(s) in your stated issue. That said:

I understand your first bit, to wit: "We have Excel 2007 and 2010 loaded on our computers due to policy and ..." to mean that each PC has Excel available in both 2007 and 2010 versions. Is that verified as correct?

If that is the case, my first effort would be to get to know a tech and learn how such a thing is accomplished.

I hope I am not wasting your time or efforts,

Mark

[snb]

Sub M_snb()
   if Application.Version="14.0" then 
      MsgBox "please open this file in Excel 2007"
      exit sub
   end if
End Sub

[westconn1]

you could have a file open macro, that will detect the excel version, then close the workbook if the wrong version
you may be able to shell or shellexecute to open the document in the 2007 excel
you would just have to test this, as there may be timing issues, between closing one version and opening in the other

[Aflatoon]

If macro files won't run in your version of Office 2010, how could a macro do anything when you open the file in 2010?

[GTO]

If macro files won't run in your version of Office 2010, how could a macro do anything when you open the file in 2010?

Yes Sir, that part struck me as well, but was curious as to the "how would this be loaded to get such a result" part. I would guess at hiding everything but a warning sheet before saving (forcing macro-enabled type efforts) - in this case, maybe a "you haven't enabled macros, or you are in 2010..." type message.

I have not tried 'Workbook_AfterSave(ByVal Success As Boolean)'...

[oam]

GTO;
There are both version loaded on all the computers! This was a directive from upper management and the R&D team made their own version of Excel 2007 & 2010 so it would load both on each machine. They disabled the macros in Excel 2010 with the idea macros contain virus but we use them make it our process more user friendly, what a pain!

I guess I did not think this through, you are right, how would the macro run if macros are disabled in 2010?

After reading your comments I am not sure how to solve this and unless some else has an idea(s) I should cancel this post.

Thank you all for your help and insight.

[SamT]

Have a worksheet that warns to open book in 2007

In 2007, have a workbook_Open sub that deletes that sheet, if it exists.

Also have a Before Close sub that recreates, then activates that sheet. (In case it is deleted in 2010.)

If Macros are disabled, the sheet will be the first thing seen, otherwise it will not be seen.

[GTO]

GTO;
There are both version loaded on all the computers! This was a directive from upper management and the R&D team made their own version of Excel 2007 & 2010 so it would load both on each machine. They disabled the macros in Excel 2010 with the idea macros contain virus but we use them make it our process more user friendly, what a pain!

Yeh, tech bureau guys are great. I "get it" though. What little bits of code I've written to help my guys/gals get something or other done easier/quicker/more accurately, at least in Excel, has probably had more code in it to prevent the user from doing stuff they shouldn't ("You mean deleting the cells/rows/columns is somehow different than erasing?").

I guess I did not think this through, you are right, how would the macro run if macros are disabled in 2010?...

That was Aflatoon's comment, not mine. I think Sam's idea is neat, though I like using BeforeSave better than BeforeClose. Here's something not tested well, and it will not work for a SaveAs as currently written. Those points mentioned, see if it is something to start with if you like.

In the ThisWorkbook Module:

Option Explicit
  
Private bInProcess  As Boolean
Private bClosing    As Boolean
  
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Long
  
  'Stop '<--- Un-REM Stop(s) to step through
  
  Application.DisplayAlerts = False
  
  If Not bClosing Then
    If Not ThisWorkbook.Saved Then
      
      intResponse = MsgBox("Do you want to save the changes you made to '" & _
                           Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - (Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".")) - 1) & "'?", _
                              vbExclamation Or vbYesNoCancel Or vbDefaultButton1, _
                              "My Custom Project")
      
      Select Case intResponse
      Case vbYes
        bClosing = True
        '// See procedure.  We don't need to execute the save here, and in    //
        '// fact, due to a weird glitch in Excel (least 2000), this is        //
        '// better.                                                           //
        Call Workbook_BeforeSave(False, False)
        '//This is required, as even though the file saved while in           //
        '// BeforeSave, changes occurred post save.                           //
        ThisWorkbook.Saved = True
      Case vbNo
        bClosing = True
        '// User doesn't want to save changes, so just mark file saved.     //
        ThisWorkbook.Saved = True
      Case vbCancel
        '// User cancelled closing, and least in Excel 2000, I found it     //
        '// necessary to reactivate stuff if I wanted the focus returned.   //
        ThisWorkbook.Activate
        bClosing = False
        Cancel = True
        Application.DisplayAlerts = True
        ActiveCell.Activate
        Exit Sub
      End Select
      
    End If
  End If
  
End Sub
  
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wksCurrentActiveSheet As Worksheet
Dim wksWorksheet          As Worksheet
  
  'Stop
  
  If SaveAsUI Then
    Cancel = True
    MsgBox "Not enough code to handle a SaveAs...", vbInformation, vbNullString
    Exit Sub
  End If
  
  If Not (bInProcess And Not Cancel) Then
    
    bInProcess = True
    Set wksCurrentActiveSheet = ActiveSheet
    
    shtMacWarn.Visible = xlSheetVisible
    
    For Each wksWorksheet In ThisWorkbook.Worksheets
      If Not wksWorksheet.CodeName = "shtMacWarn" Then
        wksWorksheet.Visible = xlSheetVeryHidden
      End If
    Next
    
    Cancel = True
    DoEvents
    
    ThisWorkbook.Save
    
    If bClosing = True Then
      Exit Sub
    End If
    
    For Each wksWorksheet In ThisWorkbook.Worksheets
      wksWorksheet.Visible = xlSheetVisible
    Next
    
    shtMacWarn.Visible = xlSheetVeryHidden
    
    If Not (ActiveSheet.Name = wksCurrentActiveSheet.Name) _
    And (wksCurrentActiveSheet.Visible = xlSheetVisible) Then
      
      wksCurrentActiveSheet.Activate
      
    End If
    
    ThisWorkbook.Saved = True
    
    bInProcess = False
    
  End If
  
End Sub
  
Private Sub Workbook_Open()
Dim wksWorksheet As Worksheet
  
  'Stop
  
  For Each wksWorksheet In ThisWorkbook.Worksheets
    wksWorksheet.Visible = xlSheetVisible
  Next
  
  shtMacWarn.Visible = xlSheetVeryHidden
  
  ThisWorkbook.Saved = True
  
End Sub

Mark

[gmayor]

If you are allowed to run macros in Excel 2007 and not in Excel 2010, then any policy designed to 'prevent viruses' by not allowing macros in 2010 is simply nonsensical.

Macros don't hold viruses. They can contain malicious code, and that would be just as malicious in 2007 as 2010.

If you are blocked from and not allowed to run macros on either version, then there would be no point whatsoever adding macro code to either version. It wouldn't run.

Two versions of Excel will share some resources. In this instance the principle issue is the file association, which can only be associated with one version. By default that will be the last one you used.

You can configure Excel to be associated only with one or the other, but that would require a registry hack, and if you are not allowed to run macros, then hacking the registry would hardly be allowed.

This policy needs to be rethought. Someone needs to take the seat polisher responsible for this policy on one side and educate him. The policy is harming the company's productivity and not achieving anything useful.

[SamT]

Mark,

Do you see anything wrong with this. You have more experience with BeforeSave bugs than I do. I'm thinking that, if it works, it will let the book(s) be viewed and manually edited in 2010 There must be a reason Management wants people to use it.

ThisWorkbook Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  CreateWarningSheet
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 CreateWarningSheet
End Sub

Private Sub Workbook_Open()
  DeleteWarningSheet
End Sub

Module Code, although it too could go in the workbook's code page.

Sub CreateWarningSheet()

If WarningSheetExists Then Exit Sub
  Application.ScreenUpdating = False
  Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = "WarningSheet"
  Application.ScreenUpdating = True
  Sheets("WarningSheet").Activate

End Sub

Sub DeleteWarningSheet()

Application.DisplayAlerts = False
  If WarningSheetExists Then Sheets("WarningSheet").Delete
Application.DisplayAlerts = True

End Sub

Function WarningSheetExists() As Boolean

On Error Resume Next
If Sheets("WarningSheet").Name <> "WarningSheet" Then
  WarningSheetExists = False
Else
  WarningSheetExists = True
End If

End Function

[GTO]

Mark,

Do you see anything wrong with this. You have more experience with ... bugs than I do. I'm thinking that, if it works, it will let the book(s) be viewed and manually edited in 2010 There must be a reason Management wants people to use it.

Hi Sam,

Ditching 'BeforeSave' would no doubt leave the statement far more accurate. Unfortunately, mostly not actual bugs, just some new way I figured to bugger up some code If the board ever creates a "dunce cap" level, I am sure to qualify

I see nothing wrong and no way to "beat" it; nice one! My comment reference using BeforeClose was at using only that event (well... in addition to the Open event); but you are using the BeforeSave as well. You may be right about the users needing to be able to use it (without vba enabled), I went with the OP's initial request as to outright blocking opening in 2010.

Mark

[oam]

Thank you all for your exciting discussion!
SamT,

Your idea works for me so far but I would like to know if there is a way I can add a message to the “Warning Sheet” that would let the user know they should close the file and reopen in Excel 2007?

[SamT]

Dang me, Thought I did that

Sub CreateWarningSheet()

If WarningSheetExists Then Exit Sub
  Application.ScreenUpdating = False
  Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = "WarningSheet"
  
  With Sheets("WarningSheet")
    With .Range("C14")
       .Value = "Macros are disabled in this version of Excel. Please use Excel 2007."
       .Font.Size = 16
       .Font.Bold = True
    End With 'Rng C14
    With .Range("C15")
       'Example of second cell usage
    End With 'Rng C15
  End With 'Wrn Sht

  Application.ScreenUpdating = True
   Sheets("WarningSheet").Activate

End Sub

[GTO]

Hi oam,

In short/gist, just set a reference to the sheet upon creating it.

Option Explicit
  
Sub example()
Dim wks As Worksheet
  
  With ThisWorkbook
    Set wks = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count), Type:=xlWorksheet)
  End With
  
  wks.Range("C2:C5").Value _
    = Application.Transpose(Array("Greetings:", _
                                  "This workbook needs opened in Excel 2007, and macros need to be enabled.", _
                                  "Currently, our company has restrictions that prohibit Excel 2010 from being fully functional.  So,", _
                                  "if you have this open in 2010, plesae close it and reopen in 2007" _
                                  ) _
                            )
  
End Sub

After you set a reference to the sheet, you can format, add values, or whatever, to your heart's content.

A bit of cat killin' curiosity on my part, and at a point Sam brought up: Do you wish for all the sheets to be visible (so they can still be worked on) even if macros are disabled?

@SamT:

Hey Sam, I was just thinking that rather than rebuilding the sheet, couldn't we just hide it? Of course a user might delete it, so the insert/format would still be coded in case the sheet no longer exists.

Mark

[SamT]

oam, I edited two errors GTO pointed out to me in the code above.

@ Mark; Of course you could. Very easily. a minor edit in two subs is all it takes.

Can't garuntee that no 2007 or 2010 User messes with the warning message.

[oam]

You guys are the best!

Even when the macros won't run you come up with a solution to a problem!

Thank you all for your help, I think this will work.

[gmayor]

I still can't get my head round how, if macros are blocked in 2010, this macro is going to run when opened in 2010?

[SamT]

It's not.

If macros can't run, the warning sheets is active.

If macros do run, the Warning sheet is deleted.

BTW, to Activate a specific sheet in 2007, add the following line tat the end of Sub DeleteWarningSheet

Sheets("SpecificSheet").Acivate