Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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. -- HTH Bob Phillips "Kevin Simons" wrote in message ... Thx, I needed to change a bit of code but it works. The funny thing is that it only works when I ask to run. If I click on the save button he does not start the macro. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel properties
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Properties | Excel Discussion (Misc queries) | |||
Excel Properties Tab on Doc Save | Excel Discussion (Misc queries) | |||
Excel text box properties | Excel Discussion (Misc queries) | |||
Excel Properties | New Users to Excel | |||
Excel properties - 'archive'? | Excel Discussion (Misc queries) |