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:
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):
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:
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!
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: