Solved: Limit Tbox entry to line length

[Dave]

Using XL VBA I am adding a line of text to a Word document...
.typetext Text:=StrVar1 + " Name: " + StrVar2 & " " & StrVar3 & vbCrLf
As can be seen, string variables (StrVar's) are added to text to make the complete line. StrVar1 and StrVar2 can be of variable length. What I'd like to do is limit StrVar3's length so that its' addition to the line will not exceed 1 line. StrVar3 is entered via textbox so my plan is to limit character entry into this textbox based on what already exists in the line (ie. Len(StrVar1) + Len(StrVar2) + text length(ie. " Name: " and " " ) equals existing length.
Private Sub textbox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'toolong = ???????
If StrVar3 >= TooLong Then KeyAscii = 0
End Sub
Seemed easy enough until I realized with the font I'm using you could have either 38 "W"'s or +120 "l"'s on one line. So how to determine "toolong" for StrVar3 when Len is irrelevent? Dave
edit: fixed tags

[fumei]

Can you expand the reasoning for the requirement?

1. WHY is this important to be on one line?

2. How important can strVar3 be when you are willing to truncate it depending on whether its addition makes the paragraph go to another line?

What is critical here?

[Dave]

Good afternoon Gerry and thanks for your interest. The critical part goes back to that "chunk" thing of a previous thread. If this line exceeds 1 paragraph length then the "chunk" (aka. a block of paragraphs) size expands which messes everything up. The strvar3 variable is for the purpose of adding miscellaneous notes to the paragraph. So the length of the notes (strvar3) can be limited without too much user frustration. Perhaps not ideal but workable. I'd sure appreciate any of your thoughts on how to achieve this. Dave

[fumei]

Arrrggh.

this is text this is text this is text this is text this is text

ONE PARAGRAPH!


this is text this is text this is text this is text this is textthis is text this is text this is text this is text this is textthis is text this is text this is text this is text this is textthis is text this is text this is text this is text this is textthis is text this is text this is text this is text this is text


ONE PARAGRAPH!

Both of the above are ONE paragraph. This is what you get for using "chunks" and counting lines.
If this line exceeds 1 paragraph length then the "chunk" (aka. a block of paragraphs) size expands which messes everything up. A paragraph does NOT have a specified length.

A paragraph length is precisely the length between paragraph marks.

How does it - please describe exactly - "mess everything up"???

If you are dealing with actual paragraphs - which you apparently are not - then the added length is irrelevant.

Adding notes to the paragraph....aaarrrrggggh. Notes? You can not add "notes" (comments yes) to a paragraph. You add text to a paragraph. What? Are these notes to be considered (somehow) independent or separate from the paragraph?

Say that chunk has three paragraphs. You add text to paragraph 2, and now paragraph 2 covers 2 lines, instead of 1.

There are STILL only three paragraphs.

Say you add text to paragraph 2 and now paragraph 2 covers 117 lines.

There are STILL three paragraphs.

This is the point to using paragraph objects. The length of the object is not relevant.

Doing XYZ action to paragraph 2 (covering 2 lines) is EXACTLY the same as doing XYZ action to paragraph 2 (covering 117 lines).

I will qualify that. It can depend on what XYZ is doing. But for most actions, no.

ANY format action? Nope, length/line count is irrelevant.
Style use? Nope, length/line count is irrelevant.
Search text? Nope, length/line count is irrelevant.

But....sigh.....describe exactly the situation please.

[fumei]

I guess my point is that you may be stuck on this one. As you noted in your own post...Len is irrelevant because of kerning.

That is, until you put the text IN, it is very difficult to determine if it will push the paragraph mark to another line, or not.

Technically you could parse through each character in the string to be inserted, figure out how many "i" vs how many "w", and calculate how much real estate it will take.

1. BOOOOOORING. You would have to do a calculation for every possible letter.....BOOOOOOORRRING.

2. The calculation would only work on THAT machine, with THAT printer driver, as Word places text on a page directly from its interpretation algorithms from the printer driver.

[Dave]

Gerry it seems like I'm starting to push your paragraph buttons :friends:
Does this really means that there are some things that can't be done?
I'm going to read up on kerning and I never considered the printer problem. Apologies again for mixing my line and paragraph terms. The ouput would look something like this:

GrnName BUYER: BuyerName Notes: Bin1 Grade 1Can

As you can see the "note" section of this top line (which is indeed a paragraph) can be of variable length depending upon user input. If the content of "Notes:" gets too long (based on availability of line length re. variable length of GrnName & BuyerName) then the paragraph does indeed become 2 lines which causes the aforementioned mess up. Not the block of "paragraphs" which I mistated. No worry if it can't be done, I'll just set a conservative limit rather than be exact about it. Thanks again for your time and valuable input. Dave

[Tommy]

Just as a suggestion, not seeing anything else in the doc's. But I think I would look into using subscripts with the definitions in the footer of the doc which "shouldn't" mess thing up too much, maybe yes/no ? :)

[Dave]

Thanks Tommy for your suggestion but I'm not sure if I understand it. If you're referring to placing subscript "notes" in the document footer, I don't think that this will work as each document has multiple "chunks" and therefore has multiple notes. The "notes" do not need to be in subscript and I'm not sure what happenned to my previous eyesore post (if a moderator can fix it I'd appreciate it). I did some reading up on kerning and it seems that the text already has to be in a document in order to adjust it (vs being on route to the document). I guess I'll stick with my approach of conservativly setting the maximum character length of the "notes" tbox and hope that this works. I really don't like having the possibility of a mess/error but I guess that's what I get for "counting". Again, thank you for your input. Dave

[fumei]

Dinna fess yourself. I am just being dramatic.

Notes:" gets too long (based on availability of line length re. variable length of GrnName & BuyerName) then the paragraph does indeed become 2 lines which causes the aforementioned mess upWHAT mess up??

It is mentioned, but not described.

WHY does this paragraph becoming two lines cause a problem?

[fumei]

Oh, and BTW....why are you still using .TypeText?

[Dave]

Gerry as you mentioned I am counting lines (which I'm afraid are also paragraphs). So if 1 paragraph becomes 2 lines by some long winded textbox "note" typer, then I no longer "know" the whereabouts of all my inputted data into the document. This "messes" up the output from the same document which I realize is sort of removed from my previous posts which may have, in retrospect, alluded to the input being messed up. For example, if I have inputted 4 lines/paragraphs (eg. a "chunk") into a document and these same 4 lines become 5 or 6 lines by some excessive "notes" typing, then outputting the entire "chunk" becomes impossible because I no longer "know" how big it is. The advantage of having the "notes" section is that it allows the user to group together output based on their "notes" added. The .typetext seems to work in XL VBA. Gerry I'm not sure if this is worth any further efforts. I read up on kerning and it seems like the text has to be in the doc not en route. As you inuendoed probably impossible and I do have a workable (not ideal) solution. I thought maybe I'd add a cautionary user warning... or perhaps just place a stickie on my monitor. Anyways, thanks again for your help. If I arrive at something more precise I'll post. Dave

[Dave]

edit: whoops double entered that post

[fumei]

For example, if I have inputted 4 lines/paragraphs (eg. a "chunk") into a document and these same 4 lines become 5 or 6 lines by some excessive "notes" typing, then outputting the entire "chunk" becomes impossible because I no longer "know" how big it is. ......sigh.

[Dave]

Found a way to get rid of my stickie with precision. By just adding another paragraph to the document you can then determine the added paragraph's line location and then by proxy determine if the first paragraph (the real data) was more than 1 line. This does however require opening the document which I was trying to avoid. I think I'll keep my textbox character entry limit code and then just to ensure the "mess" doesn't happen I'll use the following code. It uses selections and paragraphs :devil2:
Maybe it's a project complete? Dave

Textbox code...

Private Sub textbox3_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
'textbox allow only 26 chars
If Len(TextBox3.Text) > 26 Then KeyAscii = 0
End Sub


mess fix...

Sub OneLinePara()
Dim Wdapp As Object, StrVar1 As String
Dim StrVar2 As String, StrVar3 As String
'Ensures paragraph(1) containing variable...
'..strings is 1 line only. No Word reference
'requires 3 textboxes (or strings)
StrVar1 = TextBox1.Text
StrVar2 = TextBox2.Text
StrVar3 = TextBox3.Text
On Error GoTo FixEr
Set Wdapp = CreateObject("Word.Application")
'Open file. Change path to suit
Wdapp.documents.Open Filename:="C:\test1.doc"
With Wdapp.Selection
.typetext Text:=StrVar1 + " BUYER: " + _
StrVar2 & " Notes: " & StrVar3 & vbCrLf
.typeparagraph 'insert temp paragraph(2)

If CheckLine(Wdapp) Then
'continue with data entry here
.typetext Text:="That worked!" & vbCrLf
Wdapp.Visible = True ' used for testing
'Wdapp.activedocument.Close savechanges:=True
Else
MsgBox "No input saved. TextBox3(Notes) is Too long"
Wdapp.activedocument.Close savechanges:=False
Wdapp.Quit
End If
End With
Set Wdapp = Nothing
Exit Sub

FixEr:
On Error GoTo 0
MsgBox "File error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub

Public Function CheckLine(WrdAp As Object) As Boolean
'checks line location of temp paragraph(2) 1st char
'returns false if not on line 2
CheckLine = False
'select temp paragraph
WrdAp.activedocument.paragraphs(2).Range.Select
'check if line number or temp paragraph
'wdFirstCharacterLineNumber=10
If WrdAp.Selection.Information(10) = 2 Then
'remove temp paragraph
Selection.Delete
CheckLine = True
End If
End Function

[Dave]

Well I really wouldn't recomend that last approach even though it seemed to work so nice on testing. When I tried to adapt it for controlling the length of tbox input on a userform, I encountered a miscellany of errors. I finally settled on the following approach which has worked well without error (so far). I'll mark this thread as solved. Dave

Sub OneLineOnly()
Dim Wdapp As Object, StrVar1 As String
Dim StrVar2 As String, StrVar3 As String
Dim MsgFlag As Boolean
'Ensures paragraph(2) containing variable...
'..string lengths is 1 line only. No Word reference
'trims Tbox3 string length
'requires 3 textboxes (or string variables)
StrVar1 = TextBox1.Text
StrVar2 = TextBox2.Text
StrVar3 = TextBox3.Text
MsgFlag = False
On Error GoTo FixEr
Set Wdapp = CreateObject("Word.Application")
'adjust filepath to suit
Wdapp.documents.Open Filename:="C:\test1.doc"
Above:
With Wdapp.Selection
.typeparagraph
.typetext Text:=StrVar1 + " BUYER: " + _
StrVar2 & " Notes: " & StrVar3 & vbCrLf
.typetext Text:="Next line" & vbCrLf
End With
Wdapp.activedocument.paragraphs(4).Range.Select
'wdFirstCharacterLineNumber=10
'check if start of para 4 is on line 4
If Wdapp.Selection.Information(10) > 4 Then
Wdapp.activedocument.paragraphs(3).Range.Select
Wdapp.Selection.Delete
Wdapp.activedocument.paragraphs(2).Range.Select
Wdapp.Selection.Delete
Wdapp.activedocument.paragraphs(1).Range.Select
Wdapp.Selection.Delete
StrVar3 = Left(StrVar3, Len(StrVar3) - 1)
MsgFlag = True
GoTo Above
End If
With Wdapp.Selection
'continue with doc input
.typetext Text:="Continue entry" & vbCrLf
End With
Wdapp.activedocument.Close savechanges:=True
Wdapp.Quit
Set Wdapp = Nothing
If MsgFlag Then
TextBox3.Text = StrVar3
MsgBox "Tbox3/Notes were too long. Notes entered as: " & vbCrLf _
& StrVar3
End If
Exit Sub
FixEr:
On Error GoTo 0
MsgBox "File error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub

[fumei]

And why are you using? Wdapp.activedocument.paragraphs(3).Range.Select
Wdapp.Selection.Delete

Is there a reason for first selecting, then deleting? If you know the paragraph, then just delete it.
Wdapp.activedocument.paragraphs(3).Range.Delete There is no need to select and delete. You can delete directly.

[Dave]

It did seem kind of cumbersome. Thanks Gerry that works perfect. Dave