Format Name in Cells

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Format Name in Cells

Post by arroway »

We have a column for Name. Entries are entered LastName, FirstName. Sometimes people enter ALL CAPS and we need it formated so Cap is first letter of FirstName and LastName and lowercase for everything else. I was doing it by hand but now more and more entries are coming in and it's getting to be too much of a job. Is there a formatting condition or function or something like that which will format entries in this column automatically?

Thank you,
Dax
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

You could run a macro. Select the cells before running it.

Code: Select all

Sub ProperCase()
    Dim cel As Range
    Application.ScreenUpdating = False
    For Each cel In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
        cel.Value = StrConv(cel.Value, vbProperCase)
    Next cel
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

:bananas: :bananas: :bananas: Sweetness!! Thank you! :bananas: :bananas: :bananas:
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Oooo. One thing I notice is it reduces the case for hyphonated names. So Smith-Holly becomes Smith-holly. Can that part be fixed?
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Format Name in Cells

Post by Rudi »

In addition to Hans's macro, you could also use the Excel function called PROPER.
Assume the names are in column A, starting at A2
In B2, enter the formula: =PROPER(A2) and autofill down.

The formula should sort out the hyphenated issue too...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

That's much more complicated. I'll get back to you later.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Format Name in Cells

Post by Rudi »

Am I overlooking something? (Which I tend to do...so pardon me if I have)...

If you do need an automated version and not the function I referred to, you can update Hans's macro to use the 'PROPER' worksheet function.

Code: Select all

Sub ProperCase()
    Dim cel As Range
    Application.ScreenUpdating = False
    For Each cel In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
        cel.Value = Application.Proper(cel.Value)
    Next cel
    Application.ScreenUpdating = True
End Sub
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Hi Rudi,
Thanks for the info. Your solution is preferred as it would make the process automatic. I've inserted a column into my worksheet for the proper-ed names and updated the VBA in my Word document which extracts data and enters it into this spreadsheet but when I do this, it overwrites the =PROPER(<cell>) function and just enters a "0".

Here's a snippet from my Word's VBA:

Code: Select all

StrWkBkNm = "H:\my file location\Numbers.xlsm" 'Excel spreadsheet location
StrBkMk = "bMedicaidNum,bLname,bFname,,bDOB,VOL,bIPHospital,Inits,ciCallDate,,dInitDaysAuthFrom,dInitDaysAuthThru,,,,,bTxAgencyName"
Sep = "|" ' Data Separator Character
StrXlPwd = "MyPassword" '"Password" 'Password for the Excel file

With ActiveDocument
        For i = 0 To UBound(Split(StrBkMk, ","))
    If i = 1 Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & ", "
    ElseIf i = 2 Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
    ElseIf i = 5 Then
      StrData = StrData & "VOL" & Sep
      ElseIf i = 6 Then
        If .FormFields(Split(StrBkMk, ",")(i)).Result = "Other" Then
            StrData = StrData & .FormFields("bIPHospOther").Result & Sep
        Else
            StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
        End If
    ElseIf i = 9 Then
      StrData = StrData & Sep
    ElseIf i = 12 Then
      StrData = StrData & Sep
    ElseIf i = 13 Then
      StrData = StrData & Sep
    ElseIf i = 14 Then
      StrData = StrData & Sep
    ElseIf i = 15 Then
      StrData = StrData & Sep
    ElseIf i = 7 Then
      While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
        StrIn = InputBox("Please add your initials.  (In CAPS please.)", "User Initials")
      Wend
      StrData = StrData & StrIn & Sep
    ElseIf i = UBound(Split(StrBkMk, ",")) Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result
    Else
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
    End If
  Next
End With
i=4 is the one that overwrites. Do you know how I could update this code so it leaves the function in the cell, untouched?
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Actually, come to think of it. Maybe the code could be updated to include the "PROPER" format instruction. So the bLname and bFname parts could look something like:

Code: Select all

If i = 1 Then
StrData = StrData & .FormFields (Format.PROPER (Split (StrBkMd, ",") (i))) .Result & ", "
If i = 2 Then
StrData = StrData & .FormFields (Format.PROPER (Split (StrBkMd, ",") (i))) .Result & Sep
...but I'm sure that's probably not quite right. But maybe you see where I'm going with it. Possible?
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Format Name in Cells

Post by Rudi »

Hi Dax,

My Word VBA is not up to par to assist you. You will need to wait for someone else to assist with VBA skills in Word.
AFAIK, you cannot use the PROPER function in Word VBA as it is not part of Words object library. The PROPER function belongs to the Application.WorksheetFormula (which is part of Excel). This is obviously the part I missed and in Hans's foresight, why he did not go into using the function in the first place.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

Sorry, you've lost me completely. What are you trying to do?
Best wishes,
Hans

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Sorry about that. I thought it was simpler at first but then I realized it got quite complicated really fast. Let's try the whole explaination.

I have a Word document locked form with named formfields. In it there's a Macro which collects the data from the fields and enters them into a spreadsheet in an Excel spreadsheet. (Hans, you've helped me with most of this.) When someone fills out the form sometimes, they don't use proper case and sometimes use all caps or no caps. What I need in the spreadsheet are properly cased names.

My first question was, is there a way to update the excel spreadsheet, which Hans you provided the Macro for but I'd have to run it and it didn't do the names with Hyphens the correct way. Rudi offered another suggestion which I really like as it is automatic and takes all the work out of it for me. However, following Rudi's advice to insert another column to grab the adjascent collumn's data (the names) and format them using the =PROPER() function, when the Macro from the Word document is run, it overwrites the =PROPER() function and enters a 0.

I'm trying to get it to work without overwritting the =PROPER() function. I'd thought that possibly updating the Word's Macro might be a place to do this so that's where the code and tangential thinking came from.

Does that make sense?
Please let me know if I need to explain any part further. And thanks millions for your time!
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Format Name in Cells

Post by Rudi »

Did you try running the modified code I posted (using Hans's original macro) in Excel. This will simply update the data dumped into Excel via the import. Run it from Excel after the data is dumped on the sheet.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Yes, actually that does work. And it doesn't have issues with the hyphenated names. Which is exactly what I was looking for, however, you showed me that this could actually be done without me needing to select my cells and run the Macro. I didn't know about the =PROPER() function and now that looks a LOT more attractive! (I love the idea of not having to do anything! :evilgrin: )

So that's why I'm still going on this. It seems that the "added colum for the =PROPER() function" works great if I can just get the 'data dump' to ignore that column and not overwrite the function with a Zero.
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

You haven't shown us the code that writes data to the worksheet, so there is no way for us to know why the formulas are overwritten.
Best wishes,
Hans

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Yes I did. That's the code I inserted earlier. Maybe I didn't explain it well enough or include enough of it. I basically included the part of it that I thought would do what we're talking about. Here's the whole thing:

Code: Select all

Sub IPLogInsert()
Dim StrBkMk As String, StrData As String, StrIn As String, Sep As String, i As Long
Dim xlApp As Object, xlWkBk As Object, xlWsh As Object, StrWkBkNm As String
Dim bStrt As Boolean, r As Long, iDataRow As Long, bFound As Boolean, StrXlPwd As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem

StrWkBkNm = "H:\My File Location\Numbers.xlsm" 'Excel spreadsheet location
StrBkMk = "bMedicaidNum,bLname,bFname,,bDOB,VOL,bIPHospital,Inits,ciCallDate,,dInitDaysAuthFrom,dInitDaysAuthThru,,,,,bTxAgencyName"
Sep = "|" ' Data Separator Character
StrXlPwd = "MyPassword" '"Password" 'Password for the Excel file

With ActiveDocument
        For i = 0 To UBound(Split(StrBkMk, ","))
    If i = 1 Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & ", "
    ElseIf i = 2 Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
    ElseIf i = 5 Then
      StrData = StrData & "VOL" & Sep
      ElseIf i = 6 Then
        If .FormFields(Split(StrBkMk, ",")(i)).Result = "Other" Then
            StrData = StrData & .FormFields("bIPHospOther").Result & Sep
        Else
            StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
        End If
    ElseIf i = 9 Then
      StrData = StrData & Sep
    ElseIf i = 12 Then
      StrData = StrData & Sep
    ElseIf i = 13 Then
      StrData = StrData & Sep
    ElseIf i = 14 Then
      StrData = StrData & Sep
    ElseIf i = 15 Then
      StrData = StrData & Sep
    ElseIf i = 7 Then
      While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
        StrIn = InputBox("Please add your initials.  (In CAPS please.)", "User Initials")
      Wend
      StrData = StrData & StrIn & Sep
    ElseIf i = UBound(Split(StrBkMk, ",")) Then
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result
    Else
      StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
    End If
  Next
End With
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next
bStrt = False ' Flag to say whether we've started Excel, so we can close it later
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
  Set xlApp = CreateObject("Excel.Application")
  If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  Else
  End If
  bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open
With xlApp
  bFound = False
  For Each xlWkBk In .Workbooks
    If xlWkBk.FullName = StrWkBkNm Then ' It's open
      Set xlWkBk = xlWkBk
      bFound = True
      Exit For
    End If
  Next
  ' Not open, so open it
  If bFound = False Then
    If IsFileLocked(StrWkBkNm) = True Then
      MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
      Exit Sub
    End If
    Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, Password:=StrXlPwd)
    If xlWkBk Is Nothing Then
      MsgBox "Cannot open:" & vbCr & StrWkBkNm
      Exit Sub
    End If
  End If
  ' Update the workbook
  With xlWkBk
    Set xlWsh = .Worksheets(1)
    ' Identify the worksheet to update
      If ActiveDocument.FormFields("bEnT").CheckBox.Value Then
      Set xlWsh = .Worksheets("EnT_2014")
    Else
      Set xlWsh = .Worksheets("2014")
    End If
    ' Update the first available row in the worksheet,
    ' skipping over any columns for which there are no data
    With xlWsh
      iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row + 1 ' -4162 = xlUp
      For i = 0 To UBound(Split(StrData, Sep))
        If Trim(Split(StrData, Sep)(i)) <> vbNullString Then
          .Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
        End If
      Next
    End With
  End With
  MsgBox "Workbook updates finished.", vbOKOnly
  xlApp.Visible = True
  AppActivate "Microsoft Excel"
End With


'Email Function to send email

On Error Resume Next

'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
    'Outlook wasn't running, start it from code
    Set oOutlookApp = CreateObject("Outlook.Application")
     bStarted = True
End If

'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
     'Set the recipient for the new email
   .To = "person1@myplace.com"
     'Set the subject
    .Subject = "NOTIFICATION: IP Log Submital"
     'The content of the document is used as the body for the email
    .Body = "Someone hit the Submit Button!"
     .Send
End With

If bStarted Then
     'If we started Outlook from code, then close it
    oOutlookApp.Quit
End If

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub
Does that help? If I'm reading the code right, the data collection gathers the form field data and then gives it incremental numbers. So i=1=bMedicaidnum, i=2=bLname, i=3=bFname, and i=4 is a blank one. These corolate with the columns on the Excel worksheet. So Column 1=Medicaid Number, 2=Lastname, Firstname (2 items combined), and 4 is where the =PROPER() function lives.
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

In your earlier post, you omitted the part that starts at

' Test to see if there is a copy of Microsoft Excel already running.

i.e. you left out all the Excel code. The first part doesn't have anything to do with Excel. The code that you have now posted writes a row of values to the first available (empty) row in the Excel worksheet. It's not clear where the formula should go.

Do you want ALL cells to be formatted as proper case? If so, you can change the line

Code: Select all

          .Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
(which is the line that actually writes data to a cell) to

Code: Select all

          .Cells(iDataRow, i + 1).Value = Application.Proper(Application.Trim(Split(StrData, Sep)(i)))
If you want to format only one column as proper case, please indicate which column that should be.
Best wishes,
Hans

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

Not all the cells, only the name column, which would be column #2.
It takes 2 to tango; unless you speak binary; then it takes 10.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Format Name in Cells

Post by HansV »

In that case, change the line

Code: Select all

          .Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
to

Code: Select all

          If i = 1 Then ' This will become column 2, so apply Proper
              .Cells(iDataRow, i + 1).Value = Application.Proper(Trim(Split(StrData, Sep)(i)))
          Else ' Otherwise simply add the value
              .Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
          End If
Best wishes,
Hans

User avatar
arroway
3StarLounger
Posts: 368
Joined: 04 Jan 2012, 22:43

Re: Format Name in Cells

Post by arroway »

It throws a Compile error: Method or data member not found.
When I hit OK, the ".Proper" characters of the code are highlighted. :scratch:
It takes 2 to tango; unless you speak binary; then it takes 10.