Solved: Help with word macro design...

[srdennis]

Hello everyone,

I'm new to VBA, however I am really excited at the potentional of having a lot of cool things in the documents that I create. Right now I have two hurdles that I am trying to over come. The first is changing a birth date into an age that is representated in years and weeks. I use the following formula in excel, but would like a way to use this in a word document. The document will be printed out and become part of a patients medical record.

=ROUND(((TODAY()-A1)/365.25),0)&" years "& ROUND(((((TODAY()-A1)/365.25)-INT((TODAY()-A1)/365.25))*52),1)&" weeks "

This seems to work pretty good, but it would be cool if I could type in the birth date and have a macro covert it to an age. Any thoughts on how I can approach this from a VBA prospective?

Second, while the document has a lot of typing on it that standard, I would like to be jump between points on the document instead of moving the mouse and then clicking on where I need to add stuff. I tried creating a form which worked o.k., but the inflexibility of being able to edit stuff outside of the fields was unacceptable for a medical record. Again any thoughts on how VBA might be able to solve this would be apperciated.

Thanks

Steve Dennis

[fumei]

1. Look up Date functions in help.

2. Could you clarify / expand your requirements for navigation? You state "jump between points". OK, but that means nothing really. WHAT points? Thnik this through. If you want to jump between points, what is the first thing you need?

Answer? Points. Which means.....you need to define, or declare, a location that IS a point. Right? What makes, say the beginning of the fourth paragraph a "point" and the beginning of the tenth paragraph NOT a point?

Defined "points" ina document are similar to a bookmark in a book. You stick a piece of paper in a book to mark a location. You stick a bunch of them in a book to mark different locations. You then can "jump" between these points easily, becasue you have "bookmarked" them.

Word has bookmarks. They can mark a single point - or they can mark chunks. Say you hace a document with 10 chapters, you can bookmark an entire chapter. You can nest bookmarks within bookmarks. You could bookmark the entire document, if you want.

Please define your requirements and we can help. There are a number of possible solutions.

RE: formfields, and the problem of not being able to edit outside the fields. The answer to this si use Continuous Section breaks. Thatway, you can exolicitly define the areas that allow edits, and those that do not.

[MOS MASTER]

Hi and welcome to VBAX

For the first part you could use something like:[VBA]
Option Explicit

Sub GiveAge()
Dim vBirth As Variant
vBirth = InputBox("Give date of birth")

If IsDate(vBirth) Then
MsgBox Age(CDate(vBirth))
End If
End Sub

Private Function Age(dtBirth As Date) As Integer
Dim dtNow As Date

If IsNull(dtBirth) Then Age = 0: Exit Function

dtNow = DateDiff("yyyy", dtBirth, Now)

If Date < DateSerial(Year(Now), _
Month(dtBirth), _
Day(dtBirth)) Then
dtNow = dtNow + 1
End If

Age = CInt(dtNow)
End Function
[/VBA]

For the second part I go with Gerry not knowing what you want exactly...

[mdmackillop]

The following code attached to a form with a listbox will list all bookmarks and navigate to a selected item
[VBA] Option Explicit
Private Sub UserForm_Initialize()
Dim bmk As Bookmark
For Each bmk In ActiveDocument.Range.Bookmarks
ListBox1.AddItem bmk
Next bmk
End Sub
Private Sub ListBox1_Click()
Selection.GoTo What:=wdGoToBookmark, Name:=ListBox1
End Sub

[/VBA]

[srdennis]

Thank you so much for those that replyed so far. I really apperciate the help. If you need help with your pets I'd be happy to give my $0.02 (I'm a veterianrian).

The document that I have is a patient examination. It looks like the document below. I would like to jump from Signalment to Reason for visit to Subjective, then to T=, P=, R=, etc. This way I can enter data that I need or leave it if there is nothing to change. At one point I created a form with fields at each of these points so that I would just tab from one field to the next, however I couldn't use the spell check and it became difficult to edit the document if I needed to take out a section or add something it that was not in a field.

I'm sorry that I don't quite know how to think about these "points", but I hope this help you enough so you can help me understand what they are.

Steve Dennis

Date Monday, June 27, 2005

Signalment: ? ?



Reason for visit:



Subjective: No medications. No V/D/C/S/PU/PD/PP/Lameness.



Objective:

General: BAR, mucus membranes pink and moist, CRT < 2 seconds, well hydrated.
T= P= R= Weight = #

EENT:

Eyes: Clear OU.

Ears: NSF.

Nose: Moist.

Throat: Tonsils in crypts.

Integument: Full, shiny coat.

Musculoskeletal: Body condition score 5/9. Symmetric. No gait abnormalities.

Cardiovascular: No murmurs auscultated. Femoral pulses moderate and synchronous.

Respiratory: All lung fields auscult clear.

Gastrointestinal: Abdomen soft, non-painful, no masses palpated, bladder small.

Urogenital: Within normal limits.

Neurologic: Within normal limits.

Lymph Nodes: Within normal limits.



Problem List:

1. .

Diagnostic Test:

1. .

Assessment:

1. .

Plan:

1. .







Steven R. Dennis, DVM

[MOS MASTER]

Hi Steve,

What you need is a UserForm that will fill in your document at the specific locations.

Just go to the VBE (ALT+F11)

Choose Insert/UserForm and use the control panel to add labels and textboxes as you like.
Arange the controls as you like.
Put the headers in the document and say something like Textbox PetName belongs here for each header.

Post the document back and we"ll code it for you so it pop's up automatically when you open a new document and help you fill it it in.

We can work from there..

Later..

[fumei]

Well...there are a couple of solutions. i am attaching two files (in a ZIP of course).

Vet_test1.doc - uses bookmarks to point to locations. I added a macro fire button on the main toolbar. The title is "Get Bookmarks". If you click it, a userform displays with the list of all bookmarks.

What I did was put bookmarks at the end of each of your items. However, I did not put ALL of them in. This is for demonstartion purposes.

Selecting a bookmark from the dropdown list moves the insertion point to that bookmark. Now you can jump to whereever you want to, via bookmarks. This is no different from using Insert > Bookmarks and selecting the bookmark. It just makes it a little easier with a dropdown.

Vet_Test2.doc - this file uses ActiveX controls as textboxes. You can type info into the textboxes. As most security settings when they sense ActiveX open the document in Design mode. I added code to toggle it off.

An third alternative is to use formfields.

[MOS MASTER]

Ah..Ok..If it's only navigating your after you could add a simple sub to jump to the bookmarks. (You put arround the headers)

And create a shortcut key to fire it easily..

[fumei]

There are a number of solutions, or methods for navigating around a document. There are a number of solutions for gathering information. You could totally use a UserForm and then fill in the data.

A lot depends on the choices required for input. For example:

Lymph Nodes: Within normal limits.

Now, do you ever need to type text here? Could you have a set list of phrases, however many? If so, then a dropdown seems appropriate. You could use an ActiveX control, or a formfield. Selective use of Continuous Section breaks, would still allow text edit/input in determined areas of the document., For example:

******End of Protected Section ******
Gastrointestinal: Abdomen soft, non-painful, no masses palpated, bladder small.

********* Start of Protected Section *******
Urogenital: Within normal limits. ' this is a formfield
*********End of Protected Section**********

using Continuous Sections you can slice up your document into protected and unprotected sections.

[MOS MASTER]

Yes of course Gerry..but I'm still trying to figure out what our Vet trully wants...

BTW it was mentioned you could not use the spell checker within a protected document with formfields. Just for the record it is possible to do this with VBA.

[srdennis]

Thanks everyone for all of the help. I had no idea that I was get so many options or that you all will pitch in to help me. Since there are a couple of different ideas on how to approach this and I still need to become a little more fimillar vit VBA, I'm going to take a little time to digest all of this. Should I mark this solved?

Steve Dennis

[MOS MASTER]

Hi Dennis,

You're most welcome and do come back for aditional questions!
Yes mark the thread solved you could always add to this question or start a new one.

Later..

[srdennis]

Well I marked this solved, but the question that I have keep coming. I have played with the macro recorder and learned a little from the code it writes. Here is one thing that I wrote for when I spay a dog.

Sub OVHdog()
'
' OVHdog Macro
' Macro recorded 6/29/2005 by Tsunami Steve
'
Selection.Font.Name = "Arial"
Selection.Font.Size = 10
Selection.TypeText Text:="Ovariohysterectomy"
Selection.TypeParagraph

Selection.TypeText Text:="Patient was placed in anesthetized and maintained on isoflurane. Patient was placed in dorsal recumbence. The abdomen was clipped, scrubbed, and prepped for abdominal exploration. An incision was made to the linea with a #10 scaple blade. "
Selection.TypeText Text:="The subcuticular layer was released from the linea alba with metzenbaums. The linea was picked up with brown-adson tissue forceps and incised with the #10 blade. The uterus was located and followed to the left ovary. "
Selection.TypeText Text:="The proper ovarian ligament was secured with a rochester-carmalt forcep and the suspensory ovarian ligament was manually broken down. The ovarian artery and vein were isolated clamped and double ligated with _________suture. The right ovary was tied off in the same manner. "
Selection.TypeText Text:="The ovarian body was double clamped and double ligated with _______suture and removed from the abdomen. No signs of hemorrhage were noted from the ovarian or uterine pedicles. The linea alba was closed in a simple continuous pattern with __________suture."
Selection.TypeText Text:="sub-cuticular was closed in a simple continuous pattern tacking to the linea alba every third pass with__________suture. "
Selection.TypeText Text:="The skin was closed with an inter-dermal simple continuous pattern with ____________suture. The anesthetic recovery was uneventful."

'Selection.TypeText Text:="Patient was placed in anesthetized and maintained on isoflurane. Patient was placed in dorsal recumbence. The abdomen was clipped, scrubbed, and prepped for abdominal exploration. An incision was made to the linea with a #10 scaple blade. The subcuticular layer was released from the linea alba with metzenbaums. The linea was picked up with brown-adson tissue forceps and incised with the #10 blade. The uterus was located and followed to the left ovary. The proper ovarian ligament was secured with a rochester-carmalt forcep and the suspensory ovarian ligament was manually broken down. The ovarian artery and vein were isolated clamped and double ligated with _________suture. The right ovary was tied off in the same manner. The ovarian body was double clamped and double ligated with _______suture and removed from the abdomen. No signs of hemorrhage was seen from the ovarian or uterine pedicles. The linea alba was closed in a simple continuous pattern with __________suture."
'Selection.TypeText Text:="sub-cuticular was closed in a simple continuous pattern tacking to the linea alba every third pass with__________suture. The skin was closed with an inter-dermal simple continuous pattern with ____________suture. The anesthetic recovery was uneventful."

End Sub


As you can see, I have a lot of text that I want placed into the document when I run the macro. Is breaking it up into several Selection.TypeText Text:= better than a couple of really long lines? Is there a better way of doing this?

I also tried running the Sub Age() macro that Mr. Verdaasdonk wrote for me (thanks), but when I run it it from within word It says compile error. Ambigious Name detected: Age. I tried the online help, but there is a little too much specialized language at this time for me to understand it. I might be wrong, but do I need a special object library for Age? (I know that I sound like an idiot, but the terms will come with time).

Thanks again.

Steve

[MOS MASTER]

Hi Steve,
Just call me Joost.

Little time now.

Ambigious name means there is another sub somewhere in your project that's also named Age!

Normaly the VBE (Editor) will select the Ambigious name when you acknowledge the msgbox.

You can also run The menu Error (or something like that) | Compile project.
The same message will apear and the double name will be selected..

Later.

[mdmackillop]

Here's a possible route using autotext entries. These save a lot of typing in the code window, and are very flexible.

[srdennis]

Hello again,
Thanks msmackillop. I like form you created. Very quick and easy to fill in. The thing that I would like to know how you did is how do you get the drop down fields into an autotext entry? Is there a limit to the number of characters in an autotext entry (255?)
I understand the first part of this I think. Isn't it just telling word to make an array of the autotext entries OV00-OV06?

Sub FillReport()
Dim MyText, MT
MyText = Array("OV00", "OV01", "OV02", "OV03", "OV04", "OV05", "OV06")

This is where I get lost. Could you tell me a little more about how this code produces the dropdown menus with in the array that allow you to pick the type of suture to use? When I look at the text in OV00-OV06 in the autotext section, I can find the where the dropdown menus will be but am not sure how to get them there.
For Each MT In MyText
ActiveDocument.AttachedTemplate.AutoTextEntries(MT).Insert_
Where:=Selection.Range, RichText:=True
Next

I think this last bit protects the document so that only the fields can be changed. Is this right

ActiveDocument.Protect Type:=wdAllowOnlyFormFields

End Sub

[mdmackillop]

I added drop downs into your text from the Forms toolbar and inserted the optional words in its properties. I then selected the whole sentence and made it an autotext item (Insert/Autotext/New/Abbreviation), which includes the formfield.
As to size, I've just made one of 1500 words. There may be a limit, but I don't know what it is.
You're right as to the last bit. If you need to edit the main text, this would be better done before protecting the document to fill in the fields. The reason for this is that unprotecting a "completed" document can lose the field data when it is unprotected.
I did make up a utility a while ago, which stores text in a spreadsheet, that can be selected from a userform list for insertion into a document, which might be useful in conjunction with this. I'll have a look for it.

[mdmackillop]

Here's a couple of the Excel option. Save the Excel file into C:\AAA\ or rename the path in the Userform code module (any questions, just ask!) Clicking an item on the userform will insert the text at the insertion point.

[srdennis]

Hello everyone again. I am getting really excited about what I have created. I know that you are going to think that it is pretty basic, but the fact that I can write this and it does some of the things I need it to do is really cool. Not to mention that I am starting to understand a little VBA stuff.

Here is where I am stuck. I have writen a simple macro to enter text and format it the way I like. I have droped in 2 bookmarks. One called DOB and the other called Temperature. I have created a user form to collect several pieces of data (DOB, Temp, Pulse, Resp, Weight). I now would like to have the data go from being entered into the user form to where the book marks are within the document. If I can see how this is done I will be albe to do this for a lot of other things. I have attached my document with the macros and userform. If one of you could just take a peek at it and let me know what you think that would be great.

Steve Dennis

P.S. Thanks for the excel save option. I have not looked at this yet, but I think that I can use it to store client contact information or drop in stuff to my document that are typical treatments I do all the time.

P.S.S. I just read the last post. Maybe I can figure it out my problem from the file you created to move text from excel into word with a user form. Sorry for not reading before posting.

[mdmackillop]

Hi Steve,
Your macros have not come with the document. They may have been saved in your Normal.dot. If you look in the VB Editor with this document open, you can drag the code modules and userform from Normal to this project (this creates a copy of them, not move them)