asked on

VBA - Word building up document with buildingblocks with userform

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.

DrTribos

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

Harsh Kumar

ASKER

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?

DrTribos

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..?

DrTribos

https://www.google.com.au/url?sa=t&source=web&rct=j&ei=gQzjVKGBA4OT8QWR44Fg&url=https://msdn.microsoft.com/en-us/library/office/ff862806.aspx&ved=0CBsQFjAA&usg=AFQjCNGBBSjCEDp_V6o8QWlYS9ONlunZ1Q

DrTribos

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 |

Harsh Kumar

ASKER

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.

DrTribos

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

DrTribos

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

GrahamSkan

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

Harsh Kumar

ASKER

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.

DrTribos

Change the file extension to doc then attach. Just provide details of the original extension dotm or docm

GrahamSkan

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.

Harsh Kumar

ASKER

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

GrahamSkan

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.

Harsh Kumar

ASKER

did you get my file and screenshot?

GrahamSkan

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.

GrahamSkan

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

Harsh Kumar

ASKER

You rock!!! Graham!!! i will check this during today! thank you so much for your time!!

Harsh Kumar

ASKER

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

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harsh Kumar

ASKER

Thanks Alot! I'm trying to work this out :)

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

https://www.experts-exchange.com/questions/28623294/VBA-Word-Clear-content-of-bookmark-but-not-the-bookmark.html

GrahamSkan

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

Harsh Kumar

ASKER

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.