Solved

create and insert bookmarks into word document using VBA

Posted on 2008-10-31
10
3,629 Views
Last Modified: 2012-06-27
Hi all,

I have a template for creating invoices. However i want to be able to add a bill of materials to it. at the moment i prompt for all the needed information for labour and transport in the following format;

LABOUR <labourcost>      <total1>
TRANSPORT <transcost>  <total2>
                                           <inv total>

this is in a simple table of 6 cells (mainly to align the totals on the right)

i want to be invoice to look (eventually) like the following;

LABOUR <labourcost>      <total1>
TRANSPORT <transcost>  <total2>
MATERIALS <material1>    <mattotal1>
...                                       ...
MATERIALS <materialN>  <mattotalN>
                                           <inv total>

now i have added the folowing code;

    mCount = 1
    vAnswer = MsgBox(Prompt:="Are there any Materials to enter?", Buttons:=vbYesNo)
     
    While vAnswer = 6
        AddMaterials (mCount)
        mCount = mCount + 1
        vAnswer = MsgBox(Prompt:="Are there any further Materials to enter?", Buttons:=vbYesNo)
    Wend

and the AddMaterials method

Sub AddMaterials(ByVal count As Integer)
        vVar = InputBox(Prompt:="Please enter description of the materials and click ok.")
       
        'build bookmark name
        Dim Name As String
        Name = "MATERIAL" + CStr(count)
        ActiveDocument.Bookmarks.Add Name, Selection.Range
        ActiveDocument.FormFields(Name).Result = vVar
       
        vVar = InputBox(Prompt:="Please enter cost of the materials and click ok.")
       
        Name = "MATERIALCOST" + CStr(count)
        ActiveDocument.Bookmarks.Add Name
        ActiveDocument.FormFields(Name).Result = vVar
End Sub

now i can't see how to ;

a) create a new row in the table (i can remove this table if needed) in VBA
b) once the new row has been created add the detail and the cost.

Any ideas?

Many thanks in advance,

Matt.
0
Comment
Question by:flynny
  • 6
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22848863
Your strategy is a bit confusing to me - a mixture of FormFields, Bookmarks and Tables.

To use FormFields, you need the document to be protected for forms. Their purpose is to allow the user to enter data only in particular places, but to add rows and change data elsewhere, including at non-formfield bookmarks, the document must be unprotected.

Also, If you are using tables, you can address the rows and cells directly, so you probably don't need bookmarks.

This demonstrates how to add a row to the end of a table.

Dim tbl As Table

Dim rw As Row

Set tbl = ActiveDocument.Tables

Set rw = tbl.Rows.Add

rw.Cells(1).Range.Text = "Some Text"

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22848874
Sorry. Line 3 should read
Set tbl = ActiveDocument.Tables(1) ' for the first table in the document.
0
 

Author Comment

by:flynny
ID: 22848998
hi thanks for the post. Sorry about the confusion. yes it is formfield i want to use.

ok, i've added the following code to add the additional line with the detail and the cost

        vText = InputBox(Prompt:="Please enter description of the materials and click ok.")
         
        Dim tbl As Table
        Dim rw As Row
        Set tbl = ActiveDocument.Tables(1)
        Set rw = tbl.Rows.Add
        rw.Cells(1).Range.Text = vText
       
        vText = InputBox(Prompt:="Please enter cost of the materials and click ok.")
     
        'now access cell
        Set oRange = rw.Cells(2).Range
        Set oFormField = ActiveDocument.FormFields.Add(Range:=oRange, Type:=wdFieldFormTextInput)
       
        'build the fields name
        Dim fieldName As String
        fieldName = "MATERIAL" + CStr(count)
        oFormField.Name = fieldName
        oFormField.Result = vVar

however i get a run time error '91'

object variable or with block variable not set.

any ideas what this means?

0
 

Author Comment

by:flynny
ID: 22849004
sorry, the error occurs at this line

oFormField.Name = fieldName
0
 

Author Comment

by:flynny
ID: 22849112
also just to add the program does create the formfield in the relevant cell.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22849680
Sorry for the delay. She decided we had to go to Tesco's.

The problem is that the form field addition failed without warning you.

The reason that it fails is that the cell range include the cell formatting, and this would be overwritten. Adding text seems to realise this and work properly but the form field procedure doesn't, and you have to pull the range back buy one character.
    Dim vText As String

    Dim oRange As Range

    Dim oFormField As FormField

    Dim Count As Integer

    Dim vVar As Variant

    vText = InputBox(Prompt:="Please enter description of the materials and click ok.")

     

    Dim tbl As Table

    Dim rw As Row

    Set tbl = ActiveDocument.Tables(1)

    Set rw = tbl.Rows.Add

    rw.Cells(1).Range.Text = vText

    

    vText = InputBox(Prompt:="Please enter cost of the materials and click ok.")

    

    'now access cell

    Set oRange = rw.Cells(2).Range

    oRange.MoveEnd wdCharacter, -1 '<----------------------

    Set oFormField = ActiveDocument.FormFields.Add(Range:=oRange, Type:=wdFieldFormTextInput)

    

    'build the field's name

    Dim fieldName As String

    fieldName = "MATERIAL" + CStr(Count)

    oFormField.Name = fieldName

    oFormField.Result = vVar

Open in new window

0
 

Author Comment

by:flynny
ID: 22851356
great that works a treat. one last thing do you know how i can set the number format of the text form field please? i've been playing around for ages with it with no luck whatsoever.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 22851765
Use FormField.TextInput.EditType

Here is an example
    Dim ffld As FormField

    Dim rng As Range

    

    ActiveDocument.Unprotect 'Password

    Set rng = ActiveDocument.Bookmarks("\EndOfDoc").Range

    Set ffld = ActiveDocument.FormFields.Add(rng, wdFieldFormTextInput)

    ffld.TextInput.EditType wdNumberText, "#,##0.00", "0.00", True

    ActiveDocument.Protect wdAllowOnlyFormFields, True ',password

Open in new window

0
 

Author Comment

by:flynny
ID: 22865362
brilliant thanks so much for your help on this its been very much appreciated. sorry fo the late reply, but for some reason ee kept crashing when i tried to post a reply. proberbly due o my poor wireless connection.

thanks again!

Matt.
0
 

Author Closing Comment

by:flynny
ID: 31511982
superb quick repsonse and direct to the solution.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

707 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

17 Experts available now in Live!

Get 1:1 Help Now