insert new row and insert data validation.

[KingGoku]

Hi there,

need help with auto adding data validation everytime new row has been inserted.
Data validation to be "Accept or Reject". if cell is reject whole row to be rejected and highlighted red.

      With WSheet
        .Activate  'activating the sheet
        .Range("A5").EntireRow.Insert (xlDown)
         .Range("A5").EntireRow.ClearFormats     'Clear all formats from row 5
        .Range("A5:Q5").Font.Size = 14      'Increase font size
        'Insert a new row at 5 and shift everything down
       
        .Range("A5").Value = Date             'Insert date at A5
        .Range("B5").Value = FormSelectSht.ComboBoxProduct.Value    'Insert Combobox value at B5
       ' .Range("C5") = JulianDt(Date)
        .Range("C5").Value = FormSelectSht.TextBoxOperator1.Value
        .Range("D5").Value = FormSelectSht.TextBoxOperator2.Value
        .Range("E5").Value = FormSelectSht.TextBoxLot.Value
        .Range("I5").Value = FormSelectSht.CBVent.Value
        .Range("G5").Value = Time           'insert time at G5
        .Range("H5").Value = FormSelectSht.CBCrane.Value
        .Range("J5").Value = FormSelectSht.ComboBoxPowder.Value
        
      End With

 

[BigBeachBananas]

Which cell indicates "Accept" or "Reject"?

 

[KingGoku]

Which cell indicates "Accept" or "Reject"?

Cell L.

Sorry it's not written in code.

 

[BigBeachBananas]

How about this?

With WSheet
    .Activate ' Activating the sheet
    .Range("A5").EntireRow.Insert (xlDown)
    .Range("A5").EntireRow.ClearFormats ' Clear all formats from row 5
    .Range("A5:Q5").Font.Size = 14 ' Increase font size

    ' Insert a new row at 5 and shift everything down

    .Range("A5").Value = Date ' Insert date at A5
    .Range("B5").Value = FormSelectSht.ComboBoxProduct.Value ' Insert Combobox value at B5
    .Range("C5").Value = FormSelectSht.TextBoxOperator1.Value
    .Range("D5").Value = FormSelectSht.TextBoxOperator2.Value
    .Range("E5").Value = FormSelectSht.TextBoxLot.Value
    .Range("I5").Value = FormSelectSht.CBVent.Value
    .Range("G5").Value = Time ' Insert time at G5
    .Range("H5").Value = FormSelectSht.CBCrane.Value
    .Range("J5").Value = FormSelectSht.ComboBoxPowder.Value

    ' Check if cell L5 has the value "Rejected" and apply conditional formatting to the entire row
    If .Range("L5").Value = "Rejected" Then
        .Range("L5").EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="=$L5=""Reject"""
        .Range("L5").EntireRow.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    End If
End With

 

[KingGoku]

How about this?

With WSheet
    .Activate ' Activating the sheet
    .Range("A5").EntireRow.Insert (xlDown)
    .Range("A5").EntireRow.ClearFormats ' Clear all formats from row 5
    .Range("A5:Q5").Font.Size = 14 ' Increase font size

    ' Insert a new row at 5 and shift everything down

    .Range("A5").Value = Date ' Insert date at A5
    .Range("B5").Value = FormSelectSht.ComboBoxProduct.Value ' Insert Combobox value at B5
    .Range("C5").Value = FormSelectSht.TextBoxOperator1.Value
    .Range("D5").Value = FormSelectSht.TextBoxOperator2.Value
    .Range("E5").Value = FormSelectSht.TextBoxLot.Value
    .Range("I5").Value = FormSelectSht.CBVent.Value
    .Range("G5").Value = Time ' Insert time at G5
    .Range("H5").Value = FormSelectSht.CBCrane.Value
    .Range("J5").Value = FormSelectSht.ComboBoxPowder.Value

    ' Check if cell L5 has the value "Rejected" and apply conditional formatting to the entire row
    If .Range("L5").Value = "Rejected" Then
        .Range("L5").EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="=$L5=""Reject"""
        .Range("L5").EntireRow.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    End If
End With

Doesn't work because everytime new row get inserted Data validation moves down as well and new row doesn't have data but not drop down data validation.

 

[BigBeachBananas]

Can you share a sample of your sheet using XL2BB?

 

[KingGoku]

Can you share a sample of your sheet using XL2BB?

I can't get the XL2BB addin

 

[BigBeachBananas]

Is column L supposed to be a drop-down list validation with values "Accept" & "Reject"?

 

[KingGoku]

Is column L supposed to be a drop-down list validation with values "Accept" & "Reject"?

Yes.

 

[BigBeachBananas]

How about this?

      With Wsheet
        .Activate  'activating the sheet
        .Range("A5").EntireRow.Insert (xlDown)
         .Range("A5").EntireRow.ClearFormats     'Clear all formats from row 5
        .Range("A5:Q5").Font.Size = 14      'Increase font size
        'Insert a new row at 5 and shift everything down
   
        .Range("A5").Value = Date             'Insert date at A5
        .Range("B5").Value = FormSelectSht.ComboBoxProduct.Value    'Insert Combobox value at B5
        .Range("C5") = JulianDt(Date)
        .Range("C5").Value = FormSelectSht.TextBoxOperator1.Value
        .Range("D5").Value = FormSelectSht.TextBoxOperator2.Value
        .Range("E5").Value = FormSelectSht.TextBoxLot.Value
        .Range("I5").Value = FormSelectSht.CBVent.Value
        .Range("G5").Value = Time           'insert time at G5
        .Range("H5").Value = FormSelectSht.CBCrane.Value
        .Range("J5").Value = FormSelectSht.ComboBoxPowder.Value
 
      End With
  
      With Wsheet.Range("L5").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Accept,Reject"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
 

    With Rows("5:5")
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$L$5=""Reject"""
    .FormatConditions(1).Interior.Color = 255
    .FormatConditions(1).StopIfTrue = False
    End With