Down and dirty where do I put it?

KA3PMW

New Member
Joined
Jul 25, 2016
Messages
46
I have 2 spread sheets that I need to have all entries on in uppercase. I did have one working but it quit.

If I supply the sheets can someone please tell me where to put the VBA code?

Thank you in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
No need to supply the sheets, but are you talking about changing everything after the fact, or as new entries are typed into the cells?

If you want an ongoing automatic change as cells are modified, try this in the worksheet code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = UCase(Target.Value)
End Sub

Right click on the worksheet tab, select "View Code", paste that into the white area on the right.
 
Upvote 0
Copy and paste this macro into the worksheet code module for each sheet. Do the following: right click the tab for your first sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Repeat the process for your second sheet. Close the code window to return to your sheet.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target(1).Value = UCase(Target(1).Value)
    Application.EnableEvents = True
End Sub
 
Upvote 0
To avoid duplicating the code for multiple sheets (using code provided by mumps )
- amend Name1 and Name2 to match the 2 sheet names

and place this in ThisWorkbook module
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "[COLOR=#ff0000]Name1[/COLOR]" Or Sh.Name = "[COLOR=#ff0000]Name2[/COLOR]" Then
        Application.EnableEvents = False
        Target(1).Value = UCase(Target(1).Value)
        Application.EnableEvents = True
    End If
End Sub
 
Last edited:
Upvote 0
Got another problem. The Add/Edit Spotters keeps losing the link when I open Emergency Log.xls
I keep resetting the link and saving the Log but it seems to lose it again. What am I missing???
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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