Solved

VBA - Word building up document with buildingblocks with userform

Posted on 2015-02-17
23
453 Views
Last Modified: 2016-02-11
Hi guys, I need some advise,

I'm trying to create a template that can create documents with some flexibility, out from a userform (lots of checkboxes), building blocks.

What I need is that the document to be flexible, so what ever the user checks off in the userform will come out on the document in a specific order, and be able to change the choosen checks if the user by mistake forgot something, so be able to access the userform again.

What i got so far i that i have worked the other way around, i have all the text in the document and from what you check in the userform will stay and everything else will be deleted, this i have made with bookmarks, so in short its simply delete the bookmark and its content if not checked in the userform.

But i need some advise, since if i do it my way i will not be able to add a building block again since the bookmarks is not there anymore, so is there a way to create the document from various checkboxes checked in the userform and it will insert the building blocks in the correct order?

kindly advise.
0
Comment
Question by:Hakum
  • 9
  • 7
  • 7
23 Comments
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613744
How about when you delete from the doc you write the info to a document property so you can read it late?

Sorry for brevity, using phone
0
 
LVL 1

Author Comment

by:Hakum
ID: 40613745
well anything would work for me if it will work in any version of office, but i haven't worked with that before could you kindly explain how this work to me?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613748
At this stage i am sitting at a restaurant table in a foreign city - I'd have to google.

I'm assuming that you only need help with accessing the doc prop and addind..?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613750
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613759
I guess you could give the property a name like form1checks... you could create a single property for all the items and separate each with a special char and use split function to create an array

Or you could load each item into its own array

I probably would use the array and separate my items with a pipe character  |
0
 
LVL 1

Author Comment

by:Hakum
ID: 40613764
hehe awesome! :) well to be honest i'm not that awesome to VBA just yet but still learning, but if you could give me an example when ever you have time would be much appreciated.
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613842
Ok - turns out custom properties might be a little tricky but it is viable.  The main trick is that you need to add the property to the document.  You can not add the same property twice... useful to know if you need to update the property.  But you can not update the property if you have not added it... lost yet?

Here is the how to from MS MVP web: http://word.mvps.org/faqs/macrosvba/MixedDocProps.htm
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40613846
The array side of things is a bit easier... you can store all the items in a single string and then convert a string to an array and then assign to control...
(I'm sure there are other ways to do this.. but...)

say your string is like
myString = "item1|Item2|item3"

Note that the delimiter character is the pipe char... Shift + \... so
then myAarray = split(mystring, "|")

Item1 = myArray(0)
Item2 = myArray(1).... and so on...

Hope this helps
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40615046
If you have a list of BuildingBlock entry names in a specific order, you can use it to insert or delete building blocks in the right place. You would mark each inserted block with a name that is derived from the building block entry name.
Here is some sample code. It might fit your exact situation, but it illustrates the general idea:
Sub BB(bBlockInsert As Boolean, iBlockNumber As Integer, Tem As Template)
    Dim strBlockNames() As String
    
    strBlockNames = Split("Apple,Red,Walrus,Delhi,Water", ",")
    bBlockPresent = ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(i - 1))
    'bBlockInsert is True to insert, False to remove
    If bBlockInsert <> bBlockPresent Then
        If bBlockInsert Then '(but not already installed)
            strPrevBookmark = ""
            For j = 1 To iBlockNumber - 1
                If ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(j - 1)) Then
                    strPrevBookmark = "bmk" & strBlockNames(j - 1)
                End If
            Next j
            If strPrevBookmark = "" Then 'no previous bookmark found, so put block at end of document
                Set rng = ActiveDocument.Bookmarks("\EndOfDoc").Range
            Else
                Set rng = ActiveDocument.Bookmarks(strPrevBookmark).Range
                rng.Collapse wdCollapseEnd
            End If
            rng.InsertBreak wdPageBreak
            rng.Collapse wdCollapseEnd
            rng.Text = "dummy"
            Tem.BuildingBlockEntries(strBlockNames(iBlockNumber - 1)).Insert rng, True
            rng.MoveStart wdCharacter, -2 'include page break in bookmark
            ActiveDocument.Bookmarks.Add "bmk" & strBlockNames(iBlockNumber - 1), rng
        Else
            'Already there, but not wanted
            ActiveDocument.Bookmarks("bmk" & strBlockNames(iBlockNumber - 1)).Range.Delete
        End If
    End If
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Hakum
ID: 40619041
Hi Graham, Thank you for your insight as always!

But as you know i'm quite not so awesome with vba yet, trying to work my way around tho.

I'm not sure how to use it, would it be possible for you to give me an example,? here is what i tried with:

I copied the code into a module, and created a call to the sub.
I created 3  bulding blocks and saved them into template itself.

Sadly i cannot attach the file since its not allowing me.
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40619817
Change the file extension to doc then attach. Just provide details of the original extension dotm or docm
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40620240
I have put the code into a template, but we can't attach templates.
The code is in three parts:
In the This Document module:
Option Explicit

Private Sub Document_New()
    UserForm1.Show
End Sub

Open in new window

In a UserForm:
Option Explicit
Dim templ As Template

Private Sub cboNames_Click()
    cmdAdd.Enabled = (cboNames.ListIndex > -1)
    cmdRemove.Enabled = (cboNames.ListIndex > -1)
End Sub

Private Sub cmdAdd_Click()
    BB True, cboNames.ListIndex + 1, templ
End Sub

Private Sub cmdRemove_Click()
    BB False, cboNames.ListIndex + 1, templ
End Sub


Private Sub UserForm_Initialize()
    Dim i As Integer
    strBlockNames = Split("Apple,Red,Walrus,Delhi,Water", ",")
    For i = 0 To UBound(strBlockNames)
        cboNames.AddItem strBlockNames(i)
    Next i
    Set templ = ActiveDocument.AttachedTemplate
End Sub

Open in new window

In a code module:
Option Explicit
Public strBlockNames() As String
        

Sub BB(bBlockInsert As Boolean, iBlockNumber As Integer, Tem As Template)
'Dim i As Integer
Dim j As Integer
Dim bBlockPresent As Boolean
Dim strPrevBookmark As String
Dim rng As Range
    bBlockPresent = ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(iBlockNumber - 1))
    'bBlockInsert is True to insert, False to remove
    If bBlockInsert <> bBlockPresent Then
        If bBlockInsert Then '(but not already installed)
            strPrevBookmark = ""
            For j = 1 To iBlockNumber - 1
                If ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(j - 1)) Then
                    strPrevBookmark = "bmk" & strBlockNames(j - 1)
                End If
            Next j
            If strPrevBookmark = "" Then 'no previous bookmark found, so put block at end of document
                Set rng = ActiveDocument.Bookmarks("\EndOfDoc").Range
            Else
                Set rng = ActiveDocument.Bookmarks(strPrevBookmark).Range
                rng.Collapse wdCollapseEnd
            End If
            rng.Text = "dummy"
            Tem.BuildingBlockEntries(strBlockNames(iBlockNumber - 1)).Insert rng, True
            rng.MoveStart wdCharacter, -1
            ActiveDocument.Bookmarks.Add "bmk" & strBlockNames(iBlockNumber - 1), rng
        Else
            'Already there, but not wanted
            ActiveDocument.Bookmarks("bmk" & strBlockNames(iBlockNumber - 1)).Range.Delete
        End If
    End If
End Sub

Open in new window


I have uploaded the template to Google Drive, and shared it with Steve (Dr Tribos), whose email address I know. If you tell me your email address via the EE messaging system, I should be able to share it with you as well.
0
 
LVL 1

Author Comment

by:Hakum
ID: 40620640
I have dropped you a mail Graham,

and sorry i didn't think of that earlier with changing the extention :)

here is the file:
Doc1.dot
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40620758
Harsh,
I have read your message, and have added your address to the shared list. The item name is Q28618302.dotm. I have sent you an EE message with my own email address.
0
 
LVL 1

Author Comment

by:Hakum
ID: 40620839
did you get my file and screenshot?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40620870
I am trying to keep most of the correspondence here, avoiding hidden stuff.

My demonstration template employs a userform with a combo to select the building blocks by name. The userform is opened via the Document_New.

The userform in your template has many checkboxes instead of the combo and is opened from an ActiveX command button on the document.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40621233
To expand on my last comment, for each of the checkboxes that is to control the insertion or deletion of a BuildingBlock, you would need some code like this.

Private Sub CheckBoxXX_Click()
    BB  CheckBox15.Value, YY, Activedocument.AttachedTemplate
End Sub
0
 
LVL 1

Author Comment

by:Hakum
ID: 40625197
You rock!!! Graham!!! i will check this during today! thank you so much for your time!!
0
 
LVL 1

Author Comment

by:Hakum
ID: 40625422
I'm trying but no luck :S i soo badly suck at VBA :(

I have tried the following:

Private Sub chktest1_Click()
    BB chktest1.Value, "Test1", templ
End Sub

Open in new window


Private Sub chktest1_Click()
    BB chktest1.Value, chktest1, templ
End Sub

Open in new window


i have added some new quickparts and it works with Graham's setup but if i want to do it with the checkboxes, i'm not sure what to write instead of YY,

please advise
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 40625964
In my code I create a string array (strBlockNames) of BuildingBlock names. The sequence in the array corresponds with the order that you want the blocks to appear in the document.  The YY is the index of the name in that array (iBlockNumber) in the BB procedure.  I use two command buttons to initiate the adding or removal of the block whose name is showing the the combo and derive the index from the ListIndex property.

With multiple checkboxes, you would need to put the appropriate index in the place of the YY in each of the click events.
0
 
LVL 1

Author Closing Comment

by:Hakum
ID: 40628251
Thanks Alot! I'm trying to work this out :)

I just posted another question Maybe any of you could help me out?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_28623294.html
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40630967
Here is the idea.
You need an array of BuildingBlock Names in the order that they are to appear in the document. The index into the array is the index to be used.

You are using checkboxes, so there isn't an automatic index like the one that comes with a combo. For this reason I suggested that you hard-code the index number for that block into the Click event of the matching checkbox.

The checkbox click event needs to look something like this:

Private Sub chktest1_Click()
    BB chktest1.Value, 13, templ
End Sub

Open in new window


However, you might find it easier to use the Caption Property, especially if they all correspond to the building block names.
You would need an extra function to get the index
Private Sub chktest1_Click()
    Dim strBuildingBlock = chktest1.Caption
    Dim ix as index

    ix =  GetIndexFromName(strBlockName)
    BB chktest1.Value, ix, templ
End Sub

Function GetIndexFromName(strBlockName As String) As Integer
    Dim i As Integer
    
    GetIndexFromName = -1
    For i = 0 To UBound(strBlockNames())
        If strBlockName = strBlockNames() Then
            GetIndexFromName = i
            Exit Function
        End If
    Next i
End Function

Open in new window


Here is the BB prcedure agin. This time with better comments.
Sub BB(bBlockInsert As Boolean, iBlockNumber As Integer, Tem As Template)
'bBlockInsert is True to insert, False to remove
'iBlockNumber is the position in the desired order of blocks
'Tem is the template with the building blocks

'Note that the array strBlockNames() has been set up earlier as Public, so can be accessed from here.
'It contains the names of all the blocks in the order that they are to appear



Dim j As Integer
Dim bBlockPresent As Boolean
Dim strPrevBookmark As String
Dim rng As Range

    'test to see if the bookmarked block is already in the document
    bBlockPresent = ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(iBlockNumber - 1))
    
    If bBlockInsert <> bBlockPresent Then 'some action is needed
        If bBlockInsert Then '(block is requested but not already installed)
        
            'find the bookmark of the block that this block is to follow
            strPrevBookmark = ""
            For j = 1 To iBlockNumber - 1
                If ActiveDocument.Bookmarks.Exists("bmk" & strBlockNames(j - 1)) Then
                    strPrevBookmark = "bmk" & strBlockNames(j - 1)
                End If
            Next j
            If strPrevBookmark = "" Then 'no previous bookmark found, so put block at end of document
                Set rng = ActiveDocument.Bookmarks("\EndOfDoc").Range
            Else
                Set rng = ActiveDocument.Bookmarks(strPrevBookmark).Range
                rng.Collapse wdCollapseEnd
            End If
            rng.Text = "dummy" 'pilot text. Overwritten immediately in the next line
            Tem.BuildingBlockEntries(strBlockNames(iBlockNumber - 1)).Insert rng, True
            rng.MoveStart wdCharacter, -1
            
            'enclose the new block in a bookmark with matching name
            ActiveDocument.Bookmarks.Add "bmk" & strBlockNames(iBlockNumber - 1), rng
        Else
            'Already there, but not wanted, so delete the bookmark and its contents
            ActiveDocument.Bookmarks("bmk" & strBlockNames(iBlockNumber - 1)).Range.Delete
        End If
    End If
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Hakum
ID: 40658099
Thank you so much Graham, I have been struggling with figuring out how to create an index, googled around for a few days and so on, would it be possible for you to point me in some kind of direction, that would put my boat in the ocean? I dont mind hardcoding the buldingblocks at all.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now