Correct code for coloring a userform inkEdit textbox text string with the text string on multiple lines in the code

[chazrab]

As stated above. This code does the correct job of coloring the text string - as long as the string in the code is on ONE line:

Private Sub UserForm_Activate()
Dim firstpost, lastpos As Integer
inkText.Text = "And He said to them," & Chr(34) & "Do you not see all these things? Truly I say to you, not one stone here will be left upon another, which will not be torn down." & Chr(34) & "Matthew 24:2 (NASB)"
inkText.SelStart = 0
inkText.SelLength = Len(inkText.Text)
inkText.SelColor = vbBlue
inkText.SelFontSize = 18
firstpos = InStr(1, inkText.Text, Chr(34))
lastpos = InStr(firstpos + 1, inkText.Text, Chr(34))
inkText.SelStart = firstpos
inkText.SelLength = lastpos - firstpos - 1
inkText.SelColor = vbRed
inkText.SelLength = 0
End Sub
[code]
image below show result of this code.  I have a lot of text I wish to color red.  Its impossible to put a very long text string on one line of code
When I try to separate a long text string into shorter line segments in the code, I keep getting "Expected end of statement" messages.  

The bolded code below is what I want to accomplish with very long text strings, achieving the same result as the image displayed with the text string on one line.
Its a matter of separating a long string into multiple lines and still have the correct punctuation to display the entire text string in vbred.

This code gives that error message and this text is colored red
[code]
Dim firstpost, lastpos As Integer
inkText.Text = "And He said to them," _
 & Chr(34) & "Do you not see all these things?" _
 Truly I say to you, not one stone here will be left"
upon another, which will not be torn down." & Chr(34) & _
"Matthew 24:2 (NASB)"
[generates "Expected end of statement erro"r]

inkText.SelStart = 0
inkText.SelLength = Len(inkText.Text)
inkText.SelColor = vbBlue
inkText.SelFontSize = 18
firstpos = InStr(1, inkText.Text, Chr(34))
lastpos = InStr(firstpos + 1, inkText.Text, Chr(34))
inkText.SelStart = firstpos
inkText.SelLength = lastpos - firstpos - 1
inkText.SelColor = vbRed
inkText.SelLength = 0

Using this string as a model, how do you put this text string on any number of multiple lines and still have it displayed correctly in red ?

Please help if you can. Thanks very much
Thanks, cr

 

[Micron]

You've forgotten your leading ampersands and line leading double quotes and some spaces between words. You also have an extraneous ampersand before a line continuation character. Try

inkText.Text = "And He said to them," _
& Chr(34) & "Do you not see all these things?" _
& " Truly I say to you, not one stone here will be left" _
& " upon another, which will not be torn down." & Chr(34) _
& "Matthew 24:2 (NASB)"

 

[chazrab]

You've forgotten your leading ampersands and line leading double quotes and some spaces between words. You also have an extraneous ampersand before a line continuation character. Try

inkText.Text = "And He said to them," _
& Chr(34) & "Do you not see all these things?" _
& " Truly I say to you, not one stone here will be left" _
& " upon another, which will not be torn down." & Chr(34) _
& "Matthew 24:2 ( NASB)"

Hi Micron - this works great. Exactly what I've wanted for a long time. Have to do this with 3K+ text strings, which is why
I needed the text strings on multiple lines in the code. so I've a ton of work ahead.
Can't thank you enough for helping. Things seem real easy - if you know what to do.
Image below shows result of your code
Thanks again. cr

 

[Micron]

You're welcome and thanks for the recognition.

There is another method which I prefer to use most of the time. One reason would be that I've copied samples from M$ pages using the line continuation method and they are still syntactically incorrect. The method is like

Dim str As String
str = "And He said to them," & Chr(34) 
str = str & " Do you not see all these things?" 
str = str & " Truly I say to you, not one stone here will be left"
str = str & " upon another, which will not be torn down." & Chr(34)
str = str & " Matthew 24:2 (NASB)"
inkText.Text = str

Not that I would make the lines that short though. Note that the necessary space is at the beginning of each line. Easier to spot than at the end of lines that are all different lengths. Not sure you need to use the Text property for the control though.

 

[chazrab]

You're welcome and thanks for the recognition.

There is another method which I prefer to use most of the time. One reason would be that I've copied samples from M$ pages using the line continuation method and they are still syntactically incorrect. The method is like

Dim str As String
str = "And He said to them," & Chr(34)
str = str & " Do you not see all these things?"
str = str & " Truly I say to you, not one stone here will be left"
str = str & " upon another, which will not be torn down." & Chr(34)
str = str & " Matthew 24:2 (NASB)"
inkText.Text = str

Not that I would make the lines that short though. Note that the necessary space is at the beginning of each line. Easier to spot than at the end of lines that are all different lengths. Not sure you need to use the Text property for the control though.

 

[chazrab]

Hi again Micron

The text string that makes this work is contained within the code. The application I'm trying to integrate the inkText red text string into
is a normal textbox that gets its values from Listbox1 tied to a Rowsouce. That is, each text string is in its own cell down column D of Sheet names "MATT24". The text is currently displayed in Textbox1 of the userform this way:

Private Sub ListBox1_Change()
Dim n As Long
n = Me.ListBox1.ListIndex
ListBox1.ListIndex = n
TextBox5.Value = n  --->row number as Listbox1 is scrolled down
End Sub

Private Sub ListBox1_Click()
Dim n As Long
n = ListBox1.ListIndex
Me.TextBox1.Value = ListBox1.List(n, 3) _
& vbCrLf _
& vbCrLf _
& ListBox1.List(n + 1, 3) _
& vbCrLf _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 2, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 3, 3) _
& vbCrLf + ListBox1.List(n + 4, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 5, 3) _

The carriage returns just separate the text strings in each Rowsource cell into paragraphs, one paragraph per Rowsource sheet cell down column D. The image below shows the way the code displays each cell value - and it works great - except the text is all the same textbox Properites Forecolor - blue. I'd like very much to achieve the same display effect with the inkText Textbox but with the ability to display each cell value specified text string within each cell in red. This is presenting a bit of a little challenge for me in figuring out how to do this.

This is the code you did with the small change I made in the commented line:

Private Sub UserForm_Activate()
Dim firstpost, lastpos As Integer

Dim rows As Integer
rows = Sheets("MATT24").Range("B2").End(xlDown).Row
TextBox4.Value = Sheets("MATT24").Range("H1").Value  'total rows in Sheet col.

Dim str As String
str = Sheets("MATT24").Range("D1").Value '--> I assigned str to the value in cell D1.  This is as far as I've gotten.
str = "And He said to them," & Chr(34)
str = str & " Do you not see all these things?"
str = str & " Truly I say to you, not one stone here will be left"
str = str & " upon another, which will not be torn down." & Chr(34)
str = str & " Matthew 24:2 (NASB)"
inkText.Text = str

inkText.SelStart = 0
inkText.SelLength = Len(inkText.Text)
inkText.SelColor = vbBlue
inkText.SelFontSize = 18
firstpos = InStr(1, inkText.Text, Chr(34))
lastpos = InStr(firstpos + 1, inkText.Text, Chr(34))
inkText.SelStart = firstpos
inkText.SelLength = lastpos - firstpos - 1
inkText.SelColor = RGB(155, 15, 15) - I also made the red text a little darker to be less obtrusive than basic vbRed
inkText.SelLength = 0

End Sub

Do you think you could help me with this a little further on how to figure out how ot do this when you have a chance?
Thanks again for all your help.

cr

 

[Micron]

This I don't get
n = Me.ListBox1.ListIndex
ListBox1.ListIndex = n

If listindex = 3 then n = 3. Next line makes the listindex 3 because n = 3, but the index is already 3??
Would this not work and be simpler?
TextBox5 = ListBox1.ListIndex

I'm a bit confused. You say text is hard coded (not good) yet there is a range of column cells, each with their own text? So why not refer to that? Also, you say you want it to be red

display each cell value specified text string within each cell in red

but show it all in blue. I take it that is your result but it's not what you want. I think what's in post 1 is what you want.

This all looks familiar to me and I'm wondering if I helped you out with this project before. Can you upload this file to a file share and post (or pm) a link and confirm that post 1 is the desired output?

 

[Micron]

Another point for clarification. If you are trying to do this in a sheet cell it is doable. If trying to do this in a standard textbox it is not.
EDIT - never mind, I see that is not the case

 

[Micron]

This is my result with your second block of code it post 6

While I've learned something new about Excel controls I'm not making any headway in figuring out what isn't working for you. I can say that the text shouldn't be hard coded, I'm not understanding the need for the normal textbox, nor the need for line wraps in code (better to be in the sheet cell?). I have to believe that a lot of things would be clearer if I could see the file.

 

[chazrab]

Hi Micron. I'm going to try to send the entire app in Dropbox. I've not used it much and need to make sure I'm doing it right so you'll get
the link and be able to open the file without any problems. You're correct saying that the original post using the string variable str is the code
that makes the text string red. As simply as I can explain, all I'd like to do is continue the str code block as the result in post1 which seems to be the same as post 6 in the vb editor and adding more text lines using Chr(34) and " " in the correct places for the text strings to be red. In studying your code, I ran into problems in trying to color subsequent text strings red - i.e., they all stayed blue as in the second image below. Here's what I mean:

Private Sub UserForm_Activate()
Dim firstpost, lastpos As Integer

Dim rows As Integer
rows = Sheets("MATT24").Range("B2").End(xlDown).Row
TextBox4.Value = Sheets("MATT24").Range("H1").Value

Dim str As String
str = "And He said to them," & Chr(34)  
str = str & " Do you not see all these things?"
str = str & " Truly I say to you, not one stone here will be left"
str = str & " upon another, which will not be torn down." & Chr(34)
str = str & " Matthew 24:2 (NASB)" _
(this code above works correctly making the text in quotes red.)
& vbCrLf _
& vbCrLf
str = str & " As He was sitting on the Mount of Olives,"
str = str & " the disciples came to Him privately, saying,"
str = str & " Tell us, when will these things happen,"
str = str & " and what will be the sign of Your coming,"
str = str & " and of the end of the age?” "
str = str & " Matthew 24:3 (NASB)" _
& vbCrLf _
& vbCrLf
(This code immediately above is all in blue as it should be)


str = str & " And Jesus answered and said to them," & Chr(34) -->should be in red text a sin Word doc image below
str = str & " See to it that no one misleads you." & Chr(34) -->should be in red text a sin Word doc image below
str = str & " Matthew 24:4   (NASB)" _
& vbCrLf
str = str & " For many will come in My name, saying, ‘I am the Christ,’ and will mislead many," & Chr(34) -->should be in red text a sin Word doc image below
str = str & " Matthew 24:5   (NASB) "
str = str & " (Proclaiming to be Jesus, or preaching Jesus Christ is Jesus but twisting His Truth)"
(comments show where code does not show text in red where indicated it should.  To me, the text between each set of Chr(34) markders should be in red, but as the second image shows its all in blue, yet I've copied the code exactly and to me, it should give exactly the same results. Confusing.
inkText.Text = str
inkText.SelStart = 0
inkText.SelLength = Len(inkText.Text)
inkText.SelColor = RGB(15, 15, 200) 'vbBlue
inkText.SelFontSize = 12
firstpos = InStr(1, inkText.Text, Chr(34))
lastpos = InStr(firstpos + 1, inkText.Text, Chr(34))
inkText.SelStart = firstpos
inkText.SelLength = lastpos - firstpos - 1
inkText.SelColor = RGB(155, 15, 15) 'vbRed
inkText.SelLength = 0

End Sub

Hopefully, this is clear. i don't want to make any more confusing than it is by giving too much information, but I also don't to leave out
anything that might help understand where the issues are. I'll send the entire file via Dropbox. Give me about an hour or so to
do this. You have helped me before in this, and I apologize for going over old ground. I don't know why I've not been able to solve this
with earlier post helps.

Thanks again for all yourhelp. This is the closest I've come to getting this critical component of the app completed using the inkEdit control thanks to your help. Please reply with any feedback/comments you might have while I'm working to send the file to you via Dropbox if you still need it
in the meantime.

cr