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

 

[steve the fish]

Re: Arguemnt not optional message

What is it you are trying to do? There wont be anything in those cells.

 

[ipbr21054]

Re: Arguemnt not optional message

Morning.
I operate my button which inserts a new row.
It is this row I need to be upper case.

 

[steve the fish]

Re: Arguemnt not optional message

If there was something in the row you can convert it to uppercase but there isnt an uppercase format as such.

 

[ipbr21054]

Re: Arguemnt not optional message

When i insert a new row by pressing the button & the code running i needed the cells to change the text to upper case when i type

 

[steve the fish]

Re: Arguemnt not optional message

Maybe try this in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

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
        For Each c In rng
            c.Value = UCase(c.Value)
        Next
    End If
End If
    
End Sub

 

[ipbr21054]

Re: Arguemnt not optional message

I have added that code in to an existing code,see below.
Nothing happens when i leave the cell.
It only changes when i return to that cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "W"


'   *** Specify start row ***
    myStartRow = 5
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True
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
        For Each c In rng
            c.Value = UCase(c.Value)
        Next
    End If
End If
End Sub

 

[RoryA]

Re: Arguemnt not optional message

The code Steve posted was a Worksheet_Change code, not a Worksheet_SelectionChange code.

 

[steve the fish]

Re: Arguemnt not optional message

Thats because it needs worksheet change as i said. With selection change nothing happens until you change the selection. With worksheet change its when you change the cell contents.

 

[ipbr21054]

Re: Arguemnt not optional message

Sorry my mistake.
So ive added it into the correct code but i either did it wrong or im missing something as i get into a loop when the msgbox appears and unable to get out of it whether i click yes or no just keep having to select ye or no.

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
        For Each c In rng
            c.Value = UCase(c.Value)
        Next
    End If
End If
End If
End If
End Sub