CODEWITHSUNDEEP
ms-accessfontsvba
abalter
abalter

Reputation: 10383

Access VBA programmatically setting font/size not working

I have a button that is supposed to change the font face and size of a textbox to Tahoma 8pt. The button event is:

Private Sub btnSetFont_Click()
    MsgBox ("Setting Inventory Description to Tahoma 8pt")
    Me.InventoryDescription.FontSize = 8
    Me.InventoryDescription.FontName = "Tahoma"
End Sub

Unfortunately, the text does not change. I'm testing it by first editing the font and size by hand, and then pressing my button.

However, if I do the following, 

Private Sub btnSetFont_Click()
    MsgBox ("Setting Inventory Description to Tahoma 8pt")
    Me.InventoryDescription.Value = "hello"
    Me.InventoryDescription.FontSize = 24
    Me.InventoryDescription.FontName = "Times"
End Sub

The text changes to "hello" of course, but the font and size do indeed change. (I used Times 24pt because the default for the textbox is Tahoma 8pt and I wanted to make sure it wasn't just reverting to the default) This made me think that the textbox needs to have the focus to make the changes. So, I tried:

Private Sub btnSetFont_Click()
    MsgBox ("Setting Inventory Description to Tahoma 8pt")
    Me.InventoryDescription.SetFocus
    Me.InventoryDescription.FontSize = 24
    Me.InventoryDescription.FontName = "Times"
End Sub

But, no go.

Soooo, what am I doing wrong?

I found one aspect to the problem. The text box .TextFormat is set to Rich Text. If I change it to Plain Text, then the button effect works. However, the reason it is set to Rich Text is to allow italics. So, I tried first setting it to plain text, and then changing the font/size, but that didn't work either.

Upvotes: 1

Views: 6452

Answers (4)

hermeslm
hermeslm

Reputation: 1990

I started using the @yves solution but it fails when a "font" tag includes a "size" property, for example:

<font face="Arial" size="5">a colour in it will break?</font>

So I found a better way using #RegExp, you can follow the thread in this forum: accessforums.net

Public Function CleanRichTextRegEx(ByVal strText As String, _
                               ByVal strFont As String, _
                               ByVal nSize As Integer) As String
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBScript.RegExp")
    On Error Resume Next
    With objRegEx
       .Global = True
       'Replace font size
       .Pattern = "size=[0-9]"
       strText = .Replace(strText, " size=" & nSize)
       'Replace font face
       .Pattern = "face=([""'])(?:[\r\n]*(?=(\\?))\2.)*?\1"
       strText = .Replace(strText, "face=" & strFont)
    End With
    Set objRegEx = Nothing
    CleanRichTextRegEx = strText
End Function

And you can use it in this way:

richText = "<font face='Arial' size='5'>a colour in it will break?</font>" 
richTextResult = CleanRichTextRegEx(richText, "Arial", 2)

Upvotes: 0

Yves
Yves

Reputation: 11

I have the same need: I want the users to be able to format the text with boldface, italics and underlined characters, but I don't want to allow font name changes, or font size changes. And copy/paste actions often import formatted text in my textBox, which needs to be "cleaned".

The solution I found is in the Function below. This function should be called by an Event Procedure (i.e. After Update, or On Click).

Public Function CleanRichText(strTEXT, strFont, nSize)
'*****************************************************
' 

    For i = 1 To 9
        strTEXT = Replace(strTEXT, "size=" & i, "size=" & nSize)
    Next i

    strTEXT = Replace(strTEXT, "font face", "font_face")
    strTEXT = Replace(strTEXT, "font" & Chr(13) & Chr(10) & "face", "font_face")

    Do While InStr(1, strTEXT, "font_face=" & Chr(34)) > 0
        iCut1 = InStr(1, strTEXT, "font_face=" & Chr(34))
        iCut2 = InStr(iCut1 + 12, strTEXT, Chr(34))
        strLeft = Left(strTEXT, iCut1 - 1) & "font_face=Face"
        strRight = Right(strTEXT, Len(strTEXT) - iCut2)
        strTEXT = strLeft & strRight
    Loop

    Do While InStr(1, strTEXT, "font_face=") > 0
        iCut1 = InStr(1, strTEXT, "font_face=")
        iCut2 = InStr(iCut1 + 12, strTEXT, Chr(32))
        strLeft = Left(strTEXT, iCut1 - 1) & "font face=" & strFont & Chr(32)
        strRight = Right(strTEXT, Len(strTEXT) - iCut2)
        strTEXT = strLeft & strRight
    Loop
    CleanRichText = strTEXT

End Function

Private Sub Cause_AfterUpdate()

    Me.Cause = CleanRichText(Me.Cause, Me.Cause.FontName, 2)
End Sub

Upvotes: 1

jamesTheProgrammer
jamesTheProgrammer

Reputation: 1777

Your code worked for me using Access 2010.

I added this code to the click event for a couple command buttons that allow the user to dynamically control the font size in a List box called teachersList.

Private Sub cmdDecreaseFont_Click()
     Me.teachersList.FontSize = Me.teachersList.FontSize - 1
End Sub

Private Sub cmdIncreaseFont_Click()
    Me.teachersList.FontSize = Me.teachersList.FontSize + 1
End Sub

Then added a couple simple command buttons to the form enter image description here

Upvotes: 0

Tom Collins
Tom Collins

Reputation: 4069

Your code works for me. Try adding Me.Repaint after changing the font.

Upvotes: 0

Related Questions