Combining Worksheet change event and change case macros into one

[Dave T]

Hello All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
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> </o>
The first macro is installed in the worksheet and the second is installed in a module.
<o> </o>
Sheet 1 Code<o></o>

[/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> </o>
Module Code<o></o>

[/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> </o>
Thanking you in advance.
<o> </o>
Regards,
Dave T

 

[Smitty]

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]

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]

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]

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