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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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:
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top