Hi all,

I need to force users to fill in the properties menu for word and
excel.
I already did it with word. In excel I prompt the properties menu but
you can just click ok. I want the users to fill in everything before
they can save the document. Does anyone know how I can do this.

Thx.

Kevin,

Haven't tried it, but presumably you could code a loop that checks all of
the Builtin Document properties, and if one is not set, don't let them exit
the loop. You could also up the message, so as to 'encourage' them, for
instance, first time you put Please, second time, just tell them, third time
issue profanities<g. Of course, this doesn't stop them just entering
garbage just to shut you up.

How did you do it in Word, and why doesn't that work in Excel as well?

--

HTH

Bob Phillips

"Kevin" wrote in message
m...
Hi all,

I need to force users to fill in the properties menu for word and
excel.
I already did it with word. In excel I prompt the properties menu but
you can just click ok. I want the users to fill in everything before
they can save the document. Does anyone know how I can do this.

Thx.

Hey,

this is the sourcecode in vb word

Sub FileSave()
'
' FileSave Macro
' Saves the active document or template
'
Dim sMessage As String
sMessage = "You need to enter all the properties"
With Dialogs(wdDialogFileSummaryInfo)

StartDisplay:
If .Display = True Then
If .Title = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Subject = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Author = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Keywords = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Comments = "" Then
MsgBox sMessage
GoTo StartDisplay
Else
.Execute
End If
Else
MsgBox sMessage
GoTo StartDisplay
End If
End With
ActiveDocument.Save

End Sub

It doesn't work in excel because With Dialogs(wdDialogFileSummaryInfo)
is not defined.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This might work--put it under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim myProperties As Variant
Dim iCtr As Long
Dim OkToContinue As Boolean

myProperties = Array("title", "subject", "Author", _
"manager", "company", "Category", "Keywords", "Comments")

OkToContinue = True
For iCtr = LBound(myProperties) To UBound(myProperties)
If Me.BuiltinDocumentProperties(myProperties(iCtr)).V alue = "" Then
OkToContinue = False
Exit For
End If
Next iCtr

If OkToContinue Then
'do nothing
Else
MsgBox "Please complete all the properties!" & vbLf & _
"Then try Saving your file again."
Application.Dialogs(xlDialogProperties).Show
Cancel = True 'stop the close
End If

End Sub

Take a look at all the builtinproperties in VBA's help. (I only chose a few.)
And depending on your version of excel, not all are available. So I'd try to
limit them to just the important ones. Else you'll get a lot of comments like
Bob suggested: aaaaaa, ....

Personally, I don't think I'd inflict this misery on the users.

Kevin Simons wrote:

Hey,

this is the sourcecode in vb word

Sub FileSave()
'
' FileSave Macro
' Saves the active document or template
'
Dim sMessage As String
sMessage = "You need to enter all the properties"
With Dialogs(wdDialogFileSummaryInfo)

StartDisplay:
If .Display = True Then
If .Title = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Subject = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Author = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Keywords = "" Then
MsgBox sMessage
GoTo StartDisplay
ElseIf .Comments = "" Then
MsgBox sMessage
GoTo StartDisplay
Else
.Execute
End If
Else
MsgBox sMessage
GoTo StartDisplay
End If
End With
ActiveDocument.Save

End Sub

It doesn't work in excel because With Dialogs(wdDialogFileSummaryInfo)
is not defined.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson