VBA to Capitalise non-sequential columns in a table

LotsOfQuestions88

New Member
Joined
Sep 2, 2016
Messages
17
Hi and thanks in advance for any help with this! I've got a problem which I'm sure is fairly basic to solve but I can't seem to find the solution.I have a table (Table1) which has various columns. I'd like to use VBA to capitalise 3 of the columns (Surname, Gender and Priority) which are not adjacent and convert one column to Proper case (Name). This could be done either with a button to trigger the macro or as a worksheet change triggered event (as I'm currently trying to achieve). Currently, I have this in the Sheet1 code window:

Code:
Private Sub Worksheet_change(ByVal target as Range)
[/FONT][/COLOR][COLOR=#000000][FONT='inherit']Application.ScreenUpdating = False
[/FONT][/COLOR][COLOR=#000000][FONT='inherit']Call Match_Tables_to_Main(target)[/FONT][/COLOR][COLOR=#000000][FONT='inherit']
Call Capitalise_Surnames(target)[/FONT][/COLOR][COLOR=#000000][FONT='inherit']
Application.ScreenUpdating = False
[/FONT][/COLOR][COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]

The Match_table_to_main sub matches some other tables in the workbook to the number of rows in Table1 and the Capitalise_Surnames sub has the following code (also held in the Sheet 1 code window):

Code:
[COLOR=#000000][FONT='inherit']Private Sub  Capitalise_Surnames(ByVal target as range)
[/FONT][/COLOR][COLOR=#000000][FONT='inherit']Sheet1.ListObjects("Table1").ListColumns("Surname").DataBodyRange.Value = [INDEX(Upper(Table1[Surname]),)]
[/FONT][/COLOR][COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]

This seems to work fine. However, I'd like the Capitalisation applied to the other columns as detailed above (ie Gender and Priority) and Proper text for the Name column. I've tried putting, for example:

Code:
[COLOR=#000000][FONT='inherit']Sheet1.ListObjects("Table1").ListColumns("Name").DataBodyRange.Value = [INDEX(Upper(Table1[Name]),)][/FONT][/COLOR]

in the same Capitalise_Surnames sub below the Surname code but nothing happens. I've also tried writing separate subs for each of the columns and calling them with the main worksheet change sub but again only the first of the 'case-changing' subs seems to work (ie they work if called individually but only one works when trying to run them together). I'm a bit stuck with this and would really appreciate some help!!Thanks!
 
Last edited:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
I'm about to go to sleep, but I worked this up kind of quickly. See if you can incorporate the idea here into your code...
Code:
[table="width: 500"]
[tr]
	[td]Sub Test()
  Dim ColName As Variant
  With Sheet1.ListObjects("Table1")
    For Each ColName In Array("Surname", "Gender", "Priority")
      .ListColumns(ColName).DataBodyRange.Value = Evaluate("IF({1},UPPER(Table1[" & ColName & "]))")
    Next
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:

LotsOfQuestions88

New Member
Joined
Sep 2, 2016
Messages
17

ADVERTISEMENT

Hi - I've tried running it in the Sheet module and calling the sub and also in a separate module and triggering it with a button but either way it only seems to capitalise the first of the columns named in the code (ie "Surname"). I've tried changing the 3 columns to sequential columns but that made no difference. Do you have any other suggestions at all? No rush and thanks again in advance.
BW
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
Hi - I've tried running it in the Sheet module and calling the sub and also in a separate module and triggering it with a button but either way it only seems to capitalise the first of the columns named in the code (ie "Surname"). I've tried changing the 3 columns to sequential columns but that made no difference. Do you have any other suggestions at all? No rush and thanks again in advance.
The code works for me... the three named columns are capitalized. The only way I can think of that my code would fail is if the column names had a trailing space (or leading) space so their spelling was different than the names specified in the Array function call; however, if that was the case, I would expect the code to error out. Do you by any chance have an On Error Resume Next trap running from within the code you are calling my code from (that would hide the error message from you and then the code would look like it was only running one column and ending)?
 

LotsOfQuestions88

New Member
Joined
Sep 2, 2016
Messages
17
Hi - I see. No, I don't have an error routine running so it's not that. I have other code on the Sheet1 page though - here it is in case it makes it clearer what the problem is.... The name of the columns has changed slightly but refer to the same columns within the table.

Code:
Private Sub Worksheet_change(ByVal Target As Range)
 

 
Call Match_Tables_to_Main(Target)
Call Capitaliser2
 
 
 
End Sub
 
 
Private Sub Match_Tables_to_Main(ByVal Target As Range)
 
' Matches  table row number from Main to Review
 
 
Dim rng1 As Integer
 
    rng1 = Sheet3.Range("C2").Value
 
Dim rng2 As Integer
 
    rng2 = Sheet3.Range("H2").Value
   
Dim rng3 As Integer
 
    rng3 = Sheet3.Range("M2").Value
   
Dim Tbl28 As ListObject
Set Tbl28 = Sheet2.ListObjects("Table28")
 
Dim Tbl1 As ListObject
Set Tbl1 = Sheet1.ListObjects("Table1")
 
Dim Tbl39 As ListObject
Set Tbl39 = Sheet10.ListObjects("Table39")
 
 
If rng1 <> rng2 Then
   
    Tbl28.Resize Tbl28.Range.Resize(Tbl1.Range.Rows.Count)
   
    Else: End
   
End If
   
If rng1 <> rng3 Then
 
    Tbl39.Resize Tbl39.Range.Resize(Tbl1.Range.Rows.Count)
   
    Else: End
 
End If
 
End Sub
 
 
Sub Capitaliser2()
  Dim ColName As Variant
  With Sheet1.ListObjects("Table1")
    For Each ColName In Array("Patient surname", "Sex", "Priority call?")
      .ListColumns(ColName).DataBodyRange.Value = Evaluate("IF({1},UPPER(Table1[" & ColName & "]))")
    Next
  End With
End Sub
 
 
 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'Return active row or column in named cells.
 
[SelectedRow1] = ActiveCell.Row
[SelectedColumn1] = ActiveCell.Column
 
End Sub

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top