Could you advise on this code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I see this code a few times on my worksheets but dont know what its actually doing,please can you advise.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)    With Target
        If .Column = 1 Then Exit Sub
        If .Column = 7 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi there. I have copied your code and added some notes to each line. Basically, whats its doing is every time the worksheet is changed, and the change involves only a single cell, and the cell is not in columns 1 or 7, and the cell doesn't have a formula, then it sets the contents of the cell to be upper case.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'   This code is executed every time a change is made on the sheet (but see the note below)
With Target                           ' sets Target as the prefix for any references below starting with a .
        If .Column = 1 Then Exit Sub   ' exits if its column 1 (A)
        If .Column = 7 Then Exit Sub   ' exits if its column 7 (G)
        If .Count = 1 And Not .HasFormula Then   ' if the target is a single cell and it doesn't have a formula in it then
            Application.EnableEvents = False   ' this stops this routine being called again by the next line
            .Value = UCase(.Value)             ' sets the target cell to an upper case version of itself (e.g. Hello would become HELLO)
            Application.EnableEvents = True    ' resets the event handler so this (and any other) routines can be called
        End If
    End With
End Sub
I hope this helps.
 
Upvote 0
Morning,
So looking at the sheet this was taken from columns 1 & 7 are dates where all the other columns are text.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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