Capital letters vba code with strange result...

[WGRoad]

Hello all,

So, I have this code on my worksheets to convert all thats written o the specific range, into Upper case letters:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("B9:H78")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("B9:H78"))
        c.Value = UCase(c.Value)
    Next c
    Application.EnableEvents = True
End If
End Sub

The code does work fine as in whatever information I put on the range cells, will be cnverted into Upper Cases. But...

1.--- I have a column with a date format type on that range, and when I put in a date, with that code on the worksheet, it will change the day with the month on the date... That althout can be solved easily with just selecting 2 diferent ranges, leaving the date column out of the code... although I was curious as to why it does that, and also curious as if there is a way of preventing that situation from happening, even with the code englobing that column also.

2.--- All cells of the target range have an IF formula on it, so that acording to the information input, it will do whatever it needs to do. Sometimes, the IF formula of some cell might be deleted by inserting data, but then, maybe due to a user mistake, we might have to delete the data inserted, and insert the formula again, by copying from one of the other cells and pasting the formula on that cell. But, with this code on the worksheet, for the target range, I cannot copy any formulas... which is kinda needed. This one, as well as curious I am about it, I would actually need to find a solution for it .

Thanks in advance for your attention to the matter.

 

[Ruddles]

So, I have this code on my worksheets to convert all thats written o the specific range, into Upper case letters... when I put in a date, with that code on the worksheet, it will change the day with the month on the date... curious as if there is a way of preventing that situation from happening, even with the code englobing that column also.

I'm not sure what 'englobing' means but you can skip the column with the dates in it in this sort of way:-

If c.Column <> 6 then c.Value = UCase(c.Value)

That would skip column 6 (F).

 

[Ruddles]

... with this code on the worksheet, for the target range, I cannot copy any formulas... which is kinda needed. This one, as well as curious I am about it, I would actually need to find a solution for it...

Maybe you want to invoke the upper-case-changing code when a single cell is changed but not when more than one cell is changed in the same operation. That would allow you to paste more than one cell at a time but when you only change one cell at a time by typing into it, the code gets executed.

Does that sound helpful?

If so, start your procedure with something like this:-

If Target.Cells.Count > 1 Then Exit Sub

Then when you need to paste over any cells, just make sure you paste more than one cell at the same time and the upper-case-changing code won't be run.

 

[WGRoad]

Thanks guys .

I decided to just divide the procedure in 2, so that it wouldnt count the date column .

 

[Jonmo1]

Also, instead of skipping the column you can test if it's numeric or text..

If Not IsNumeric(c.Value) Then....

 

[Jonmo1]

Or even better...limit the range to text values only

For Each c In Intersect(Target, Range("B9:H78")).SpecialCells(xlCellTypeConstants, 2)