If then vba code

[miketallica]

i have a word doc that when you open a form pops up and you fill it out and it populates the entire document. the problem that i am having is at one point in the document it says " on the (day)th. the problem is that when the date populates i need the "th" to change to what ever is proper ending is whether it's "1st" "2nd" "4th". get it?

http://i588.photobucket.com/albums/ss321/roaddog83/Form.jpg

[fumei]

Yes, I get it. I do not get the problem though. Simply make the trailing text ("st", "nd" etc.) appropriate to the value of the number. One choice would be to use Select Case.

You do not state what version you are using. This is often helpful.

You do not state where the value - 1, 2, 4 - comes from, nor whether this is a string "1", "2", or a number 1, 2.

"the date populates " What does that mean? Dates do not populate.

Is this a DATE variable? A string? What is "populating"? A formfield? A bookmark? Just text put into the document?

How far up do you need to go? Do you need 30th, 31st? 160th?

I would do it as a Function, not a Sub, using Select Case. Have your starting value as an input parameter. Use the Right function within it to get just the last character. That way it does not matter if it is 1 or 23,981.

1 would come out 1st
23,981 would come out 23,981st

[macropod]

Hmm, here's a funtion:
Function Ordinal(IntNum As Integer) As String
Select Case IntNum Mod 100
Case 1, 21, 31, 41, 51, 61, 71, 81, 91: Ordinal = IntNum & "st"
Case 2, 22, 32, 42, 52, 62, 72, 82, 92: Ordinal = IntNum & "nd"
Case 3, 23, 33, 43, 53, 63, 73, 83, 93: Ordinal = IntNum & "rd"
Case Else: Ordinal = IntNum & "th"
End Select
End Functionwhich you could call like:
Sub Test()
Dim IntVal As Integer, Result As String
IntVal = InputBox("Please input a number")
Result = Ordinal(IntVal)
MsgBox Result
End Sub

[fumei]

Or....

Function AddEnding(strIn As String) As String

Select Case Right(strIn, 1)
Case "1"
AddEnding = strIn & "st"
Case "2"
AddEnding = strIn & "nd"
Case "3"
AddEnding = strIn & "rd"
Case Else
AddEnding = strIn & "th"
End Select
End Function

which you could call similarly:

Sub Test()
Dim IntVal As String, Result As String
IntVal = InputBox("Please input a number")
Result = AddEnding(IntVal)
MsgBox Result
End Sub
Note that I changed the input parameter to a string, rather than a number. It could be either. As previously stated, the function applies the rules to the last character so 21, or 45,361 gets the same result.

In any case, both macropod's method, or mine, does the same thing really. Except that with macropod's code

23,467 input becomes 23467th - no comma

My code retains the comma (as it uses a string).

23,467 input becomes 23,467th.

[miketallica]

so im pretty sure the last post is the one that will work the best but now im not sure where to put it or how to tigh it in. im guessing it needs to be part of the form but hidden. so that when you tab into the day textbox it automatically populates the current day which inturn decides what ending is required in the hidden textbox..... right?.... this **** is confusing sometimes. i attached the actual document. dont know why i didnt just do that in the first place.

[fumei]

Sorry, but that is a Word 2007 file. I can not read that. I am sure someone else can help though.

You should perhaps clarify what you mean by a hidden textbox.

[macropod]

Hi miketallica,

I had a look at your document and I must say you're approaching this problem in a rather strange way. For some reason, you're using a userform to populate a document full of formfields, many of which are duplicates. This is illogical - you should either:
. have a useform that populates a few bookmarks; or
. have a few formfields with the 'calculate on exit' property set and appropriate bookmark names,
then use cross-references within the document to replicate the bookmarked text.

With the latter approach, you don't even need any vba.

[fumei]

I finally got to open this document and look at it. This is atrocious use of Word.

[macropod]

Now, fumei, be nice ...

You've probably done just as badly at one time or another.

[miketallica]

i understand what your saying macropod. that was my original intent. but the problem i ran into was that you had to save or print preview for the text ref's to update. i wasnt sure if it was possible to update the document with vba when exiting the form so i did it the best way i could but you both are right it's definitley rigged.

[macropod]

Hi miketallica,


you had to save or print preview for the text ref's to updateThat would only be so if:
. the formfields didn't have bookmark names; and/or
. the 'calculate on exit' option wasn't checked.

[fumei]

macropod, fair enough. 'Tis true. And very likely not the fault of the OP. We often get documents that originated from someone else.

So...my apologies. It is still an atrocious mangle of how Word works (no use of Styles, the use of formfields for purposes that formfields are not designed to be used for, the use of text in the document that looks like it really should be in a footer, etc.). However, let's try and take a positive spin.

Mike, what can we do to help you? macropod is correct. There are a number of cases where information is replicated. In which case, you should use replication. There are a number of ways to repeat data, the primary one is (as macropod mentions) fields that refer back to another field. If that field is a formfield, then having Calculate on exit set as True works very very well.

I would like to point out, though, that formfields are designed for user input. The user enters text into the formfield. However, since your code (example follows):
.FormFields("Text11").Range.Text = Text5.Value
.FormFields("Text12").Range.Text = Text2.Value
.FormFields("Text13").Range.Text = Text3.Value
puts the text values into the range of the formfield - as I stated previously - this deletes the formfield. In which cases, why have the formfield in the first place???

Further, as you can see from the above code, it is very very difficult to truly understand what is happening, what is going where. There is not even any matching of numbers.

Text11 gets Text5.Text (or as you have it .Value, but .Text is better)

Text12 gets Text2.Text

Text13 gets Text3.Text

While it may work, it certainly does not make any readable sense.

[miketallica]

i originally had it set up the way you guys are talking about except for my lack of experience in VBA code. but i wasn't sure if i could update the ref fields with out doing a print preview or save. however i can remember if i tried calculate on exit. does that mean if that is checked it will update the ref fields immediately? that was the reason i didnt use a footer because i couldnt put the form field in it. i did notice i could use a ref field but then again i have the updating issue. i'll check in to the calculate on exit

[fumei]

"that was the reason i didnt use a footer because i couldnt put the form field in it."

You can put formfields in footers...but again, formfields are for user input. If the user are not going to actually put something there themselves, then...

If you set up the ref fields correctly, and the data is coming from a formfield, then Calculate on exit will automatically update.

[miketallica]

calculate on exit works just like i want. that's awesome. and i noticed that i can put the activeX controls in the footer but not the legacy. i've always used legacy because the last place i worked at had us using word 02. they were pretty behind on there software. what about bookmarks? i guess i can use bookmarks instead of the form fields since i am having a userform come up for them to fill out anyway. but can i reference bookmarks? i dont see why i cant. and do they have a calculate on exit option?

[fumei]

"but can i reference bookmarks? "

Yes of course.

"do they have a calculate on exit option?"

No, of course not. How could they? There is no entrance...so how could there be an exit?

[macropod]

Hi miketallica,

You can't have a formfield in a header or footer, and even if a formfield in the body of the document has the 'calculate on exit' attribute checked, a cross-reference to it in the header or footer won't update - you'll need a macro for that. You could attach a macro to the relevant formfield's 'on exit' property, coded as:

Sub UpdateHeadFoot()
Application.ScreenUpdating = False
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

[macropod]

Hi miketallica,

Another way to cross-reference a formfield's result in the header or footer is via a STYLEREF field.

The way to do this is to apply a unique Style to the desired formfield - you could choose an existing Style, or create a new one. You then put that Style's name in the STYLEREF field.

With this approach, you don't need a macro, or even the 'calculate on exit' property, since a STYLEREF field updates automatically of its own accord.

[macropod]

Or....

Function AddEnding(strIn As String) As String

Select Case Right(strIn, 1)
Case "1"
AddEnding = strIn & "st"
Case "2"
AddEnding = strIn & "nd"
Case "3"
AddEnding = strIn & "rd"
Case Else
AddEnding = strIn & "th"
End Select
End Function
Hi fumei,

The only problem with your function is that it doesn't work correctly for numbers ending in 11, 12 & 13. How about:Function Ordinal(IntNum As Integer) As String
Select Case IntNum Mod 100
Case 1, 21, 31, 41, 51, 61, 71, 81, 91: Ordinal = "st"
Case 2, 22, 32, 42, 52, 62, 72, 82, 92: Ordinal = "nd"
Case 3, 23, 33, 43, 53, 63, 73, 83, 93: Ordinal = "rd"
Case Else: Ordinal = "th"
End Select
End Function

Sub Test()
Dim StrVal As String, Result As String
StrVal = InputBox("Please input a number")
If IsNumeric(StrVal) Then
Result = StrVal & Ordinal(CInt(StrVal))
MsgBox Result
Else
MsgBox StrVal & " is not a number", vbCritical
End If
End Sub

[fumei]

Yeah, I already figured out there is an issue with those weird English exceptions like 13 (et al), and adjusted my own function. Yes, your function works fine, and as you well know, there are other ways to write the function (VBA being as it is). As I do not really know what the input may be, I like your use of Mod 100. But, whatever.

And, duh, yes of course you are correct. You can not put formfields in header/footers.

[miketallica]

Hi miketallica,

You can't have a formfield in a header or footer, and even if a formfield in the body of the document has the 'calculate on exit' attribute checked, a cross-reference to it in the header or footer won't update - you'll need a macro for that. You could attach a macro to the relevant formfield's 'on exit' property, coded as:

Sub UpdateHeadFoot()
Application.ScreenUpdating = False
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

yeah, now i remember i did have my form set up clean like everybody has been talking about but that was the problem i was running into! that is a life saver. thanks.