View Full Version : [SOLVED:] How to make Excel file only open in Excel 2007
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
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
Sub M_snb()
if Application.Version="14.0" then
MsgBox "please open this file in Excel 2007"
exit sub
end if
End Sub
westconn1
12-10-2014, 02:51 AM
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
12-10-2014, 02:58 AM
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?
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)'...
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.
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;
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
12-10-2014, 11:51 PM
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.
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
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 :p:crying::banghead: 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
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?
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
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
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.
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
12-15-2014, 10:49 PM
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?
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.