Solved: Userforms and macro interaction

[Procyan]

Hello all. I would first like to complement the site designers. Vbax is one of the best layouts I have seen. Very user friendly and informative.
I have thoroughly scanned the posts and the KBase to attempt to solve this problem I have on my own but alas I cannot.

I have created a template with 10 Text Form Fields using Word 2003. The 10 fields on the document are the only thing that change on the standard form. Next I created a userform to fill in the blanks and made a macro to combine the two together. I just have a few bugs to work out and it shall be complete. What I would like to happen is when you click on the template it will load and the user will fill out the form. Then when the user clicks ok, it will save and exit. I would prefer that the user have nothing else to do because I work with a bunch of dummys that would play with it and probably mess it up.

1. I have my form auto start when you load the template. I have it coded so if they click Cancel it will unload, but I can still click on the X on the top right of my userform and get to the template below. How do I disable the X?

2. I want to protect my work, but when it is protected I cannot insert my data into the form. I realize that I need to unprotect then re-protect the Form Fields but lack the knowledge to program. I would like to learn though.

3. Upon closing the doc and exiting Word, it prompts for a save file name. If you click ok it will save and exit. If you click cancel it errors and goes to debug. What do I have to write to stop that error?

4th and final. "Thank god huh" How do I compile my project together so it will work independently. The userfrom I am using did not originally come from the template I am using it with. I guess I'm trying to say. How do I wrap it all up?

Below is the code that I wrote. Shield your eyes this is my first VBA I have written. It is probably pretty rough so any thing that would make it more professional would help also.



Private Sub AutoNew()
Master_Fill_In.Show


End Sub




Private Sub zzCancelButton_Click()
Unload Me

ActiveDocument.Close SaveChanges:=False
Application.Quit

End Sub




Private Sub zzOKButton_Click()
MonthYearT.Value = UCase(MonthYearT.Value)

PocT.Value = UCase(PocT.Value)
SwitchT.Value = UCase(SwitchT.Value)
CurrentDateT.Value = UCase(CurrentDateT.Value)
NameGradeT.Value = UCase(NameGradeT.Value)
PlacesVisitedT.Value = UCase(PlacesVisitedT.Value)
DatesT.Value = UCase(DatesT.Value)
PurposeT.Value = UCase(PurposeT.Value)



With ActiveDocument
.Bookmarks("zulutime").Range.Text = ZuluTimeT.Value

.Bookmarks("monthyear").Range.Text = MonthYearT.Value
.Bookmarks("poc").Range.Text = PocT.Value
.Bookmarks("switch").Range.Text = SwitchT.Value
.Bookmarks("contact").Range.Text = ContactT.Value
.Bookmarks("currentdate").Range.Text = CurrentDateT.Value
.Bookmarks("namegrade").Range.Text = NameGradeT.Value
.Bookmarks("placesvisited").Range.Text = PlacesVisitedT.Value
.Bookmarks("dates").Range.Text = DatesT.Value
.Bookmarks("purpose").Range.Text = PurposeT.Value

End With

Application.ScreenUpdating = True
Unload Me
.Close SaveChanges:=True
Application.Quit

End Sub



Thanks in advance,
Mike

P.S. This is a form that requires all caps.

[xCav8r]

1. I have my form auto start when you load the template. I have it coded so if they click Cancel it will unload, but I can still click on the X on the top right of my userform and get to the template below. How do I disable the X?
Private Sub AutoNew()
On Error GoTo Error_AutoNew

Master_Fill_In.Show

Exit_AutoNew:
Exit Sub

Error_AutoNew:
On Error Resume Next
Unload oForm
Set oForm = Nothing
ActiveDocument.Close wdDoNotSaveChanges

Resume Exit_AutoNew

End Sub


2. I want to protect my work, but when it is protected I cannot insert my data into the form. I realize that I need to unprotect then re-protect the Form Fields but lack the knowledge to program. I would like to learn though.
From experience, I would say don't bother with protecting the document--especially where technically challenged and/or lazy people are involved. It's more trouble than it's worth. This is a human issue. Not a problem with Word (for a change ;)). Avoid the ignorant complaints about your document not working by leaving it unprotected. They can always figure out how to reopen it.


3. Upon closing the doc and exiting Word, it prompts for a save file name. If you click ok it will save and exit. If you click cancel it errors and goes to debug. What do I have to write to stop that error?
Add more error handling to what I started. Get the err.number and write an If err.number = x then do this.


4th and final. "Thank god huh" How do I compile my project together so it will work independently. The userfrom I am using did not originally come from the template I am using it with. I guess I'm trying to say. How do I wrap it all up?
I'm not sure I understand what you want to do. If the userform is stored in the project for the template in question, then it's already "wrapped up". If you compile from the VBE and get no errors, then you're good to go!!!


P.S. This is a form that requires all caps.
That's an unnecessary hassle for your users. Just make the text uppercase after they enter it. For example, UCase("i wanna be in uppercase") = I WANNA BE IN UPPERCASE

[Procyan]

Thank you for your input xCav8r,
I was unable to disable the X in my userform but the code you submitted has fixed the error with the save command.
I agree with you that document protection isn't nessary but how do I protect the code and the userform I have created from being tamperd with?
I included the PS incase anyone saw all of the UCase commands and was wondering what the heck they were for. Its for a government form and you know how the government can be. It already makes any text entered into UCase.
Once again thanx for the help.
Mike

[Steiner]

You can disable the X this way (it will be shown, but won't do anything):

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = True
End Sub


Daniel

[xCav8r]

Oh, whoops! That's what I get for reading carefully. I see all the Ucases now. Don't know where the mind was. ;)

[fumei]

I would also like to point out that you do not need:
PocT.Value = UCase(PocT.Value)
SwitchT.Value = UCase(SwitchT.Value)
CurrentDateT.Value = UCase(CurrentDateT.Value)
NameGradeT.Value = UCase(NameGradeT.Value)
PlacesVisitedT.Value = UCase(PlacesVisitedT.Value)
DatesT.Value = UCase(DatesT.Value)
PurposeT.Value = UCase(PurposeT.Value)

Youi do not need any of that, as you can simply UCase the values into the bookmarks directly, as in:

.Bookmarks("purpose").Range.Text = UCase(PurposeT.Value)

There is no point in UCase a value, then use that UCase'd value again.

Next, you state that you want to protect you work. You are correct in that if it is protected you can not insert the values. True, if you are using bookmarks to insert the values into. HOWEVER, you certainly can insert your values from the UserForm into FormFields when it is protected. In fact....that is precisely what formfields are designed to do. FormFields would protect your work.

[MOS MASTER]

Hi And Welcome to VBAX! :hi:

Like Gerry mentioned theres no need to unprotect/reprotect the document for writing those values to the formfields you're using!

For writing to those values you must use:
ActiveDocument.FormFields("purpose").Result = UCase(PurposeT.Value)


Enjoy! :whistle:

[Procyan]

Thank you fumei and Steiner the code works like a charm, and I have switched around the code so the UCase is next to the value. I don't know why I didn't think of that before.

The form works great now. Thanks to everyone that helped me out. I have pasted the new code below that works so if someone else has a simular question they could refer back to this.

Private Sub AutoNew()

Master_Fill_In.Show

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = True
End Sub
Private Sub zzCancelButton_Click()

Unload Me
ActiveDocument.Close SaveChanges:=False
Application.Quit
End Sub
Private Sub zzOKButton_Click()

With ActiveDocument
.FormFields("BookMark").Result = TextBoxIn UserForm
.FormFields("monthyear").Result = UCase(MonthYearT.Value)
.FormFields("poc").Result = UCase(PocT.Value)
.FormFields("switch").Result = UCase(SwitchT.Value)
.FormFields("contact").Result = UCase(ContactT.Value)
.FormFields("currentdate").Result = UCase(CurrentDateT.Value)
.FormFields("namegrade").Result = UCase(NameGradeT.Value)
.FormFields("placesvisited").Result = UCase(PlacesVisitedT.Value)
.FormFields("dates").Result = UCase(DatesT.Value)
.FormFields("purpose").Result = UCase(PurposeT.Value)
End With
Tryagain:
On Error Resume Next
Application.ScreenUpdating = True
ActiveDocument.Close SaveChanges:=True
On Error GoTo Tryagain
Unload Me
Application.Quit
End Sub

[Procyan]

Great it lost the formatting Ohh well Sy guys

[MOS MASTER]

Great it lost the formatting Ohh well Sy guys
Glad you've made it work!

To retain the Formats of the VBE. Select the code and press the VBA button in the Forum editor. The code will be formated as per VBIDE.

Later..:whistle:

[mdmackillop]

Hi Procyan,
If you select your code and click on the VBA button, it formats it as shown above.
Regards
MD