How to make a field 'optional' in a data entry sheet?

[Shushka]

I have a data entry sheet which takes the input values from one sheet and dumps them onto another sheet using the following macro:

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13,D15,G15,D17"

Set inputWks = Worksheets("Coverage_Input")
Set historyWks = Worksheets("PR Data")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all of the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub

​

The problem is that D17 should only be filled in if G15 matches a certain value.... so it should be an optional field. How do I specify this in the macro? As it is above, it requires all cells to be filled in.

 

[EducatedFool]

Hi
Welcome to the Board

Try

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
        historyWks.Cells(nextRow, oCol - 1) = IIf(inputWks.[D17] = inputWks.[G15], inputWks.[D17], "")
    End With

 

[Shushka]

Thank you for the reply... It doesn't work.... it didn't do anything to my form.

I want users to be able to submit the form with D17 empty only if G15 does not match a certain value.
If G15 does match a certain value, I want D17 to be mandatory.