Combining Worksheet change event and change case macros into one

Dave T

Board Regular
Joined
Jun 21, 2005
Messages
93
Hello All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
I use the following two macros in a workbook that I would like to combine them into a single Worksheet Change event macro and am not sure how to go about this, so any help would be very much appreciated.
<o:p> </o:p>
The first macro is installed in the worksheet and the second is installed in a module.
<o:p> </o:p>
Sheet 1 Code<o:p></o:p>
Code:
[/SIZE][/FONT]
[SIZE=3][FONT=Arial]Option Explicit<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Dim myRng As Range<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Set myRng = Me.Range("C3:C200")<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]   <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    If Intersect(myRng, Target.Cells) Is Nothing Then<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]        Exit Sub<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    End If<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Me.Parent.Save<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    <o:p></o:p>[/FONT][/SIZE]
[FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Arial][SIZE=3]
<o:p> </o:p>
Module Code<o:p></o:p>
Code:
[/SIZE][/FONT]
[SIZE=3][FONT=Arial]Sub ConvertToUppercase()<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]    Dim ws As Object<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Dim LCell As Range<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]    'Turn off screen updating to increase performance<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Application.ScreenUpdating = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Application.Calculation = xlCalculationManual<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]    'Move through each sheet in your spreadsheet<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    For Each ws In ActiveWorkbook.Sheets<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]        On Error Resume Next<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]        ws.Activate<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]        'Convert all constants and text values to upper case<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]        For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]            LCell.Formula = UCase(LCell.Formula)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]        Next<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Next ws<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Arial]    'Turn screen updating back on<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Application.Calculation = xlCalculationAutomatic<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    Application.ScreenUpdating = True<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Arial][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Arial][SIZE=3]
<o:p> </o:p>
Thanking you in advance.
<o:p> </o:p>
Regards,
Dave T
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
If you want to force the target cell entries to be converted to upper case no matter how they're entered, just add:

Target.Value = UCase(Target.Value)

HTH,
 

Dave T

Board Regular
Joined
Jun 21, 2005
Messages
93
Hello Smitty,

Thanks for the quick reply.

For some reason my employer insists that all text in the worksheet must be in upper case.
Currently I type text into column B (photo description) and then the photo number in column C. Using the caps lock key on the key board is frustrating as I forget to turn it on and when I have finished I forget to turn it off and then all other applications are upper case.

Being a VBA novice I am unsure as to where to place the code you have suggested (I have realised I could change the event code from column C to column B, so what you have suggested would work fine).

I am curious how one would go about adding some code the Worksheet Change event that would call another macro i.e the change case macro in the module.
If this is possible can you also please let me know exactly where this code should be paced within the worksheet code.

Thanks again for your help.

Regards,
Dave T
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Give this code a shot:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> myRng = Target.Parent.Range("B:C")<br>   <br>        <SPAN style="color:#00007F">If</SPAN> Intersect(myRng, Target.Cells) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column<br>            <SPAN style="color:#00007F">Case</SPAN> 2  <SPAN style="color:#007F00">'   Column B</SPAN><br>                Target.Value = UCase(Target.Value)<br>            Case 3  <SPAN style="color:#007F00">'   Column C</SPAN><br>                Me.Parent.Save<br>                <SPAN style="color:#007F00">'   Call another macro like this (uncomment to work)</SPAN><br>                <SPAN style="color:#007F00">'    Call MacroNameHere</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that you wouldn't really want to call your UCase code as it invokes a loop, when the UCase inclusion in the Change event only affects the changed cell. ;)
 

Dave T

Board Regular
Joined
Jun 21, 2005
Messages
93
Fantastic Smitty,

Just what I wanted and it works really well.
I really do appreciate all your help.

This is great forum and source of help.

Regards,
Dave T
 

Watch MrExcel Video

Forum statistics

Threads
1,123,154
Messages
5,600,024
Members
414,356
Latest member
death20

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