Code only works fully when run from VBA editor

J

johnnykunst

I have the following code, all of which works fine when run from the VBA editor:

Private Sub CommandButton1_Click()
If ActiveDocument.FormFields("nameDD").Result = "ENTER NAME" Then
MsgBox "You forgot to fill in your name. This field MUST be filled in before you can save the document"
Exit Sub
End If
Call RunSpellcheck
Dim pStr As String
pStr = "C:\Users\John\Desktop\" 'Your directory
pStr = pStr + ActiveDocument.FormFields("nameDD").Result
pStr = pStr + " "
pStr = pStr + ActiveDocument.FormFields("classificationDD").Result
pStr = pStr + " "
pStr = pStr + ActiveDocument.FormFields("wardDD").Result
pStr = pStr + " .docx"
ActiveDocument.SaveAs FileName:=pStr
Dim sCode As String
MsgBox "Your Intelligence report was saved to the central WINTEL inbox for processing & emailing. No further action is required; it is now safe to close the document"
End Sub


However, this code is assigned to a button on my Word template and when it is fired from this button the following section does not work:

If ActiveDocument.FormFields("nameDD").Result = "ENTER NAME" Then
MsgBox "You forgot to fill in your name. This field MUST be filled in before you can save the document"
Exit Sub
End If

The section is to check that a field has been filled in, and if it hasn't, to stop the macro, if it has, to continue with the rest of the macro.
 
G

Graham Mayor

What happens when you try?
1. I take it that this is a button from the ActiveX legacy control set
2. That the form is not in design mode.
3. That the form is protected.
4. That the formfield bookmark name matches "nameDD"
5. That the first entry in the drop down list is "ENTER NAME" - in upper
case - if not change to

If UCase(ActiveDocument.FormFields("nameDD").Result) = "ENTER NAME" Then
MsgBox "You forgot to fill in your name. This field MUST be filled in
before you can save the document"
Exit Sub
End If

So configured it works for me.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
J

johnnykunst

It still doesn't work, even though 1-5 are as you state.
What I have found however, is that it works if I do the following:
The template is CWS Intel Template 2010. When i open it, it becomes Document1. If I go into VBA, the releveant codes (as per above) reside as modules in the CWS Intel Template.
If I paste the code into Project (Document1) and then click the button on the form, it works as it should. If I save it like this, when I click the command button, it does not stop the code as it should if ENTER NAME is in nameDD- the message prompt pops up, but when I click OK, it goes straight into RunSpellCheck rather than stopping.
 
J

johnnykunst

I managed to fix it, only to encounter another problem:
When a new document is created through the above code, when I open the new document, I get the message "The file cannot be opened because there are problems with the contents" but no error detail.
 
D

Doug Robbins - Word MVP

Are there any illegal (for a filename) characters included in the filename
as a result of the concatenation of the contents of the formfields.

Also, it is safer to use & rather than + to build a string.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
J

johnnykunst

Thanks for your help- the problem seems to have been caused by having an identical document on the desktop- now I've deleted it the original runs fine.
 
J

johnnykunst

Turned out to be because I had set the module name the same as the macro it contained

I then had the problem of the document saving as un recognised format in some instances, but I worked out this was because of illegal characters such as \/|.:?" being entered into the text form field and ending up in the filename, so I inserted an on-exit macro from the fileds to remove the illegal characters. Now works perfectly, macro is

Sub KeywordsCleanUp(
Dim s As Strin
s = ActiveDocument.FormFields("Keywords").Resul


ActiveDocument.FormFields("Keywords").Result = RemoveIllegalCharacters(s

End Su

Which uses the following function

Function RemoveIllegalCharacters(s As String) As Strin
Dim e As Varian
e = Array("\", "/", ":", "*", "?", """", "<", ">", ".", "|", "[]", Chr$(13)
Dim temp As Strin
temp =
Dim c As Varian
For Each c In
temp = Replace(temp, c, ""
Nex
RemoveIllegalCharacters = tem

End Functio
 

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

Top