Ridding Ref field of unwanted space

[Greg]

I have just created a user form in Word utilising Bookmarks and Ref fields to populate a document with chosen text. The Bookmarks are created by making a single space in the document, selecting it and inserting a Bookmark. The result is a Bookmark that looks like a pair of bookends pressed up against each other. The Ref fields then pick up any text entered into the Bookmarks via the user form. The problem is that the Ref fields (or the Bookmarks) create an unwanted space after each entry. This doesn't matter in every case but it looks odd if the Ref Field is followed by a comma. Is there a way to solve this problem? Any help will be appreciated. Greg.

[fumei]

And why are you making the bookmarks using that space?

[Greg]

Hi Gerry,

I was simply following a procedure I read elsewhere. I think the intent was to leave the Bookmarks in situ for future use. Is there a better way to do it?

Greg.

[fumei]

But you do not NEED to use a selected space to insert a bookmark. You can just put in a bookmark. I am trying to understand why you select a space and make a bookmark. Just...make a bookmark. You can still insert your text into it.

If you use a space then the space is in the bookmark range. If you do not use a space, it is not. It may help if you describe things more.
This doesn't matter in every case but it looks odd if the Ref Field is followed by a comma. Why would there be a comma?

How are you putting text into the bookmark?

[Greg]

Gerry, some of the text entered into the bookmark is reproduced in a Ref fields in the document. In one of those Ref fields the text is folowed by a comma.

As mentioned earlier, I had thought that a space was necessary to preserve the bookmark after the macro had run so that further changes to the document could still be made by changing the text in the bookmark and updating the Ref fields accordingly.

Greg.

[fumei]

some of the text entered into the bookmark is reproduced in a Ref fields in the document. In one of those Ref fields the text is folowed by a comma.Some???? The Ref field should Ref all of the text. Are you doing some editing of it?

If there is a comma, and it was not there before, and the ref field is updated and now it IS there - then it came from the bookmark. It was put in the bookmark.

Since you are not posting any actual code, I am not sure i can suggest anything.

You do NOT need a space to preserve the bookmark.

[Greg]

Hi Gerry,

Here's the code.

Private Sub CommandButton1_Click()
With ActiveDocument
.Bookmarks("CaseNumber").Range _
.InsertBefore TextBox1
.Bookmarks("Deponent").Range _
.InsertBefore TextBox2
.Bookmarks("Claimant").Range _
.InsertBefore TextBox3
.Bookmarks("Defendant").Range _
.InsertBefore TextBox4
.Bookmarks("Reference").Range _
.InsertBefore TextBox5
End With
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub

Please note that I put the comma in the document just behind the field hoping that any text updated in the filed via the bookmark would sit immediately in front of the comma. That didn't happen and I ended up with an unwanted space in front of the comma.

Your comments regarding the above code would be appreciated.

Greg.
Edited 9-Jun-06 by geekgirlau. Reason: insert vba tags

[fumei]

InsertBefore, InsertAfter are sloppy VBA. Not a comment on you...this is a comment on VBA. I am attaching a demo file that works much better. But first, some comments.

The demo userform and bookmarks are carefully named. It is a VERY good practice to explicitly name everything, and I mean everything.

So no Textbox1, Textbox2 etc etc . No Userform1 etc. Rename everything. It really helps. Use the standard nomenclature for controls. "txtxxxxxx" for textboxes, etc.

Next, filling bookmarks. Bookmark.Range.Text can delete the bookmark. It puts the text there, true, but deletes the bookmark. InsertBefore, or InsertAfter can also place text OK, but they also mess with the bookmark range. These methods are not truly reuseable text placers. The following code is.Sub FillABookmark(strBM_Name As String, strBM_Text As String)
Dim ThisDoc As Word.Document
Set ThisDoc = ActiveDocument
' following is for formfields, if no formfields can remove
If ThisDoc.ProtectionType = wdAllowOnlyFormFields Then
ThisDoc.Unprotect
End If
On Error Resume Next
With Selection
.GoTo what:=wdGoToBookmark, Name:=strBM_Name
.Collapse Direction:=wdCollapseEnd
ThisDoc.Bookmarks(strBM_Name).Range.Text = strBM_Text
.MoveEnd unit:=wdCharacter, Count:=Len(strBM_Text)
ThisDoc.Bookmarks.Add Name:=strBM_Name, Range:=Selection.Range
.Collapse Direction:=wdCollapseEnd
End With
' to reset formfields
If ThisDoc.FormFields.Count > 0 Then
ThisDoc.Protect wdAllowOnlyFormFields, Password:=""
End If
Set ThisDoc = Nothing
End SubEssentially the code recreates the bookamrk after text has been placed. The size (length) of the bookmarks is ALWAYS reconfigured for precisely the text placed in it. Therefore you do NOT need to have any extraneous spaces, or commas, or anything. Use the bookmark exactly like a word (or phrase). If you want a space before it, put one. If you want a comma after, put one. if you don't...then don't.

This code is in a standard module. There is also a procedure that calls the userform.Sub DemoForm()
frmDataInput.Show
End SubThe userform is named....

The Userform has five textboxes - matching your CaseNumber, Deponent, Claimant, Defendent and Reference bookmarks. I duplicated them in the document. NOTE! the textboxes on the useform are explicitly named: txtCaseNumber, txtDeponent, txtClaimant etc.

The commandbutton on the userform runs:Sub CommandButton1_Click()
Dim oControl As Control
Dim strBM_Name As String
Dim strBM_Text As String

For Each oControl In frmDataInput.Controls
If Left(oControl.Name, 3) = "txt" Then
strBM_Name = Right(oControl.Name, Len(oControl.Name) - 3)
strBM_Text = frmDataInput.Controls(oControl.Name).Text
Call FillABookmark(strBM_Name, strBM_Text)
End If
Next
Unload Me
End SubHopefully you can following. Technically you can trim the code even further. Strictly speaking you do not need to use variables for the bookmark name, and text content.

Anyway, the code runs through the controls on the userform. I don't have any idea what other controls you have, but it doesn't matter. It checks for "txt" textboxes, finds one, dumps the remaining part of the name (eg. "txtCaseNumber" passes "CaseNumber") into a variable for the bookmark name; dumps the textbox text into a variable for bookmark text; and passes them on to the procedure FillABookmark.

Voila. The bookmarks match the textboxes, and the text is put into the bookmarks. The bookmarks are dynamically resized to precisely the text from the textboxes. You can run this repeatedly and the bookmarks will match perfectly.

I put the userform show procedure as a button on the top toolbar. You can run it from there - no need to go into the VBE.

[Greg]

Thanks Gerry, I can see that you have put in a lot of effort on this. After running your DemoForm I thought I could save the demo, drop my document into it, move the Bookmarks into position, add some ref fields, save it as a Document Template and achieve the result I previously had (albeit without the unwanted spaces). Unfortunately, I have not been succesful but I'll keep trying. If you think I am wasting my time to do it this way please let me know. Many thanks, Greg.

[fumei]

Hmmmm. Seems a bit twisted.

Copy the Sub FillABookmark into your document - or whatever you are using to hold code modules. Then use it it. Remember the code is dumping textbox names into the Sub with the assumption things are named correctly. The textboxes are named txtblahblah, and the appropriate bookmark is named blahblah. They must match!

As for the REF fields, simply go to where you want the REF and do a Ctrl-F9 and type in REF and the name of the bookmark. Eg. REF Reference. You can add updating instructions into your Sub, as in:Sub CommandButton1_Click()
Dim oControl As Control
Dim strBM_Name As String
Dim strBM_Text As String
Dim aField As Word.Field

For Each oControl In frmDataInput.Controls
If Left(oControl.Name, 3) = "txt" Then
strBM_Name = Right(oControl.Name, Len(oControl.Name) - 3)
strBM_Text = frmDataInput.Controls(oControl.Name).Text
Call FillABookmark(strBM_Name, strBM_Text)
End If
Next
Unload Me
For Each aField In ActiveDocument.Fields
aField.Update
Next
End Sub

[Greg]

Hi again Gerry,

I think I have overcome my problem. I had unwittingly changed the name of your CommandButton1 to "Click to Finish" without changing a line of code. I'll leave well alone next time.

My next question is whether I can get the same form to perform date calculations. In applying to the Court for Default Judgment, I have to calculate the number of days between certain dates, work out the daily interest rate and add it to a total in a form. I do this by bookmarking the 2 date fields in a table and subtracting one from the other. However, I have always had to make sure that I carefully typed inside the bookmarks to preserve the dates (and the bookmarks). Is this something that can be overcome using good code and a re-usable Userform similar to the one you have just created?

Once again, many thanks for your help to date.

Greg.

[fumei]

Aaaaaahhhhhhhhh! Someone who gets it.
Is this something that can be overcome using good code and a re-usable UserformYes indeedy Virginia, there IS a Santa Claus.

Good code.....what a concept.

As for "reuseable" - well, sure, if you can. But I have doubts about how far that can be stretched. However, the concepts used in a good UserForm, absolutely reuseable.

Hard to say considering the amount of details, but if the userforms is taking input, and you need to do calculations, you may as well do the calculations with the userform. Hard to say. Depends on what is actually happening.

SPEC IT OUT. Write down (and I mean it...write it down!) every single step you want (or need...start with need.) You would be surprised at how many real steps there are. Or at least you will if you truly break it down into EACH step.

Chunk it out as well. Do not try and put everything into one gigantic Sub. It takes little resources to make multiple calls to smaller Subs. And it makes BIG differences when you try to follow things, debug things, or pass along code to someone else to read.

[Greg]

Hi Gerry,

By now you may be getting tired of this. If so, please let me know and I'll give it a rest. If not, perhaps you can help me to automate (with a User Form) the following interest calculation in a court form. There are many others but this one is fairly simple.

Interest on $15,045.25 from 27/01/2006 to 01/03/2006 at 6% p.a. being 33 days at $2.47 per day.


In this example the two main fields are the StartDate (27 January 2006 and the EndDate (1 March 2006). Both have been bookmarked. By inserting a formula in the table that subtracts one bookmark from the other I get the number of days (33) which can then be multiplied by the daily rate (also by a simple formula).

As you probably realise, pressing F9 in either of the above date fields removes the entry completely. Therefore, if my code includes the line "ActiveDocument.Fields.Update", I risk getting no result from typing in the dates unless the code can be made to ignore the StartDate and EndDate fields. My present skills are insufficient to deal with this problem and I hope you can help.

Your help so far is greatly appreciated.

Regards,

Greg.

[fumei]

As stated, please spec out the precise steps. HOW are the bookmarks being filled? From WHERE? WHEN is this calculation to occur?

Can you post a file?

Also, remember that Word is a word processor. IMHO it should not be treated as if it is a spreadsheet, or database. Is it possible that this would be off in Excel? If it is smallish, then sure you can use Word.

What is on your userform? Can this calculation be done while the userform is active, or is this an execution only applicable AFTER the userform is closed and the bookmarks filled. What is happening here?

[fumei]

How are the date fields being populated?

[Greg]

At present I type the StartDate and EndDate in into the bookmark brackets. Then by clicking on the field that shows the number of days and pressing F9 (to update the field) the number of days between the two is given. My intention was to type the start and end dates (and the principal amount and interest rate) into a User Form whereafter the operator would click the Command button and all subsequent calculations would be performed. Apart from the calculations referred to, the dates do not appear anywhere else in the document.

Greg.

[Greg]

Hi all,

I am trying to construct a Userform in which a StartDate and EndDate are entered to perform some financial calculations. Basically, I need to calculate the number of days between the two dates, work out a daily interest amount and add it to a given debt.

Does anyone know how this can be done?

In Word, I type the dates into the fields (I type 08/06/2006 for 8 June 2006) and then place bookmarks around the fields and deduct one from the other to get the result.

However, I have to be very careful to make sure that the field is not spoiled in the process and I can't apply F9 to those fields because it eliminates them completely..

The field codes, for the StartDate and EndDate, when toggled are as follows:

=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT

Although this would be easy in Excel I need to do it in Word and hope someone can help.

Greg.

[Greg]

Sorry, the full description of the field is as follows:

=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045

[fumei]

Hi could you explain your a, b, c, d? I have a demo document going but but am not sure on HOW to do the calculation. By that I mean the actual calculation...I know how to do formula.

[fumei]

=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045 seems odd for a DATE.

[Greg]

Hi Gerry,

Is there some way I can email the document to you?

[Greg]

Hi again Gerry,

I now realise that I had completely forgotten to tell you I was using a DateDiff function. I forgot to tell you because I forgot it myself.

Anyway, after a bit of browsing I came across the following information on the Word Tips web-site.

When you are programming macros using VBA, you should know that dates are stored internally, within variables, as serial numbers. The serial number represents the number of days elapsed since a starting "base date," specifically since 1 January 100. This means that you can perform math with the serial numbers, if desired. You can, for instance, find the number of days between two dates by simply subtracting the dates from each other.

If you want to get fancier in your date calculations, you can use the DateDiff function. This function allows you, for instance, to determine the number of weeks or months between two dates. In order to use the function to find this type of information, you would do as follows:
iNumWeeks = DateDiff("ww", dFirstDate, dSecondDate)iNumMonths = DateDiff("m", dFirstDate, dSecondDate)
The first line determines the number of weeks between the two dates, and the second determines the number of months between them.

Gerry, all I need to know now is how best to use the above information to calculate the difference between dates in days (not weeks or months) and how to make it work in a Userform. That is, how can I type the dates into a Userform and have the macro do the maths for me? Or more specifically, how can I do this in the context of the code that you have already provided?

I trust that this will make things clearer and I look forward to your response.

Greg.

[TonyJollans]

Hi Greg,

I've tried to follow this thread but am having some difficulty. I'm really not sure what you're working with. Do you have your dates in fields in your document or in a (VBA) UserForm? Date calculations in Fields are immensely complex (see below); date calculations in VBA are relatively simple.

Although not hugely significant the information you have picked up from the Word Tips site is incorrect - VBA dates are nominally based on 31-Dec-1899.

Gerry (and Greg),

=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045 looks like part of a conversion of a date to a Julian day number. There are a few similar algorithms and the prevalence of this in Word fields is, I suspect, due to macropod (a poster at a few sites and now a Word MVP) who has written a document or two about calculations in Word fields which use a similar calculation. Personally I think it's rather obscure but it doesn't really matter what base date or conversion algorithm one uses so long as one is consistent.

[fumei]

The date in Document VS UserForm is exactly what I am trying to figure out.

The essential point is that - as Tony pointed out - you can do your date stuff (and calculations) using VBA, in the form. Then put the results into the document.

[Greg]

Hi Tony,

My dates are in fields but I now accept that VBA may be preferred.

Gerry kindly provided the basis for a Userform which I have adapted to my needs. His attachment can be found somewhere above. I now wish to expand the functionality of that Userform so that it calculates the difference (in days) between two (or more) dates. Once the number of days between the dates has been determined, I will calculate the interest applicable to a given amount, at a given interest rate (both entered via the Userform), for the number of days in question. Is this something you can help me with? My own VBA skills are still very limited.

Many thanks,

Greg.

[fumei]

Sure. Here is a starting point. I don't know how to do the rate per day calculation. It is left blank. The userform starts up with Document_Open.