VBA Code Optimisation

[scott_od]

I am fairly new to VBA and have created a file where the VBA code seems excessive, due to repetition
My feeling is that it can be optimised, but I am not sure of the best way to do it, so I was looking for some guidance on how it could be rewritten.

Below is a part of the code (to update validation input messages) which uses the Worksheet Change event & as you can see, this is the same code repeated, but with varying targets & output values

If Target.Address = "$G$2" Then
With Worksheets("Gantt Chart").Range("$F$3").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "Comment"
.InputMessage = Sheet3.Range("$G$2").Value
End With
End If

If Target.Address = "$G$3" Then
With Worksheets("Gantt Chart").Range("$F$4").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "Comment"
.InputMessage = Sheet3.Range("$G$3").Value
End With
End If

If Target.Address = "$G$4" Then
With Worksheets("Gantt Chart").Range("$F$5").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "Comment"
.InputMessage = Sheet3.Range("$G$4").Value
End With
End If

If Target.Address = "$G$5" Then
With Worksheets("Gantt Chart").Range("$F$6").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "Comment"
.InputMessage = Sheet3.Range("$G$5").Value
End With
End If

Any advise would be gratefully received. Thanks

 

[Peter_SSs]

Is this to be combined with the previous worksheet_change code or does it replace that previous code?

I'll assume combined. If it was replace, then just remove the top block in the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Not Intersect(Target, Range("G2:G5")) Is Nothing Then
    With Worksheets("Gantt Chart").Range("F" & Target.Row + 1).Validation
      .Delete
      .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
      .InputTitle = "Comment"
      .InputMessage = Target.Value
    End With
  End If
 
  If Not Intersect(Target, Range("H2:L5")) Is Nothing And Target.Rows.Count = 1 Then
    With Worksheets("Gantt Chart").Range("H" & Target.Row + 1).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
    .InputTitle = "FTE"
    .InputMessage = Range("Z" & Target.Row).Value
    End With
  End If
End Sub

 

[scott_od]

I'll assume combined. If it was replace, then just remove the top block in the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Not Intersect(Target, Range("G2:G5")) Is Nothing Then
    With Worksheets("Gantt Chart").Range("F" & Target.Row + 1).Validation
      .Delete
      .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
      .InputTitle = "Comment"
      .InputMessage = Target.Value
    End With
  End If
 
  If Not Intersect(Target, Range("H2:L5")) Is Nothing And Target.Rows.Count = 1 Then
    With Worksheets("Gantt Chart").Range("H" & Target.Row + 1).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
    .InputTitle = "FTE"
    .InputMessage = Range("Z" & Target.Row).Value
    End With
  End If
End Sub

It was indeed combined. Thanks again Peter.

 

[Peter_SSs]

You're welcome.

It was indeed combined.

In that case, since if the first condition is met then there is no need to test for the second one as it cannot also be met so the code would be marginally more efficient written like this.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Not Intersect(Target, Range("G2:G5")) Is Nothing Then
    With Worksheets("Gantt Chart").Range("F" & Target.Row + 1).Validation
      .Delete
      .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
      .InputTitle = "Comment"
      .InputMessage = Target.Value
    End With
  ElseIf Not Intersect(Target, Range("H2:L5")) Is Nothing And Target.Rows.Count = 1 Then
    With Worksheets("Gantt Chart").Range("H" & Target.Row + 1).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
    .InputTitle = "FTE"
    .InputMessage = Range("Z" & Target.Row).Value
    End With
  End If
End Sub