Macros and Protect / Unprotect

D

Diana Morrison

I have created a form that a manager completes. I created a macro that
changes the font to one colour, creates a header and saves to a certain file.
It's run from a macro button on the end of the form. However, if the form
is locked, i can't run it. Is there a way in the macro to first unlock the
form, then run the macro, then password protect the form again?
 
J

Jay Freedman

There shouldn't be any problem getting a macrobutton to run a macro,
even in a protected form. The problem is that the macro can't make any
changes to the document outside the form fields while the protection
is turned on.

To work around this, set up your macro like this:

Sub DoSomething()
With ActiveDocument
If .ProtectionType <> wdNoProtection Then
.Unprotect Password:="myPassWord"
End If
End With

' make your formatting changes here

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True, Password:="myPassWord"
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
D

Diana Morrison

This worked great - thanks!

One other tiny problem...after the new document is updated, protected and
saved, when you re-open it the userform appears. How do i get rid of that?

Thanks,
Diana
 
J

Jay Freedman

Hmmm. A userform can't show itself; it *must* be called by a separate
macro. And because it's showing up when the document opens, that macro
has to be one of two kinds of "auto" macros.

Look in the ThisDocument module of the template -- is there a
Document_Open routine there? (That's also mentioned in the article I
pointed to.)

Are there any macros in the document itself? That's unlikely, because
Word throws a security warning message or blocks macros altogether
unless the security level is set to Low. But as Sherlock Holmes
supposedly said, "When you have eliminated the impossible, what is
left, no matter how unlikely, is the truth."

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
D

Diana Morrison

Jay, that was the problem - a document_open routine in the This Document
module. (I'm in WAY over my head). Where would i be without all the help
I've received from you people over the years? Thank you so much.
Diana Morrison
 
J

Jay Freedman

You're welcome.

When you have some spare time(!), read that article. It'll help to get your
head above water. <g>

I will say that it's unusual to find both an AutoNew macro and a
Document_Open macro in the same template; developers usually adopt one pair
of auto macros or the other.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
S

Sin

Hi Jay,

I've tried your code but realise one problem with it - i.e. I could only run
the macro once (by clicking the macrobutton) on the protected form, the 2nd
attempt onward won't work unless I close the document and re-open it. Are
there anyway to work around it so that user can click on the macro as many
time as they want without close and re-open the form?
 
J

Jay Freedman

I have no problem at all getting this macro to run multiple times
without closing the document. Exactly what happens when you try it?
"It doesn't work" is not a useful description.

It would help if you post your actual macro.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
S

Sin

My macro actually unlock the form, change colors, print the form and re-lock
the form. Once the form is printed, it will disable all other macrobutton on
the page (I had another macrobutton on the page to display a msgbox which
works multiple times as well). The problem seems to lie with this particular
macrbutton only.

Sub test()

With ActiveDocument
If .ProtectionType <> wdNoProtection Then
.Unprotect Password:="tony"
End If
End With

Dim tblCurrent As Table
Dim lngUndoCount As Long
Dim rgeStart As Range

Set rgeStart = Selection.Range

'Set to false to make code run faster
'In case of large document with many tables
Application.ScreenUpdating = False

lngUndoCount = 0
For Each tblCurrent In ActiveDocument.Tables
tblCurrent.Shading.BackgroundPatternColor = wdColorWhite
lngUndoCount = lngUndoCount + 1
Next

With Application
'Reset to true or if user moves dialog around,
'there wil be traces on the screen
.ScreenUpdating = True
.Dialogs(wdDialogFilePrint).Show
.ScreenUpdating = False

ActiveDocument.Undo lngUndoCount

rgeStart.Select

.ScreenRefresh
.ScreenUpdating = True
End With

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True, Password:="tony"

End Sub

Thx
sin
 
J

Jay Freedman

This is only a guess, but here's what I think may be happening:

When you click the macrobutton to launch the macro, the Selection is
on the macrobutton. As the macro starts, it assigns that location to
rgeStart. Toward the end of the macro, you call rgeStart.Select, and
then you protect the form.

If the Selection is in a non-form-field piece of text when you start
form protection, the cursor is forced to the next form field. I think
it's at that time the macrobutton loses it. The trouble is, I can't
reproduce the behavior you're seeing in a simplified version here. My
macrobutton just continues to work.

You could try putting the Selection in the next form field after the
macrobutton instead. If that doesn't help, you can email the form to
me if you want me to examine it.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
S

Sin

Sorry that I cannot send you the form since it's confidential.

Basically, the first 2 pages of the form is all writing with no form field,
follwoing your comment below, what I found is that the macrobutton will work
so long as it's placed on page with form field (regardless of the last cursor
position) but it won't work if i place it on pages without form fields. Are
you way to work around this?
 
J

Jean-Guy Marcil

Sin was telling us:
Sin nous racontait que :
Sorry that I cannot send you the form since it's confidential.

Basically, the first 2 pages of the form is all writing with no form
field, follwoing your comment below, what I found is that the
macrobutton will work so long as it's placed on page with form field
(regardless of the last cursor position) but it won't work if i place
it on pages without form fields. Are you way to work around this?
Click to expand...

I wrote the restart code....
At the time, it was in a different thread. I had no idea that it was going
to be used sandwiched between two Unprotect/Protect statements, and I did
not know it was destined to be activated from a MACROBUTTON.

So, I never tested for that.

Now, instead of playing around with a MACROBUTTON, if you are dealing with a
template, why don't you use a Toolbar button?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
S

Sin

The form is a downloadable template and the toolbar button won't be visible
to other users.

I managed to work around the problem by putting in a invisiable field on the
first page, as such, the button will work multiple times.

Thx
Cindy
 
J

Jean-Guy Marcil

Sin was telling us:
Sin nous racontait que :
The form is a downloadable template and the toolbar button won't be
visible to other users.

I managed to work around the problem by putting in a invisiable field
on the first page, as such, the button will work multiple times.
Click to expand...

Glad you worked it out!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

DJElliott

Jay, I am still struggling to create a form in MS Word 2003 (XP). I want to
use a macro button that allows a user to save/close a document and one that
routes it.
I tried using a macrobutton, but run into the protected/unprotected
minefield. I double click on the macrobutton and nothing happens. :)

Public Sub SaveIt()
'
With ActiveDocument
If .ProtectionType <> wdNoProtection Then
.UnProtect Password:=""
End If
End With
'
'Enable ENTER key on exit
Application.Run MacroName:="ReinstateEnterKey"
MsgBox Prompt:="SAVES and CLOSES supplier maintenance document, but
does not route it."
If ActiveDocument.Saved = False Then ActiveDocument.SaveAs
ActiveDocument.protect Type:=wdAllowOnlyFormFields, _
NoReset:=True, Password:=""
'Close saved supplier maintenance form
ActiveDocument.Close
End Sub
 
J

Jay Freedman

The problem with using a macrobutton field in a protected form is that you can't
really double-click it. Word sees the first click as an attempt to select
something in the protected area of the form, and before your second click
registers as being part of a double-click, the cursor is forced into the next
available form field. The macro never gets invoked.

The solution is another macro that tells Word to execute the macrobutton field
on a single click instead of a double-click. See the "Double-click or
single-click" section of
http://www.word.mvps.org/FAQs/TblsFldsFms/HLinksInForms.htm.
 
G

Graham Mayor

Or use a custom toolbar button rather than a macrobutton?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA word change keys 0
MS365 WORD: How to Create Macro Button to CLEAR ALL fields in fill-in form 1
Protected word template - vb/macro to update footer 7
Clearing Fields in a Word Form 5
macro to unprotect and protect 1
Building Blocks and Macros 5
unprotect / protect a form to run a macro 1
unprotect run a macro then protect a form again 1

Top