Word 2010 Can VBA actually do what i need ?

[bartrum]

Hello All,

I am totally new to VBA and Macros and so I am unsure that what I need to do is even possible and is probably too ambitious.

I create dozens of similar documents and I am trying to find a way to automate the process. What I would like to do is develop a base template document that has all of the sections anyone could possibly need with formatting and tables, etc..


I have already create a UserForm with the choices for the different headings and the required AutoText (building blocks?) but its linking the users selection to populate a word document at correct point is an issue.

I presume this would be done with Bookmarks and AutoText or Building Blocks ?

By section, I meant heading and content such as:
1. Main Heading

1.1 Sub Heading
1.2 Sub Heading
1.3 Sub Heading

2. Main Heading

2.1 Sub Heading
2.2 Sub Heading
2.3 Sub Heading

So the user would click on 1. Main Heading and 1.2 Sub heading if they wanted those two sections in their new document.

Is this actually possible or is this too ambitious for VBA?

If this isn’t possible, can anyone suggest an alternative way to perform a similar task?

Any help or guidance the experts can offer would be greatly appreciated

hope this makes sense

Bartrum

[gmaxey]

It is certainly possible to make something work. Paragraphs and tables in a document are indexed. So if you template contains all of the paragraphs and tables, then it is just a matter of keeping the ones the user selects and deleting the rest working from the end of the document to the start:


Private Sub CommandButton1_Click()
'Working from the end of the document to the start
If Not chk_T2 Then ActiveDocument.Tables(2).Delete
If Not chk_T1 Then ActiveDocument.Tables(1).Delete
If Not chk_SH2 Then ActiveDocument.Paragraphs(3).Range.Delete
If Not chk_SH1 Then ActiveDocument.Paragraphs(2).Range.Delete
If Not chk_H1 Then ActiveDocument.Paragraphs(1).Range.Delete

End Sub

[bartrum]

Hi Greg

many thanks for the super quick response - i will try this shortly

thank you again

[bartrum]

Hi Greg

Again thanks for the reply but this isnt quite what i was after

I have attached an example of what i am trying to achieve and the poor attempt of code below




Private Sub option2_Click()

If Option2.Value = True Then
Option1.Value = False
End If


End Sub

Private Sub Option1_Click()

If Option1.Value = True Then
Option2.Value = False
End If


End Sub

Private Sub Option3_Click()

If Option3.Value = True Then
Acc01.Value = False
End If

End Sub

Private Sub Acc01_Click()

If Acc01.Value = True Then
Option1.Value = False
End If

End Sub

Private Sub Acc02_Click()

End Sub

Private Sub ClearFields_Click()

'the Below will clear all check boxes
Question_clear = MsgBox("Are you sure you want to clear all boxes? ", vbYesNo + vbQuestion, "Question")

If Question_clear = 7 Then Exit Sub
If Question_clear = 6 Then

Option1.Value = False
Acc01.Value = False
Acc02.Value = False
Option2.Value = False
Acc03.Value = False
Acc04.Value = False
Option3.Value = False
Acc05.Value = False
Acc06.Value = False
Option4.Value = False
Acc07.Value = False
Acc08.Value = False

End If

End Sub

thank again for any assitance

Bartrum

[gmaxey]

Bartram,

Since your Submit_Click event is empty and contains no code it is not surprising that your code does not do what you want it to do. Additionally, you have not made it clear in code or anywhere else what it is exactly the you want the code to do.

For example, lets say "Option 1, Accessory 1 and Accessory 5" is checked by the user and everything else is unchecked, what exactly do you want the resulting document to look like?

[bartrum]

Hi Greg

Sorry I should have made it clearer, this the limit of my knowledge with VBA and the doc has been upload is an example of what i am trying to do. So basically the idea is the following -


1. The user makes a selection of the various options and or accessories, clicks the submit button, some of the selections are incompatible hence why some selections get unchecked.
2. When they click submit, only certain parts of the document get changed with either predefined paragraph from the predefined autotext or the unwanted paragraphs are removed.

3. Some paragraphs will never change so i don’t need to be modified (sections marked with "This section will never change")

so my problem (apart from not knowing VBA in any way shape or form :( ) is a how to link the submit button to the UserForm selections to either insert or delete specific paragraphs to the selections. i thought this might be possible to do with bookmarks.

I am open to suggestions on how to best perform this

Thank you again for your time

Bartrum

[fumei]

Hi Bartrum. You have basically reiterated your original post in explanation. I will attempt to sum it up.

You have some paragraphs that will ALWAYS be there.
You have some paragraphs that you WANT to be there (indicated by the user selecting them, by name or description I assume).
You have some paragraphs that you DO NOT WANT (indicated by the user selecting them, by name or description I assume).

Good so far?

What Greg posted in his first post does this. You state that "but this isnt quite what i was after". I think it actually is. First off, it is much easier to remove content than it is to insert content. This is what Greg's Delete instruction does. The concept is that EVERYTHING is in the document, and the user selection alters things. What is NOT selected is removed (and the content selected remains in the document - as it is already there).

"how to link the submit button to the UserForm selections to either insert or delete specific paragraphs to the selections. i thought this might be possible to do with bookmarks."

Indeed you can use bookmarks, but again, the concept is that everything is already there. There is no insertion, only removals. Paragraphs that should always be there are simply not listed as a choice.

I am not sure why you have the unchecking.

"some of the selections are incompatible hence why some selections get unchecked."

If they are incompatible, then why are they even there at all? It seems to me that if you work out - exactly, precisely - the logic you need, this should not be all that difficult. The principle involved has come up a number of times and solutions quite similar to what Greg suggests generally work.

[bartrum]

Hi Fumei

Thank you for taking the time to reply.

Your summary is correct this is what I would like to achieve.

The reason for the unchecking and why some items may be incompatible is for example a user may select Blu-ray Player but as an option they have selected a DVD disc rather than a Blu-ray disc. This is a very simplified example. Yes, it would seem obvious that the two choices are incorrect but it is to ensure that a simple mistake is not made by the user. This will be a technical based document so they may not be aware of what is correct; it’s to ensure that the user doesn’t select the wrong combination by mistake.

So going back to Greg’s example code, as it is not clear to me, Greg says the Paragraphs and Tables are “indexed” – how can I see what these values are i.e. the 16th paragraph has a value of ?? or is it just as simple as having to manually count the number of paragraphs i.e. this paragraph is number 6

I may have exaggerated my knowledge of VBA from being a total newbie, to no knowledge, but that appears to be obvious J I am more than willing to learn, I just need to be hand held whilst I get to grips with the concept of scripting etc. but this may be something that this forum can’t offer

Again thank you and Greg for taking the time to respond

Bartrum

[gmaxey]

Bartrum,

Don't be discouraged. We all started basically the same place you are (i.e., knowing nothing). Basically yes, in my simplistic example, you simply count the paragraphs and tables. So if the base template has "all" the content and the content is 20 paragraphs and 4 tables, then you simple delete the paragraph or table by index the user didn't check in the form. You work from the bottom up in order to preserve the index.

[fumei]

"The reason for the unchecking and why some items may be incompatible is for example a user may select Blu-ray Player but as an option they have selected a DVD disc rather than a Blu-ray disc. This is a very simplified example. Yes, it would seem obvious that the two choices are incorrect but it is to ensure that a simple mistake is not made by the user. This will be a technical based document so they may not be aware of what is correct; it’s to ensure that the user doesn’t select the wrong combination by mistake"

Understood. This is a matter of logic and while certainly not trivial it can be handled logically. And in a variety of ways. TESTING and action in the final Sub. Real-time changes on the userform (eg. selecting Blu-ray removes the DVD option). It all depends on clear and precise logic as you determine what is required.

[bartrum]

Hi Greg and Fumei

thanks for the advice, I will continue to follow your advice and continue with Testing

have a good evening

Bartrum

[bartrum]

Hi Fumei and Greg or Greg and Fumei :)

I have worked out a solution that works for me, as in it does what the user wants to expierence, so the coding may not be pretty :(

so now when you open the form, a userform opens and you can select, for example, Product1 and any accessory related to Product1 and the Product2 and its accessories will be deselected if selected and vice a versa. The same applies to Product 3 and 4

If you run the macro again and click "Clear All Sections" it will unhide all the hidden bookmarks (need to added a function to that the userform will show again)

thoughts?

many thanks again for your time



Private Sub Product1_Click()

ActiveDocument.Bookmarks("Product1").Range.Font.Hidden = Not Product1.Value

If Product1.Value = True Then
Product2.Value = False
Accessory3.Value = False
Accessory4.Value = False

End If

End Sub
Private Sub Product2_Click()

ActiveDocument.Bookmarks("Product2").Range.Font.Hidden = Not Product2.Value

If Product2.Value = True Then
Product1.Value = False
Accessory1.Value = False
Accessory2.Value = False

End If

End Sub
Private Sub Product3_Click()

ActiveDocument.Bookmarks("Product3").Range.Font.Hidden = Not Product3.Value

If Product3.Value = True Then
Product4.Value = False
Accessory7.Value = False
Accessory8.Value = False

End If

End Sub
Private Sub Product4_Click()

ActiveDocument.Bookmarks("Product4").Range.Font.Hidden = Not Product4.Value

If Product4.Value = True Then
Product3.Value = False
Accessory5.Value = False
Accessory6.Value = False

End If

End Sub
Private Sub Accessory1_Click()

ActiveDocument.Bookmarks("Accessory1").Range.Font.Hidden = Not Accessory1.Value

If Accessory1.Value = True Then
Product2.Value = False
Accessory3.Value = False
Accessory4.Value = False

End If

End Sub
Private Sub Accessory2_Click()

ActiveDocument.Bookmarks("Accessory2").Range.Font.Hidden = Not Accessory2.Value

If Accessory2.Value = True Then
Product2.Value = False
Accessory3.Value = False
Accessory4.Value = False

End If

End Sub
Private Sub Accessory3_Click()

ActiveDocument.Bookmarks("Accessory3").Range.Font.Hidden = Not Accessory3.Value

If Accessory3.Value = True Then
Product1.Value = False
Accessory1.Value = False
Accessory2.Value = False

End If

End Sub
Private Sub Accessory4_Click()

ActiveDocument.Bookmarks("Accessory4").Range.Font.Hidden = Not Accessory4.Value

If Accessory4.Value = True Then
Product1.Value = False
Accessory1.Value = False
Accessory2.Value = False

End If

End Sub
Private Sub Accessory5_Click()

ActiveDocument.Bookmarks("Accessory5").Range.Font.Hidden = Not Accessory5.Value

If Accessory5.Value = True Then
Product4.Value = False
Accessory7.Value = False
Accessory8.Value = False

End If

End Sub
Private Sub Accessory6_Click()

ActiveDocument.Bookmarks("Accessory6").Range.Font.Hidden = Not Accessory6.Value

If Accessory6.Value = True Then
Product4.Value = False
Accessory7.Value = False
Accessory8.Value = False

End If

End Sub
Private Sub Accessory7_Click()

ActiveDocument.Bookmarks("Accessory7").Range.Font.Hidden = Not Accessory7.Value

If Accessory7.Value = True Then
Product3.Value = False
Accessory5.Value = False
Accessory6.Value = False

End If

End Sub
Private Sub Accessory8_Click()

ActiveDocument.Bookmarks("Accessory8").Range.Font.Hidden = Not Accessory8.Value

If Accessory8.Value = True Then
Product3.Value = False
Accessory5.Value = False
Accessory6.Value = False

End If

End Sub
Private Sub ClearFields_Click()

'the Below will clear all check boxes
Question_clear = MsgBox("Are you sure you want to clear all boxes? ", vbYesNo + vbQuestion, "Question")

If Question_clear = 7 Then Exit Sub
If Question_clear = 6 Then

Product1.Value = False
Product2.Value = False
Product3.Value = False
Product4.Value = False
Accessory1.Value = False
Accessory2.Value = False
Accessory3.Value = False
Accessory4.Value = False
Accessory5.Value = False
Accessory6.Value = False
Accessory7.Value = False
Accessory8.Value = False

End If

If Question_clear = 7 Then Exit Sub
If Question_clear = 6 Then

'the Below will Show all hidden bookmarks
ActiveDocument.Bookmarks("Product1").Range.Font.Hidden = Product1.Value
ActiveDocument.Bookmarks("Product2").Range.Font.Hidden = Product2.Value
ActiveDocument.Bookmarks("Product3").Range.Font.Hidden = Product3.Value
ActiveDocument.Bookmarks("Product4").Range.Font.Hidden = Product4.Value
ActiveDocument.Bookmarks("Accessory1").Range.Font.Hidden = Accessory1.Value
ActiveDocument.Bookmarks("Accessory2").Range.Font.Hidden = Accessory2.Value
ActiveDocument.Bookmarks("Accessory3").Range.Font.Hidden = Accessory3.Value
ActiveDocument.Bookmarks("Accessory4").Range.Font.Hidden = Accessory4.Value
ActiveDocument.Bookmarks("Accessory5").Range.Font.Hidden = Accessory5.Value
ActiveDocument.Bookmarks("Accessory6").Range.Font.Hidden = Accessory6.Value
ActiveDocument.Bookmarks("Accessory7").Range.Font.Hidden = Accessory7.Value
ActiveDocument.Bookmarks("Accessory8").Range.Font.Hidden = Accessory8.Value

End If

End



End Sub
Private Sub Submit_click()

Call Product1_Click
Call Product2_Click
Call Product3_Click
Call Product4_Click
Call Accessory1_Click
Call Accessory2_Click
Call Accessory3_Click
Call Accessory4_Click
Call Accessory5_Click
Call Accessory6_Click
Call Accessory7_Click
Call Accessory8_Click

End

End Sub

[fotodj]

:)