PDA

View Full Version : [SOLVED] How to make Excel file only open in Excel 2007



oam
12-09-2014, 04:50 PM
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
12-10-2014, 01:38 AM
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
12-10-2014, 02:50 AM
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?

GTO
12-10-2014, 03:37 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?

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
12-10-2014, 04:22 PM
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
12-10-2014, 05:37 PM
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
12-10-2014, 10:39 PM
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.

SamT
12-10-2014, 11:52 PM
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
12-11-2014, 12:49 AM
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

oam
12-11-2014, 05:21 PM
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
12-11-2014, 07:35 PM
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
12-11-2014, 07:42 PM
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
12-11-2014, 09:40 PM
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
12-15-2014, 09:00 PM
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?

SamT
12-15-2014, 11:14 PM
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