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 16^th 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]