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]

It is working on my side, perhaps your last filled cell in column H on sheet "Training Log" is not the cell you think it is?

 

[FormR]

After running the code, if you execute these lines in the immediate window do you get the results you expect.

? Sheets("Training Log").Range("H" & Sheets("Training Log").Rows.Count).End(xlUp).address

? Sheets("Training Log").Range("H" & Sheets("Training Log").Rows.Count).End(xlUp).validation.inputmessage

5 Ways to Use the VBA Immediate Window - Excel Campus

Learn how to use the VBA Immediate Window in the Visual Basic Editor to get answers about your Excel file, run macros, and more. Free sample file download.

www.excelcampus.com

 

[Ironman]

This is WEIRD. It has just started working and it's perfect. I wish I knew why, I haven't changed anything - yes, the cell is the last row Col H.

The main thing is to say a big THANK YOU and apologise for complicating it.