Excel properties

[Kevin]

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.

 

[Bob Phillips]

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?

 

[Kevin Simons]

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]

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)).Value = "" 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.

 

[Bob Phillips]

Kevin,

That is probably because you have not put the code in the right place. As
Dave said, it need to go in the ThisWorkbook code module, and that it
becomes an event triggered by Save (button, Ctrl-S, etc). To get at this
module,

- go into the VB IDE
- select the workbook, if there is a plus sign beside it, click that to open
it
- you'll now see an item called Microsoft Excel Objects, click this if it
also needs opening
- you'll now see the ThisWorkbook object. Double click it to open the code
pane, and copy Dave's code into there.

 

[Kevin Simons]

Thx,

but the thing is that the code is not executed when I save the file
under an existing file name. But when the user saves the document
another time he will be prompted.


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

 

[Dave Peterson]

I just tried it again and it fired each time I tried to do a save.

Maybe the properties are already filled in one of the files????

 

[Kevin Simons]

Hi,

They are empty except the user name and the company. But it doesn't
matter. The main thing was that it worked in word or excel and it works
in word and half in excel. So I think they will be pleased enough.



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