Insert new row & apply Ucase

[ipbr21054]

Hi,

Can you advise please the correct way to apply Ucase for when i insert a new row each time at A6:H6

I have this code below but keep getting Run Time Error 13

Private Sub InsertNewRow_Click()Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6").Select
Range("A6:H6").Font.Size = 18
Range("A4:H6").Font.Bold = True
Range("A6:H6").Interior.ColorIndex = 6
Range("A6:H6").Borders.LineStyle = xlContinuous
Range("A6:H6").Borders.Weight = xlThin
Range("A6:H6").HorizontalAlignment = xlCenter
Range("A6:H6").VerticalAlignment = xlCenter
Range("A6:H6").Name = "Calibri"
Range("A6:H6").RowHeight = 30
[COLOR=#ff0000]Range("A6:H6").Value = UCase(Range("A6:H6").Value)[/COLOR]
End Sub

Many Thanks

 

[trunten]

that isn't how it should work at all. Perhaps just forget the undo stuff for now. God knows why it can't find the onUndo sub if you've copied it across.

 

[ipbr21054]

To eliminate the error the above line generates, replace it with this one...

Range("A6:H6").Value = Evaluate("IF(A6:H6="""","""",UPPER(A6:H6))")

Hi,
That also didnt work.

Im now getting confused with all this so im going to leave it sorry

 

[ipbr21054]

Maybe @DanteAmor can assist please.

This is what i have left the code as.

Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
  On Error GoTo AllowEvents
  If Target.Count > 1000 Then Exit Sub
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Target
      If c.Row > 5 And c.Column = 2 Then
          If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
          Application.EnableEvents = False
          MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
          c.Value = ""
          c.Select
          Else
              c.Characters(Start:=10, Length:=1).Font.ColorIndex = 3


        End If
      End If
    Next
  End If
AllowEvents:
  Application.EnableEvents = True


End Sub

I just need to have it in Ucase A6:H6

 

[Rick Rothstein]

That also didn't work.

Not very helpful... please describe in what way it did not work (what happened that shouldn't have, tell us about any errors or error messages you saw, etc.).

 

[trunten]

just change to this and you're away:

Private Sub Worksheet_Change(ByVal Target As Range)    
    On Error GoTo AllowEvents
    If Target.Count > 1000 Then Exit Sub
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
      For Each c In Target
        If c.Row > 5 And c.Column = 2 Then
            If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
                Application.EnableEvents = False
                MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
                c.Value = ""
                c.Select
            Else
                c.Characters(Start:=10, Length:=1).Font.ColorIndex = 3
           End If
        End If
      Next
    End If
    If Not Intersect(Target, Me.Range("A6:H6")) Is Nothing Then
        Application.ScreenUpdating = False
        For Each c In Target
            c.Value = UCase(c.Value)
        Next c
        Application.ScreenUpdating = True
    End If
End Sub

 

[ipbr21054]

Thanks but im going to wait for @DanteAmor

 

[jolivanes]

Re: Thanks but im going to wait for @DanteAmor
That's good to know. Make sure that in the future all your queries are for DanteAmor and mention that you will not accept help from anyone else.

 

[ipbr21054]

Re: Thanks but im going to wait for @DanteAmor
That's good to know. Make sure that in the future all your queries are for DanteAmor and mention that you will not accept help from anyone else.

If you read my post i didnt mention that i would not accept help from any other member.
This afternoon i have tried a few pieces of advice from other members including yourself but kept geeting errprs etc.

If you also read my signature that might explain why im going to wait.

 

[Rick Rothstein]

This afternoon i have tried a few pieces of advice from other members including yourself but kept geeting errprs etc.

Telling us you got an error tells us absolutely nothing... you have to describe the error to us and tell us on what line of code it occurred.

 

[ipbr21054]

There was no error message,it just never changed to upper case