Solved: Word challenge: Force-in macro enabling

[joelle]

Hello Everyone,

I recently have a Word project and I need for the users to always click "Enable Macros" so that they can interact with data in that document; otherwise, it is just a stiff/dead document. I cant ensure the process without the help of some codes.
I found on the web example codes below that forces a user to "Enable Excel Macros" (that looks terribly advanced for me) but I cannot find the equivalence for Word -- especially Word does not use [sheets]).

As always, many thanks in advance for your help.
Nee

'Enter name of the Entry/Warning Page
Private Const dsWarningySheet As String = ?sheet1?
Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
For Each ds In ActiveWorkbook.Sheets
If LCase(dsWarningSheet) = LCase(ds.Name) Then
ds.Visible = True
Else: ds.Visible = xlVeryHidden
End If
Next
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal ds As Object, _
ByVal Target As Excel.Range)
If LCase(ds.Name) = LCase(dsWarningSheet) Then
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End If
End Sub

Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End Sub

[matthewspatrick]

The code you grabbed exploits Excel's worksheet interface--if you do not enable the macros, then all you see is one worksheet is a worksheet that says "next time enable the macros, you bozo!" If the designer password-protects the VB Project, then the only ways for the user to really interact with the workbook is to (1) enable macros or (2) crack the various passwords--doable, but a hassle.

You may want to consider "signing" the VB Project to get around the warning. See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_dsvba.asp

For better certiification, you have to pay :)

Patrick

[mdmackillop]

It's not very secure, and a save is carried out when the document is closed, but it may be some use.
The passwords are mdm

[matthewspatrick]

Malcolm,

I was thinking along those lines. What I really wanted to do was make a dummy first section that would carry the "you must enable macros or feel my wrath!" message, and then have that section be hidden, but I could not find a way to do it. Not easily, anyway.

The more I try to wade into the Word object model, the more I come away feeling frustrated. Sometimes it seems as if MS just gave up on it.

Patrick

[mdmackillop]

I'd go with the signed code as the simplest solution, if this can be set up on other users PCs, with my solution as a fallback.

[fumei]

Uh, you can have all the signed code you want, but if they have Security on High...they will get asked if they want to enable macros. I mean, if you only use selfcert.

md - I am not quite getting your posted doc. The question was regarding forcing the enabling of macros. Your doc does not require enabling macros - it requires macros to be enabled.

[joelle]

Hello All,

First off, thank you all for the post back. But at the same time I feel lost.
So, can it or can't it be done? I mean, is there a workaround like Excel to give Word user a message asking them to click the "Enable macro" before they can view/make change to the target document?

Joanne -- Thanks for the test doc. I guess I can live with it if there is a way to convert the warning to a message box!
I cannot put that warning line on my document but I love your idea to have the doc protected until they reopen it with macros enabled -- almost there ?

But, I know Excel can do this ... I just know nothing about Word VBA.
Pls any other help?

Many thanks,
Nee

[fumei]

So, can it or can't it be done? I mean, is there a workaround like Excel to give Word user a message asking them to click the "Enable macro" before they can view/make change to the target document?
Have you actually used the Excel code? It can not really be considered a workaround.


Joanne -- Thanks for the test doc.
Uh, Joanne is in md's signature/comment line. He is refering to another member here named Joanne

I guess I can live with it if there is a way to convert the warning to a message box!
No - probably not. That would require a macro, and again - if you have Security on High, you will get the macro warning message. Even with md's document you get the Microsoft macro warning.

I cannot put that warning line on my document but I love your idea to have the doc protected until they reopen it with macros enabled -- almost there ?
Why can't you put it in?

But, I know Excel can do this ... I just know nothing about Word VBA.
I am not sure you are grasping this. The Excel code does NOT force the user to enable macros. Neither does md's code.

I have expanded md concept and have a warning message in the document. If the document is opened with macros enabled, the warning message is removed. When the document is closed, the warning message is replaced.

NOTE: md what version of Word are you using? I do not have a Protect wdAllowOnlyReading. It is not one of my wdProtectionTypes.

[mdmackillop]

Hi Gerry,
I'm using Word 2003, and your protection method works fine in it.
Regards
MD

[fumei]

Thanks for letting me know Joanne. Awwwww, I liked that quote of Joanne's......

[Anne Troy]

Hi, guys! Long time, no see. :)

What I have suggested in the past, and MAY work is that, if macros are enabled, a document_open macro runs an autotext that is the contents of the doc. Or perhaps changes white font to black and hides or deletes the "You must enable macros" message. Is this a form or a document that's edited over and over?

Of course, Malcolm or Gerry might hack my suggestion to threads.

[mdmackillop]

Hi DB,
Hope you had a good Christmas and New Year.
I though about white font, but not autotext, and I'm sure solutions could be made with either. Not knowing much about the documents or their proposed use, I think we need a bit more info as to implementation.
Regards
Malcolm

[TonyJollans]

What needs remembering here is that no Office app really has this feature and all that can be done is some kind of workaround. Using very hidden sheets is reasonably effective in Excel but Word has no equivalent.

It depends to some extent on what is required. If macros are required to control updates then the read-only method works fine. If they are required to enable features when reading the document, it is somewhat trickier.

Changing font colour doesn't really work because you need to remember what colour it was before (by character).

Autotext is a nice idea but its big drawback is surely that it is saved in the template, and if secondary files are an acceptable technique there are better approaches. Also, I think, you would need one autotext for each story and it might be difficult to piece the whole thing back together. Then there are problems when tracking changes.

In the general case what is needed is some kind of container which can be hidden. It is possible to embed the document in another doc for saving and to then extract it in an open macro. One downside of this is the loss of document properties (dates are completely lost - stats are hidden outside of Word) which might be important in some instances. I haven't, though, come up with anything better than this yet.

I shall keep an eye on this thread.

[fumei]

In ANY case you still can not force a user to enable macros.

I would not mind a critique of my document that basically states that the document is not functionable unless macros are enabled. In other words, enable macros or don't bother. The message text is in the document, true. But it is removed when the document is opened with macros. and replaced again when the document closes.

[matthewspatrick]

This is a response to a bunch of people :yes



md - I am not quite getting your posted doc. The question was regarding forcing the enabling of macros. Your doc does not require enabling macros - it requires macros to be enabled.


Unless you start messing with the Registry in a Windows startup routine, there is no way to force someone to enable macros. However, if you save the doc as protected, Malcolm's code would force a user to either enable macros or hack the password if the user wanted to edit the doc. Far from perfect, but Word allows little in this regard.



Have you actually used the Excel code? It can not really be considered a workaround.


I respectfully disagree. It is far from perfect, and it does not "force" anyone to enable macros, but if implemented properly it forces the user to either enable macros or hack the password to do anything with the workbook. To me, that sounds like a decent workaround, and it is very easy to implement.



I though about white font, but not autotext, and I'm sure solutions could be made with either.


Like Malcolm, I am leaning against the AutoText. Anne had a potentially good idea; I just fear that it sets up a maintenance nightmare: every time you need to edit the text of the doc, you also need to update the AutoText. Combining Malcolm's warning with document protection and white font for the "real" text sounds pretty promising, IMHO.



Changing font colour doesn't really work because you need to remember what colour it was before (by character).

Not necessarily. I would put in a section break between the "warning" text and the "real text"--that way, the code that "white-outs" the real text can be applied to Sections 2 - N. Then, if the Open event sub deletes out the message, the deletion should go from the beginning of the doc up and including that first section break.

Like Tony, I too shall be watching this thread. It would be nice to have a generally-accepted practice :yes

Patrick

[fumei]

Hmmmm, I accept your respectfully disagree, however, I would respectfully state that I disagree back. The point, the requirement is originally stated as making sure the users enables macros.

I rest my case on the fact....the Excel solution, nor the Word solution does not do anything even remotely like that. The FACT of the matter is neither ensures anything at all. Zip. Nada. It simply informs the users that they have to enable macros. It clearly delineates a choice. This is hardly the same as ensuring an outcome. It does not ensure an outcome.

To reiterate...you have a file (document/spreadsheet...whatever) and for it to function properly macros need to be enabled. To me, a "workaround" is something that actually works around the problem. The Excel solution does NO work to solve the problem. it cause the user to perform action (work) to solve the problem. The user does the work...the solution does not.

Therefore it is an informational exercise, not a working exercise. It does not work around the problem. A work around SOLVES something. The Excel and Word "solutions" do not solve anything.

They make things more agreeable, not user friendly, more informative - all good things - but they do not work around.

[fumei]

Again, I would like comments on my posted doc. The information is there and is adjusted for whether macros are enabled, or not. A section is break is not required.

[fumei]

Again, I would like comments on my posted doc. The information is there and is adjusted for whether macros are enabled, or not. A section break is not required.

[TonyJollans]

It seems to me that this could go round in circles forever.

I think we can all agree that there is no way to *force* a user to enable macros. All that can be done is to inform them that enabling macros is required to gain full functionality of the document/workbook/whatever. The Excel Very Hidden tactic hides the contents of the workbook from normal users unless they enable macros; it is fairly commonly used and works well enough in normal practice.

The protection tactic, as in Malcolm's and Gerry's examples, is as far as can easily be gone in Word. It does not stop people seeing the content of the document; it just stops them updating it.

As for the requested critique of Gerry's code: I don't understand why you change the View before protecting the document and I would like to see screen updating suppresed - in real use I would also expect the code to be password protected. Other than that, it does the job.

The White Font: maybe I'm missing an obvious trick but I don't understand how you can restore original font colours on open without having saved what they were before they were changed to white. As I write, I wonder whether tracking changes (and rejecting the font colour change) might provide a way. Indeed, as I write that, I wonder whether it could also be used to delete all document content on close and restore it on open - I will play with that later.

[fumei]

Agreed.

And agreed.

[joelle]

Hello Everyone,
Sorry to be the last one in the row again (west coast US) ...

All your feedbacks and discussions are awesome!
btw, I'm sorry to have thrown the "mist." What I meant was just like mentioned in Gerry's and Tony's post ...
****************************************
I just wanted to warn them w/ a msg or msgbox that they should open the doc with "enable macro," and when they see the warning they cannot modidy the doc (as in this case, it is password protected). Else, if they open with "macro enabled," they will NOT see the msg and they can modify
the doc.
****************************************
... but along the way now I encounter another roadblock as I already have one document_open???
At least, this is more achievable since it is no longer "Force-in macro enabling" ... more below.



I think we can all agree that there is no way to *force* a user to enable macros. All that can be done is to inform them that enabling macros is required to gain full functionality of the document/workbook/whatever. The Excel Very Hidden tactic hides the contents of the workbook from normal users unless they enable macros; it is fairly commonly used and works well enough in normal practice.

The protection tactic, as in Malcolm's and Gerry's examples, is as far as can easily be gone in Word. It does not stop people seeing the content of the document; it just stops them updating it.
Tony -- You got me right. It is almost impossible to force the users to enable the macro. I just wanted to stop them updating it if they do not open the doc with "macro enabled."




I am not sure you are grasping this. The Excel code does NOT force the user to enable macros. Neither does md's code.

I have expanded md concept and have a warning message in the document. If the document is opened with macros enabled, the warning message is removed. When the document is closed, the warning message is replaced.

NOTE: md what version of Word are you using? I do not have a Protect wdAllowOnlyReading. It is not one of my wdProtectionTypes.
Gerry -- thanks so much for the test document! It achieves what I would like to do, EXCEPT that I already have one document_open sub there, and I'm stuck once more. I cannot run yours smoothly or I cannot run mine.
My document_open sub is below -- pls how do I combine the one below with your document_open sub, then life sure would be all of a sudden more beautiful.

Private Sub Document_Open()
Dim exdate As Date
exdate = "02/06/2006"
If Date < exdate Then
Exit Sub
Else
MsgBox "Template expired"
With ActiveDocument
.Password = "mydog"
.Save
.Close
End With
End If
End Sub

Pls note when I call the sub above "mine," it is not. I got it from KPULS' tremendous help. Fair enough? Pls I still look fwd to some light down the tunnel ...
Many thanks again!

Nee

[mdmackillop]

Hi Nee,
You could follow one code with the other, but for maintenance and clarity I would call them both from the Document Open sub. Note that you'll also need to change the OnClose passwords to suit. eg



Private Sub Document_Open()

MacrosEnabled
DocExpired
End Sub
Sub MacrosEnabled()
ActiveDocument.Unprotect Password:="mydog"
ActiveDocument.Bookmarks("BookEmDano").Select
Selection.Delete
End Sub
Sub DocExpired()
Dim exdate As Date
exdate = "02/06/2006"
If Date < exdate Then
Exit Sub
Else
MsgBox "Template expired"
With ActiveDocument
.Password = "mydog"
.Save
.Close
End With
End If
End Sub

[joelle]

Hi Nee,
You could follow one code with the other, but for maintenance and clarity I would call them both from the Document Open sub. Note that you'll also need to change the OnClose passwords to suit. eg
****************************
Private Sub Document_Open()

MacrosEnabled
DocExpired
End Sub
Sub MacrosEnabled()
ActiveDocument.Unprotect Password:="mydog"
ActiveDocument.Bookmarks("BookEmDano").Select
Selection.Delete
End Sub
Sub DocExpired()
Dim exdate As Date
exdate = "02/06/2006"
If Date < exdate Then
Exit Sub
Else
MsgBox "Template expired"
With ActiveDocument
.Password = "mydog"
.Save
.Close
End With
End If
End Sub


Hello MD,

Genius!!!! A million thanks!

As mentioned, Word VBA is always an alien to me and having to run 2 document_open subs is a tremendously hard task for me. And your latest codes are just beautiful.

Gerry: Wonderful work with the code in the "ReadMacros.doc" file. I appreciate the test file and it is what I've aimed for!!! You GOT the point from the very early beginning. (and "Forcing" users to enable macro is crazily impractical! but it does throw out a challenge tho nobody might want/need it).

I'm now at the bright end of the tunnel now.

I'm so thankful to you Gerry, MD and all others reading and posting to this thread.
(at one time, I thought you're Joanne ... I apologize for the goof ... I lost my concentration at that time)

Very appreciative,
Nee :bow: :bow: :bow:

[joelle]

What needs remembering here is that no Office app really has this feature and all that can be done is some kind of workaround. Using very hidden sheets is reasonably effective in Excel but Word has no equivalent.
It depends to some extent on what is required. If macros are required to control updates then the read-only method works fine. If they are required to enable features when reading the document, it is somewhat trickier.
................................
I shall keep an eye on this thread.
I'm afraid I have to revive this Word vba challenge. I started it more than a year ago. During that thread, I got the donated code from Gerry along with a lot of discussions about the code that at the time I did not have the level to understand what all the discussions were about -- now I do. Also, I should not have marked it as "solved" but I did, shallowly thinking it achieved what I wanted to do. I apologize.

So to keep it short, is there a way to beat the limitation of Word vba wrt the attached code below as mentioned by TonyJollans and Patrick.

Option Explicit

Private Sub Document_Close()
If ActiveDocument.Bookmarks.Exists("BookEmDano") = True Then
With ActiveDocument
.ActiveWindow.View.Type = wdPrintPreview
.Protect wdAllowOnlyFormFields, "abc"
.Save
End With
Else
With Selection
.HomeKey Unit:=wdStory
.TypeText Text:="Please re-open this file with ""Enable Macros"" selected."

.InsertBreak Type:=wdPageBreak
.HomeKey Unit:=wdStory, Extend:=wdExtend
ActiveDocument.Bookmarks.Add Name:="BookEmDano", _
Range:=Selection.Range
End With
With ActiveDocument
.ActiveWindow.View.Type = wdPrintPreview
.Protect wdAllowOnlyFormFields, Password:="abc"
.Save
End With
End If
End Sub

Private Sub Document_Open()

MacrosEnabled
DocExpired
End Sub
Sub MacrosEnabled()
ActiveDocument.Unprotect Password:="abc"
ActiveDocument.Bookmarks("BookEmDano").Select
Selection.Delete
End Sub
Sub DocExpired()
Dim exdate As Date
exdate = "12/31/2007"
If Date < exdate Then
Exit Sub
Else
MsgBox "This template has been expired"
With ActiveDocument
.Close
End With
End If
End Sub

Now my projects requires the use of "making users enable macros" because without macros, I cannot run the "expiration" module, but the tricky part (limitation) already mentioned is:
If the "bookemdano" is not there, the code will create it and save it. By doing that, vba will also the doc without asking user whether or not he/she wants to save it.
Does a workaround exist for this challenge at all??? If you like Word vba challenge, please stop by and give this a shot. I know I would benefit, and you do too, since this chance for you to harvest honor&fame as this seems to be not solveable so far -- I found no other references from the web.
I bow to any light you can shed.

[joelle]

What needs remembering here is that no Office app really has this feature and all that can be done is some kind of workaround. Using very hidden sheets is reasonably effective in Excel but Word has no equivalent.
It depends to some extent on what is required. If macros are required to control updates then the read-only method works fine. If they are required to enable features when reading the document, it is somewhat trickier.
................................
I shall keep an eye on this thread. ==================================
I'm afraid I have to revive this Word vba challenge. I started it more than a year ago. During that thread, I got the donated code from Gerry along with a lot of discussions about the code. At the time I did not have the level to understand what all the discussions were about -- now I do. Also, I should not have marked it as "solved" but I did, shallowly thinking it achieved what I wanted to do. I apologize.

So to keep it short, is there a way to beat the limitation of Word vba wrt the attached code below as mentioned by TonyJollans and Patrick.

Option Explicit

Private Sub Document_Close()
If ActiveDocument.Bookmarks.Exists("BookEmDano") = True Then
With ActiveDocument
.ActiveWindow.View.Type = wdPrintPreview
.Protect wdAllowOnlyFormFields, "abc"
.Save
End With
Else
With Selection
.HomeKey Unit:=wdStory
.TypeText Text:="Please re-open this file with ""Enable Macros"" selected."

.InsertBreak Type:=wdPageBreak
.HomeKey Unit:=wdStory, Extend:=wdExtend
ActiveDocument.Bookmarks.Add Name:="BookEmDano", _
Range:=Selection.Range
End With
With ActiveDocument
.ActiveWindow.View.Type = wdPrintPreview
.Protect wdAllowOnlyFormFields, Password:="abc"
.Save
End With
End If
End Sub

Private Sub Document_Open()

MacrosEnabled
DocExpired
End Sub
Sub MacrosEnabled()
ActiveDocument.Unprotect Password:="abc"
ActiveDocument.Bookmarks("BookEmDano").Select
Selection.Delete
End Sub
Sub DocExpired()
Dim exdate As Date
exdate = "12/31/2007"
If Date < exdate Then
Exit Sub
Else
MsgBox "This template has been expired"
With ActiveDocument
.Close
End With
End If
End Sub

Now my projects requires the use of "making users enable macros" because without macros, I cannot run the "expiration" module, but the tricky part (limitation) already mentioned is:
If the "bookemdano" is not there, the code will create it and save it. By doing that, vba will also the doc without asking user whether or not he/she wants to save it.
Does a workaround exist for this challenge at all??? If you like Word vba
challenge, please stop by and give this a shot. I know I would benefit, and you do too, since this chance for you to harvest honor&fame as this seems to be not solveable so far -- I found no other references from the web.
I bow to any light you can shed.