How to Subscript text in a RichText Box

[scidb]

Hello All,

I need help with a RichTextBox Control. I have an MS Access report that has a RichTextBox control. The control gets its data from a field in the database that contains chemical formulas, e.g. H2O. What I need to do is search the RichTextBox for any numbers and make them subscripts, in H2O the "2" would be subsripted.
I have been doing some searching for VB code samples to help me do this. If I understand correctly I would need to programatically select the numbers and use the SelectCharOffset Property. Does anyone have any experience with this, I am a complete newbie to VB.

Any help would be greatly appreciated! Thanks

 

[johnwm]

A quick search gives: thread222-986405 which will get you started. If you need more, post what you have and let us know what you need

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[scidb]

Thanks johnwm!

I will give it a try and see what I come up with! Thanks very much for the quick response.

 

[scidb]

Hi johnwm,

My first sticking point is how to select only numbers in the string? This is complicated a bit as I hope the following example will show. Given the [MF]= CuS04·2H2O, I only want the code to subscript numbers that DON'T appear after a "·".

In this example then, after subscripting, (red) it would look like CuSO4·2H2O

Not sure if I need to reduce font size of subscripted number, they are usually about 2/3 the size of the normal font.

Finally how do I call this code? In the Access report I have a linked image that requires code. The code is activated using the ON FORMAT event of the report. Would this be where I would call this subscripting code?

Here is my meager offering of code:

Function Subscript()

rtb1.Text = [MF] 'This is the field name for the string
rtb1.SelStart = 0 'start at first character in string?
rtb1.??????????? 'select only numbers not preceded with a dot
rtb1.SelCharOffset = -10

End Function

As you can see, any help would be greatly appreciated.

 

[strongm]

Option Explicit

Private Sub Command1_Click()
Dim re As RegExp
Dim mymatches As MatchCollection
Dim mymatch As Match

RichTextBox1.Text = Text1.Text
Set re = New RegExp

re.Global = True

re.Pattern = "\w\d"

Set mymatches = re.Execute(RichTextBox1.Text)
For Each mymatch In mymatches
With RichTextBox1
.SelStart = mymatch.FirstIndex + 1
.SelLength = 1
.SelCharOffset = -20
.SelFontSize = .SelFontSize * 2 / 3
End With
Next
End Sub

 

[scidb]

strongm,

Thanks for the code. I have placed it in my MS Access Report. How is the code activated? Does it run automatically when the report is opened? Do I need to add it to the ON FORMAT as an event procedure?

Sorry for the questions, it is all new to me.

 

[Sheco]

The problem is that your copper sulfate is only partially hydrated. It has only 2 water and it should have 5 to make the pentahydrate. The formula should be: CuS04·[red]5[/red]H2O

 

[Sheco]

Oh, and the example code that strongm gave is for a button_click... so make command button 1 so you can test it.

If you like what it does then move it out of the button click event and into your load event.

 

[scidb]

Hi strongm,

I am getting some errors:

User-defined type not defined for New RegExp

removed options explicit, helped some, but still some undefined variables? Suggestions?

Thanks for your time.

P.S. Sheco...you must be a chemist or a good student!
Pentahydrate it is.

 

[strongm]

Sorry. Should have said that you will need to add a reference in your project to the Microsoft VBScript Regular Expressions 5.5 library (under Tools/References). And put that Option Explicit back in ;-) ...

 

[scidb]

Hi strongm,

I have added the reference library and added back in 'Option Explicit' and there are no longer undefined variables.

How do I now get this to run? In a MS Access report, there are no buttons. I have this RTB in the detail section of the report and I have an On Format, On Print and On Retreat Events.

In the On Format event I have a small routine running that brings in a linked gif image to the report and that works well; the syntax is =[setImagePath]. When I try to use the same sytax for your code, I get an error message that basically says it can't find the my code to run. Any suggestions?

Thanks for your patience!

 

[Sheco]

Can't you put it in the Report_Open() event?

Like if you right-click on your report and choose Build Event, then go to the code builder where it brings up the VB interface?

From the VB page, choose "Report" from the top left dropdown box and then you should be able to pick the Open event from the top right dropdown box.

 

[scidb]

Hi Sheco, strongm,

Current code is:
Private Sub Report_Open()
Dim re As RegExp
Dim mymatches As MatchCollection
Dim mymatch As Match

richTextBox1.Text = Text1.Text
Set re = New RegExp

re.Global = True

re.Pattern = "\w\d"

Set mymatches = re.Execute(richTextBox1.Text)
For Each mymatch In mymatches
With richTextBox1
.SelStart = mymatch.FirstIndex + 1
.SelLength = 1
.SelCharOffset = -20
.SelFontSize = .SelFontSize * 2 / 3
End With
Next
End Sub

I have put in "=Report_Open()" in the On Open event and I am getting this error message:

The expression On Open you entered as the event property setting produced the following error:
Procedure declaration does not match description of event or procedure having the same name.

Any suggestions?
Thanks Again!

 

[Sheco]

What do you mean by this:
"I have put in "=Report_Open()" in the On Open event"

Doesn't this event fire automatically when you open the report?

 

[scidb]

Sheco,

I thought I had to tell the MS Access Report to run this code explicitly. In the Properties of the MS Access Report there is an Event tab. One of the events is "On Open" and I have set that equal to my function name which is Report_Open().

I have tried to open the report without this "On Open" event set and it doesn't fire off automatically.

 

[Sheco]

What version of Access do you have?

I have version 2000 and I just made a new database and added a new report... Right-click.. Build Event ...Code-Builder then did this:

Private Sub Report_Open(Cancel As Integer)
    MsgBox "hi"
End Sub

Saved it. Closed the code builder. Closed design mode. Double-clicked the report... and I get my msgbox.

It runs automatically just like a button's _Click() event.

 

[scidb]

Sheco,

I am not sure what is going on. I got your code to work like a charm in the same database I have the report! I have Access 2002, but not sure if that is the problem!

 

[Sheco]

So in your database the Report_Open() event does not fire automatically when you open the report?

 

[scidb]

the Report_Open() event did work automatically when I used the bit of code you generated, but when I use the code for the subscripting it doesn't.

 

[Sheco]

Aha! I think I know.

Procedure declaration does not match description of event or procedure having the same name.

This means that the declaration for your event does not look like as expected by Access.

If the one that I gave you did work, then just change the code so the declaration is like mine. Like this:

Private Sub Report_Open([red]Cancel As Integer[/red])
    Dim re As RegExp
    Dim mymatches As MatchCollection
    Dim mymatch As Match
    
    richTextBox1.Text = Text1.Text
    Set re = New RegExp
    
    re.Global = True
    
    re.Pattern = "\w\d"
    
    Set mymatches = re.Execute(richTextBox1.Text)
    For Each mymatch In mymatches
        With richTextBox1
        .SelStart = mymatch.FirstIndex + 1
        .SelLength = 1
        .SelCharOffset = -20
        .SelFontSize = .SelFontSize * 2 / 3
        End With
    Next
End Sub

Dang I hope this is it!