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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
917
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 

Attachments

  • CORRECT TEXT STRING COLOR BEGINNING AND ENDING RED TEXT.png
    CORRECT TEXT STRING COLOR BEGINNING AND ENDING RED TEXT.png
    36.5 KB · Views: 19
Hi Micron. I think I did this right. Here's the link to the file in Dropbox:


If you successfully received the file, click on the inkText button. You'll see the text displayed incorrectly because the code I
tried to get to work is wrong. The images I sent above should explain(Word doc shows correct desired result).
I'm getting the code wrong starting at some point. Below is the updated image of this issue.

Hope you got the file. Please help if you can, and once again, thank you very much for all your help.
Just an endnote summary: the main end goal is to have 3,000 lines of text strings within blue text strings display in red text
in an inkEdit control displayed on a userform. If i can mange to do this, this app will be in its final stage of development.

cr
 

Attachments

  • UPDATED INCORRECT DISPLAY.png
    UPDATED INCORRECT DISPLAY.png
    78.5 KB · Views: 7
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
After a quick glance I'd say you're going to need markers. Based on that, here's a rough but untested outline.
One way would be for you and/or others to put line wraps in the text before and after each quoted portion. I did this for D2; my values in brackets.
Then I think use a count function to count how many. Say that's 4 (has to be an even number). Loop from 1 to 4.
Then use Instr starting at 1 to find the first chr(10) position [28]. That +1 (29) is the start point for red text.
Loop to find second carriage return.
Inst again to get the next chr(10) starting at 29 get the next chr(10) [158]. That -1 is the first portion to colour. Each time the variable for start position gets incremented.

Unless you think that using double quotes could be the markers then that's another thought but I have to go out and can't think it over right now.
 
Upvote 0
After a quick glance I'd say you're going to need markers. Based on that, here's a rough but untested outline.
One way would be for you and/or others to put line wraps in the text before and after each quoted portion. I did this for D2; my values in brackets.
Then I think use a count function to count how many. Say that's 4 (has to be an even number). Loop from 1 to 4.
Then use Instr starting at 1 to find the first chr(10) position [28]. That +1 (29) is the start point for red text.
Loop to find second carriage return.
Inst again to get the next chr(10) starting at 29 get the next chr(10) [158]. That -1 is the first portion to colour. Each time the variable for start position gets incremented.

Unless you think that using double quotes could be the markers then that's another thought but I have to go out and can't think it over right now.
Trying to use suggested steps for markers now. In the meantime, what's troubling me is,
shouldn't the indicated text be made red with the following code with beginning and ending Chr(34) ? It doesn't. The result is in the image below. =Still gives lines between the Chr(34)'s in blue
code]
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) --->blue as should be
str = str & " Do you not see all these things?" ---> displays red as should be
str = str & " Truly I say to you, not one stone here will be left" ---> displays red as should be
str = str & " upon another, which will not be torn down." & Chr(34) ---> displays red as should be
str = str & " Matthew 24:2 (NASB)" _
& vbCrLf _
& vbCrLf
str = str & " As He was sitting on the Mount of Olives," --->blue as should be
str = str & " the disciples came to Him privately, saying," --->blue as should be
str = str & " Tell us, when will these things happen," --->blue as should be
str = str & " and what will be the sign of Your coming," --->blue as should be
str = str & " and of the end of the age?” " --->blue as should be
str = str & " Matthew 24:3 (NASB)" _--->blue as should be
& vbCrLf _
& vbCrLf
str = str & " And Jesus answered and said to them," & Chr(34) --->next line should begin red text
str = str & " See to it that no one misleads you." & Chr(34) --> not red. Why not? Exact same beg. and end. chr(34) positions and quotes in above one that is correct
str = str & " Matthew 24:4 (NASB)" ---> blue as should be
& vbCrLf _
& vbCrLf
str = str & chr(34) & " For many will come in My name, saying, ‘I am the Christ, and will mislead many," & Chr(34) --->this line should be in red text
str = str & " Matthew 24:5 (NASB) " ---> blue as should be

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

[/code]

Sorry about the back and forth on this - I really want to make this work. And I feel like this code is very close.
Have to do this for 1900 words. The way I see it now, this inkText code will be very long. I thought about writing
some kind of Gosub routine to shorten this task.
Again, working on marker suggestion steps.

Many thanks again for all your help.
cr
 

Attachments

  • THIS IS WHAT I GET WITH THIS CODE.  I JUST REPEATED YOUR CODE THE NEW TEXT, BUT ITS NOT TURNIN...png
    THIS IS WHAT I GET WITH THIS CODE. I JUST REPEATED YOUR CODE THE NEW TEXT, BUT ITS NOT TURNIN...png
    63 KB · Views: 3
Upvote 0
I have written a procedure that results in the following within the form control. Let me know if correct then I'll explain what I did. It uses double quotes as markers
1698445911636.png
 
Upvote 0
I have written a procedure that results in the following within the form control. Let me know if correct then I'll explain what I did. It uses double quotes as markers
View attachment 101111
Yes - red text is supposed to be red and the chapter verse, "He asked", and "Matthew 24:2(NIV) should all be in blue as they are.
Unless I'm missing something, there's no code block with this. Assuming you just wanted a confirmation on what should be red and what should be blue. Just let me know next steps.

Thanks again for all your help.
cr
 
Upvote 0
Yes, just a confirmation. No code posted because of the associated changes required. I'll probably have to post tomorrow or pm you as there's a lot to tell you that won't really interest anyone who is looking to do this in the future.
 
Upvote 0
Yes, just a confirmation. No code posted because of the associated changes required. I'll probably have to post tomorrow or pm you as there's a lot to tell you that won't really interest anyone who is looking to do this in the future.
Thanks. No hurry. Get back to me tomorrow at your convenience. The focus for me is to use correct cod as a model to apply to all 3K words.
This is a huge learning experience for me. I was ready to forget about trying to display the entire 4 gospel words in red correctly and just
use regular textboxes with blue text. The goal is to display verse text strings in paragraphs separated by line continuation spaces for easier
reading. This is super easy in the Click event of a regular textbox and a listbox Rowsource like this:
Code:
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 image below shows the result of this code.

The userform overlay is a note attached to each verse activated by the little blue button.
I mention this because I don't know if an inkEdit control would allow indexing each paragraph some way. If so, that would
negate the need for the data to attach to an underlying spreadsheet. Just ignore this for now. Not relevant to current problem issues.

With an inkEdit textbox it's a different ball game.

I know very little about the functional capability of an inkEdit control other than coloring text strings in code in the vb editor.. If I can manage to just put text paragraphs in an inkEdit control, with carriage returns between paragraphs, that's fine.

I see this code block having a very long length to be able to cover 3K+ words -
unless I make 2 inkEdit controls on several userforms and break up the length that way. Just some initial thoughts
(I try to think ahead and anticipate issues before I reach them). That said, the display is the end goal.

Please continue with helping me at your convenience.

Thanks again for all your help. Until tomorrow.
cr
 

Attachments

  • MAIN USERFORM WITH CODE ABOVE AND TWO REGULAR TEXTBOXES TIED TO A LISTBOX ROWSOURCE.   .png
    MAIN USERFORM WITH CODE ABOVE AND TWO REGULAR TEXTBOXES TIED TO A LISTBOX ROWSOURCE. .png
    185.4 KB · Views: 5
Upvote 0
The goal is to display verse text strings in paragraphs separated by line continuation spaces for easier
reading.
You're making my head spin! When did this become a goal and what does it have to do with blue and red text? I saw that code before but ignored it because it didn't seem pertinent to colouring text. Anyway, getting late here so signing off for tonight.
 
Upvote 0
You're making my head spin! When did this become a goal and what does it have to do with blue and red text? I saw that code before but ignored it because it didn't seem pertinent to colouring text. Anyway, getting late here so signing off for tonight.
Sorry for the confusion. Did not mean to do that. Don't know what made me go off on a tangent. Too much positive energy about
coming close to solving this I guess. Sorry again :)
cr
 
Upvote 0
Here is code that works using double quotes as the markers, text in inkTest comes from C2 on sheet as per first sub:
VBA Code:
Private Sub UserForm_Activate()
inkText.Text = Sheets("MATT24").Range("C2")

End Sub
I put a command button on the form to trigger the code:
VBA Code:
Private Sub cmdColourText_Click()
Dim intCount As Integer, n As Integer
Dim lngPos1 As Long, lngPos2 As Long

strIn = Sheets("MATT24").Range("C2")
''strIn = Me.inkText 'n/w
intCount = Len(strIn) - Len(Replace(strIn, """", ""))
lngPos1 = 1

With Me.inkText
    .SelLength = Len(inkText)
    .SelColor = vbBlue
    .SelLength = 0
End With

For n = 1 To intCount Step 2
    lngPos1 = InStr(lngPos1, strIn, """")
    lngPos2 = InStr(lngPos1 + 1, strIn, """") - 1
    With Me.inkText
        .SelStart = lngPos1
        .SelLength = lngPos2 - lngPos1
        .SelColor = vbRed
        .SelLength = 0
    End With
    lngPos1 = lngPos2 + 2
Next

End Sub
It does not work if I refer to the text showing in the inkEdit control after moving through listbox items, probably because your code adds line wrap characters when the > button is clicked. If you must have them, then you'll have to figure out how to incorporate them in the loop counter as they may or may not be there. Personally I would ditch the idea of manually wrapping text as I think I mentioned before.

Maybe you are aware of some of the following, which I point out, not to be critical but to let you know there are potential issues:

I like to check my code to see if it compiles. To do that I had to comment a LOT of your code. In some cases the object didn't exist, in some it was about missing code elements (like End Sub). There must have been 75 errors, no lie.

Lots of quotations are missing leading or trailing quotes or both. I also saw a double quote after a citation (calling it that because I don't know what NASB is) but I can't find it now. Any issues like that are going to cause the code to not perform as expected. Other verses have trailing characters (.eg. ")" ) but that wouldn't affect the output.

Look at C2 and D5 quotes - they are not the same (C2 is ASCII 34, D5 is ASCII 147). C2 is what I coded for and it works as I showed, but I typed directly in the vb editor so I don't know if I got lucky with a match or not. I expect smart quotes (D5 et al) will cause the code to fail.

I think you've still got a lot of work ahead of you and that this is about as far as I can go with you. Good luck with your project.

EDIT - IMO, most developers advocate that every module start with Option Explicit by default (this is an editor option). Some go so far as to say if you don't have that, you deserve all the grief that you get. You could does not have it.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top