format initials

wCJanssen

New Member
Joined
Feb 22, 2009
Messages
24
Hi,

I'm looking for a macro that inserts a dot after each character in a userform textbox, in order to format initials. Thanks in advance.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
This will put you on the right track:

Code:
Function withdots(sText As String) As String

    For i = 1 To Len(sText)
        withdots = withdots & Mid(sText, i, 1) & IIf(Mid(sText, i, 1) <> " ", ".", "")
    Next

End Function

Sub f()

    MsgBox withdots("mister excel")

End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,895
Office Version
365, 2010
Platform
Windows
You should be able to do it with the TextBox Change event:

Code:
Private Sub TextBox1_Change()
Application.EnableEvents = False
    If Right(TextBox1, 1) <> "." Then TextBox1 = TextBox1 & "."
Application.EnableEvents = True
End Sub
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
Hello Hotpepper,

Application.EnableEvents does not work on userforms, only in worksheets.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,895
Office Version
365, 2010
Platform
Windows
Thanks, I was not aware of that, but it can also be used in Workbook code.

At any rate, you can use the TextBox Change event to insert the periods.

Code:
Private Sub TextBox1_Change()
If Right(TextBox1, 1) <> "." Then TextBox1 = TextBox1 & "."
End Sub
 
Last edited:

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
The only thing that'd annoy me, is that backspace does not work since it generates also a change event... Selecting letters and deleting is possible, though.

Better to use the KeyDown event?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,895
Office Version
365, 2010
Platform
Windows
KeyUp:

Code:
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode > 64 And KeyCode < 90 Then TextBox1 = UCase(TextBox1) & "."
End Sub
 

Forum statistics

Threads
1,085,031
Messages
5,381,303
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top