Add function to AfterUpdate

MissaLissa

New Member
Joined
Jul 2, 2013
Messages
26
Hi Guru's

I'm using vbProperCase in a sub AfterUpdate. However, the vbProperCase does not take into consideration last names that have a hyphen or apostrophe. So, I found a function to solve the issue, but I'm not sure how to implement it. Can someone please walk me through this? Much appreciated.

current code:
Code:
Private Sub Applicant2_Last_Name_AfterUpdate()    
    Me.Applicant2_Last_Name = StrConv(Me.Applicant2_Last_Name, vbProperCase)
End Sub

Need to add the following function:
Code:
Public Function fProperCase(ByVal vName As String)   
   Dim vReturn
   Dim vLeft
   Dim vRight
   Dim lHyphen As Long
   Dim lApostrophe As Long
   
   
   vReturn = Null
   lHyphen = Nz(InStr(1, vName, "-", vbBinaryCompare), 0)
   lApostrophe = Nz(InStr(1, vName, "'", vbBinaryCompare), 0)


   
   If Len(vName) Then
   
      If lHyphen Then
         vLeft = Mid(vName, 1, lHyphen - 1)
         vRight = Mid(vName, lHyphen + 1)
         vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "-" & StrConv(vRight, Conversion:=vbProperCase)
                         
      Else
         If lApostrophe Then
            vLeft = Mid(vName, 1, lApostrophe - 1)
            vRight = Mid(vName, lApostrophe + 1)
            vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "'" & StrConv(vRight, Conversion:=vbProperCase)
         Else
            vReturn = StrConv(vName, Conversion:=vbProperCase)
         End If
      End If
     
    End If
    fProperCase = vReturn
End Function
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,479
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try calling the function like this:

Code:
Private Sub Applicant2_Last_Name_AfterUpdate()
    Me.Applicant2_Last_Name.Text = fProperCase(Applicant2_Last_Name.Text)
End Sub

Dave
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
The function can go in the same place as the form code, or more generally you might prefer to put it into a module so that it can be used in any form.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,361
Members
415,969
Latest member
Rey99

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
Top