VBA and Word

[ChiPhiZD]

I am having much difficulty with editing this form that I created. My company knows my knowledge w/ C/C++ and because of this had me create a registration form in Word. Here are my issues, hopefully someone can assist.

1.) I have a few drop down boxes w/ 3 choices. If the user chooses a specific option, i need it to enable different checkboxes below, when the user exits the drop down.

For ex. Size/Life/Money are in the drop down, what is the command to disable different check boxes, the code w/in the brackets is what I need? I have

IF [??? "size is chosen" ???] then activedocument.formfields("checkbox1").enabled = False

2. Our company uses Lotus notes and I would like for the command button I have to automatically open up Lotus and attach the form as an attachment and populate the subject line and To: field. Not sure if this is possible but I have to ask. I have used so far:

activedocument.sendmail

3. I have a few text boxes that I need to test the length for them, so for example if the length text_user1 (text box) is larger than 1, and other boxes are not filled out ... then POP UP ERROR MsgBox. Here is my pseudo

if activedocument.formfields("user1") is > 0 AND user1_address = 0 Or user1_phone = 0 --> pop up MsgBox

4. I am really looking to prevent the users from saving this form to their HDD, or pretty much having access to the menu bars, is there anyway via VBA coding to remove them or, have them remain there but not be able to click on them. Not sure if this will totally screw up their application, if it does, then I not worried about it, but would be nice to have this single document locked down.

Thanks in advance, please remember :p that I am using really basic VBA code in this form because I am pretty much clueless, my forte is C/C++ and I offered to create something directly from that but they want Word.

Thanks again!

[ChiPhiZD]

Anyone? Bumpin Up :wave:

[DKenny]

Welcome to the Forums Chip.

Your post is a liitle unclear. Are you using a word Document as a form or do you have a VBA UserForm included in the document?

If the former, then..Are the controls you are using from the "Forms" or the "Control Toolbox" Toolbar?

[ChiPhiZD]

I have a Word document that is loaded w/ bookmarks and I have VBA coding used within in...I was just handed this file and said...make it idiot proof.

To make it clearer now that I know what you mean....there is no UserForm, just Microsoft Word Objects and a Module.

[DKenny]

I have a few drop down boxes w/ 3 choices

Are these dropdows from the "Forms" or the "Control Toolbox" Toolbar?
This is the key question, the way we handle this is dependant on the answer.

Can you upload a copy of the .doc file?

[ChiPhiZD]

Believe me I wish I could but there is some sensitive company information on this document that prevents me from uploading it....

All of the text, dropdowns, checkboxes are from the forms toobar, I have 2 command buttons that are from the command toolbar


Hope this helps thanks again

[DKenny]

Yes that helps.
You are using Form Field controls which have a very limited events model when compared with VBA Forms controls. You can make this work, but you can only fire the code on the exit event of the dropdown. You do this by assiging the procedure in the "Rum Macro on Exit" DD in the field properties.

Were you to use VBA Forms Controls, you would have a much richer environment to work in. For instance, you can have the code fire on the _change event, which IMHO is much nicer.

I have attached 2 doc.
the first one has an example of the form fields controls that you are using. The on_exit event code for the combobox, in Module1, is..Sub FormComboChange()
Dim sOption As String

sOption = ThisDocument.FormFields("cboOptions").Result

'clear current values
ThisDocument.FormFields("chkSize").CheckBox.Value = False
ThisDocument.FormFields("chkLife").CheckBox.Value = False
ThisDocument.FormFields("chkMoney").CheckBox.Value = False

Select Case sOption
Case "Size": ThisDocument.FormFields("chkSize").CheckBox.Value = True
Case "Life": ThisDocument.FormFields("chkLife").CheckBox.Value = True
Case "Money": ThisDocument.FormFields("chkMoney").CheckBox.Value = True
End Select
End Sub

The second file, shows how you would achieve this result using VBA Forms controls (available on the "Control Toolbox" Toolbar). the procedures here are in the "ThisDocument" module, with the following _Change event procedure for the combobox.
Private Sub cboOptions_Change()

Me.chkSize.Value = False
Me.chkLife.Value = False
Me.chkMoney.Value = False

Select Case Me.cboOptions.Value
Case "Size": Me.chkSize.Value = True
Case "Life": Me.chkLife.Value = True
Case "Money": Me.chkMoney.Value = True
End Select
End Sub

[ChiPhiZD]

Ok see attachment maybe, one of us is not understanding....most likely me

[DKenny]

That's exactly what I'm showing you in ChiPhiZD_BookmarkForms_Example.doc

[ChiPhiZD]

DKenny...you are the man, sorry about that brainfart......

would it be something similar for checking length of textboxes?

Once this done, all I have to worry about is attaching this to the email

[DKenny]

Sure, you would again use the on_exit macro and the code would be something like...Sub txtSomeFieldExit()

If Len(ThisDocument.FormFields("txtSomeField").Result) = 0 Then
MsgBox "This field is required"
End If

End Sub

[New2vba]

Unless I'm missing something Declan, your code is setting checkbox values as opposed to disabling them.

Shouldn't it be something like this?

Sub FormComboChange()
Dim sOption As String

sOption = ThisDocument.FormFields("cboOptions").Result

'clear current values
ThisDocument.FormFields("chkSize").CheckBox.Value = False
ThisDocument.FormFields("chkLife").CheckBox.Value = False
ThisDocument.FormFields("chkMoney").CheckBox.Value = False

Select Case sOption
Case "Size"
ThisDocument.FormFields("chkSize").Enabled = False
ThisDocument.FormFields("chkLife").Enabled = True
ThisDocument.FormFields("chkMoney").Enabled = True
Case "Life"
ThisDocument.FormFields("chkSize").Enabled = True
ThisDocument.FormFields("chkLife").Enabled = False
ThisDocument.FormFields("chkMoney").Enabled = True
Case "Money"
ThisDocument.FormFields("chkSize").Enabled = True
ThisDocument.FormFields("chkLife").Enabled = True
ThisDocument.FormFields("chkMoney").Enabled = False
End Select
End Sub

[DKenny]

No, they cannot.

I hate to say it but I really think you need to reconsider the type of controls you are using on this project.
VBA Form controls have a much richer object model and will allow for enabling/disabling and many more properties and methods that you will probably need.


Sorry Chip, these comments are based on New2's question. I though that was you. Feel free to ignore.

[New2vba]

As far as I can tell, this revised version of your example works, although it may require additional code to clear checkbox values that were selected before the dropdown choice was made.

Edit: Just realised that Declan's original code does clear values!

[ChiPhiZD]

Ok you guys both rock...everything is working great...may not be pretty but its what they want......

I have a command button that attaches this document to an email....what is the command to change the To: and Subject: fields? We use Lotus so that may be a problem and probably wont be able to be too much help, cause I know lotus is a tough one.

Oh and is there any coding to prevent this form from being saved?

Thanks again!!!! I owes yas a beer

[New2vba]

Don't know of any way to prevent a save altogether.

Perhaps you can use the DocumentBeforeSave event to display a message box warning the user not to save a copy of the document?

[ChiPhiZD]

yeah that is what I have there now...

Any ideas on the email subject/to: fields?


oh crap this code doesnt seem to be playing nice....any ideas?

Sub sec4_drop_policy1()
Dim sOption As String

sOption = ActiveDocument.FormFields("sec4_drop_policy1").Result

'clear current values
ActiveDocument.FormFields("sec5_elig_e3_1").CheckBox.Value = False
ActiveDocument.FormFields("sec5_claim1").CheckBox.Value = False
ActiveDocument.FormFields("sec5_banking1").CheckBox.Value = False

Select Case sOption
Case "CES"
ActiveDocument.FormFields("sec5_elig_e3_1").Enabled = True
ActiveDocument.FormFields("sec5_claim1").Enabled = True
ActiveDocument.FormFields("sec5_banking1").Enabled = True
Case "PRIME"
ActiveDocument.FormFields("sec5_elig_e3_1").Enabled = False
ActiveDocument.FormFields("sec5_claim1").Enabled = False
ActiveDocument.FormFields("sec5_banking1").Enabled = False
Case "COSMOS"
ActiveDocument.FormFields("sec5_elig_e3_1").Enabled = True
ActiveDocument.FormFields("sec5_claim1").Enabled = True
ActiveDocument.FormFields("sec5_banking1").Enabled = True
End Select
End Sub

[New2vba]

What's wrong with the code?

[ChiPhiZD]

ahhh just saw it and fixed it...there was no ":" after the case

[New2vba]

Works fine on my machine without the colon, but anyway, glad your problems resolved.

[ChiPhiZD]

Yeah now it works w/o it, who know.....

Can anyone explain the DocumentBeforeSave event to me a bit better...like where I would put it, what exactly to use I have seen this:

But in actuality, I'm not totally sure what it means. :eek2:

Anyway to include this coding in my command button, or where I would put when they are trying to save?

Public WithEvents appWord as Word.Application

Private Sub appWord_DocumentBeforeSave _
(ByVal Doc As Document, _
SaveAsUI As Boolean, _
Cancel As Boolean)

Dim intResponse As Integer

intResponse = MsgBox("Do you really want to " _
& "save the document?", _
vbYesNo)

If intResponse = vbNo Then Cancel = True
End Sub