Argument Not Optional error

[Ironman]

Hi

I have some existing code that was kindly provided to me that changes the validation details in a cell. I just need it to be simplified, so:

1. When I key a shortcut, the code runs, but only in sheet 'Training Log' in Col H of the last filled row and if the active cell isn't that cell, then that cell is selected and the code runs.

2. I don't need the font/alignment details as these need to remain as they are. I also don't need the "Training 1981-1997" part of the code and it can be deleted. I've tried to delete these myself but I've had an 'argument not optional' error and I didn't know where to start looking.

Sub Fillcell(Color As Long, cValue As String)

    Application.EnableEvents = False
 
    With Selection
      .Font.Name = "Wingdings"
      .Font.Size = 12
      .Font.ColorIndex = 1
      .HorizontalAlignment = xlCenter
      .Value = cValue
 
      If ActiveSheet.Name = "Training Log" Then
        With Selection.Validation
          .Delete
          .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
          :=xlBetween
          .IgnoreBlank = True
          .InCellDropdown = False
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = "Double click for lifetime mileage total up to this date"
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
        End With
        ElseIf ActiveSheet.Name = "Training 1981-1997" Then
        With Selection.Validation
          .Delete
          .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
          :=xlBetween
          .IgnoreBlank = True
          .InCellDropdown = False
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
        End With
      End If
 
    End With
 
    With Selection.Interior
      .ColorIndex = Color
      .Pattern = xlSolid
    End With
End Sub
 
    Application.EnableEvents = True
 
End Sub

Many thanks!

 

[FormR]

Hi, how about.

Sub Fillcell()

   Application.EnableEvents = False

   With Sheets("Training Log")
       With .Range("H" & .Rows.Count).End(xlUp).Validation
         .Delete
         .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
         :=xlBetween
         .IgnoreBlank = True
         .InCellDropdown = False
         .InputTitle = ""
         .ErrorTitle = ""
         .InputMessage = "Double click for lifetime mileage total up to this date"
         .ErrorMessage = ""
         .ShowInput = True
         .ShowError = True
       End With
   End With

   Application.EnableEvents = True
 
End Sub

 

[FormR]

In fact, it can probably be trimmed down to just.

Sub Fillcell()

   With Sheets("Training Log")
       With .Range("H" & .Rows.Count).End(xlUp).Validation
         .Delete
         .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
         .InputMessage = "Double click for lifetime mileage total up to this date"
         .ShowInput = True
       End With
   End With

End Sub

 

[Ironman]

Many thanks!

I've inserted it as a new module and I'm trying to test it, but it's not showing in the list of macros, I don't know why.

Should this be in the Training Log sheet change event?

 

[FormR]

The code you posted was not a change event and neither is the updated code I posted.

Did you use the code exactly as posted in the forum, included the change to remove the passed arguments in the first line?

 

[Ironman]

Ahh, apologies, I've just noticed this code was above the code I posted and it refers to fillcell:

Sub FillGreen()
If ActiveSheet.Name = "Analysis" And ActiveCell.Column = 6 Then
Fillcell 4, "J"
HorizontalAlignment = xlCenter
With Font
 .Bold = False
 End With
ElseIf ActiveSheet.Name = "Training 1981-1997" And ActiveCell.Column = 6 Then
Fillcell 4, "J"
ElseIf ActiveSheet.Name = "Training Log" And ActiveCell.Column = 8 Then
Fillcell 4, "J"
ElseIf ActiveSheet.Name = "Indoor Bike" And ActiveCell.Column = 9 Then
Fillcell 4, "J"
ElseIf ActiveSheet.Name = "Outdoor Bike" And ActiveCell.Column = 7 Then
Fillcell 4, "J"
ElseIf ActiveSheet.Name = "Walking" And ActiveCell.Column = 6 Then
Fillcell 4, "J"
Else
MsgBox "Cell fill does not work in this column or sheet", vbInformation, "Information"
End If
End Sub

I want to keep the validation code you've kindly posted separate from this though.

 

[FormR]

I want to keep the validation code you've kindly posted separate from this though.

Just give it a different name?

 

[Ironman]

Doh, I've just realised it still included

(Color As Long, cValue As String)

I've removed that and it now shows and I've run it, but nothing happens I'm afraid. Also, if the active cell isn't the last row Col H it doesn't select it?

 

[FormR]

Do you specifically want it to select the cell? The code doesn't need to select the cell to apply the data validation.

 

[Ironman]

Ah, that's interesting, then no, not necessary. I tried both your long and short versions and it's still not running though I'm afraid.