Argument not optional message

[ipbr21054]

Hi,
Please could you advisethe issue with my UCase code

Private Sub InsertNewRow_Click()Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6").Select
Range("A6:Q6").Borders.LineStyle = xlContinuous
Range("A6:Q6").Borders.Weight = xlThin
Range("A6:Q6").Interior.ColorIndex = 6
[COLOR=#ff0000]Range("A6:Q6") = UCase[/COLOR]
Range("M6") = Date
Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
Range("$Q$6").HorizontalAlignment = xlCenter
Sheets("DATABASE").Range("A7").Select
Sheets("DATABASE").Range("A6").Select
End Sub

 

[RoryA]

Re: Arguemnt not optional message

You need to disable events if you're going to make changes inside a Change event, then re-enable them when you're done.

 

[ipbr21054]

Re: Arguemnt not optional message

Can you advise then please how it should be written.

Thanks

 

[RoryA]

Re: Arguemnt not optional message

Amend this bit:

        For Each c In rng
            c.Value = UCase(c.Value)
        Next

to this:

application.enableevents = false
        For Each c In rng
            c.Value = UCase(c.Value)
        Next
application.enableevents = true

 

[ipbr21054]

Re: Arguemnt not optional message

Thanks,

I have now added that as shown below but still small case.
Leaving the cell makes no change to the cell when i just type john smith

My code is as follows,

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = Range("A6").Address Then
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
Dim r As Long
Dim ans As Variant
SearchString = Target.Value
Set SearchRange = Range("A7:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then
Else
r = SearchRange.Row
ans = MsgBox("A Duplicated Customers Name Was Found." & vbNewLine & " " & vbNewLine & "Click Yes To View Their Details", vbYesNo + vbCritical, "DUPLICATED CUSTOMER NAME MESSAGE")
If ans = vbYes Then Application.Goto Range("A" & r)
Dim rng As Range, c As Range


Set rng = Intersect(Target, Rows(6))


If Not rng Is Nothing Then
    If rng.Cells.Count < Me.Columns.Count Then
Application.EnableEvents = False
        For Each c In rng
            c.Value = UCase(c.Value)
        Next
Application.EnableEvents = True
    End If
End If
End If
End If
End Sub

 

[RoryA]

Re: Arguemnt not optional message

The way your code is written, it only applies to A6. (you should really learn to indent your code properly)

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = Range("A6").Address Then
      Dim lastrow As Long
      lastrow = Cells(Rows.Count, "A").End(xlUp).Row
      Dim SearchString As String
      Dim SearchRange As Range
      Dim r As Long
      Dim ans As Variant
      SearchString = Target.Value
      Set SearchRange = Range("A7:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
      If Not SearchRange Is Nothing Then
         r = SearchRange.Row
         ans = MsgBox("A Duplicated Customers Name Was Found." & vbNewLine & " " & vbNewLine & "Click Yes To View Their Details", vbYesNo + vbCritical, "DUPLICATED CUSTOMER NAME MESSAGE")
         If ans = vbYes Then Application.Goto Range("A" & r)
      End If
   End If
   Dim rng As Range, c As Range


   Set rng = Intersect(Target, Rows(6))


   If Not rng Is Nothing Then
      If rng.Cells.Count < Me.Columns.Count Then
         Application.EnableEvents = False
         For Each c In rng
            c.Value = UCase(c.Value)
         Next
         Application.EnableEvents = True
      End If
   End If
End Sub

 

[ipbr21054]

Thanks very much for this & your time.

Working well.

 

[RoryA]

Glad to help.