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]

What is the name of the worksheet that this code is in?

 

[scott_od]

What is the name of the worksheet that this code is in?

The worksheet name is Sheet3 (Data)

 

[Peter_SSs]

The worksheet name is Sheet3 (Data)

.. so that is the same sheet that you are getting the input message from out of the range G2:G5?

 

[cmowla]

If you're looking to just reduce the size, you can use a loop (especially since this is so uniform).

Dim i As Integer
i = 2
Do While i <= 5
    If Target.address = "$G$" & i Then
        With Worksheets("Gantt Chart").Range("$F$" & i + 1).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, operator _
            :=xlBetween
            .InputTitle = "Comment"
            .InputMessage = Sheet3.Range("$G$" & i).Value
        End With
    End If
    i = i + 1
Loop

 

[scott_od]

.. so that is the same sheet that you are getting the input message from out of the range G2:G5?

Yes that is correct

 

[scott_od]

If you're looking to just reduce the size, you can use a loop (especially since this is so uniform).

Dim i As Integer
i = 2
Do While i <= 5
    If Target.address = "$G$" & i Then
        With Worksheets("Gantt Chart").Range("$F$" & i + 1).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, operator _
            :=xlBetween
            .InputTitle = "Comment"
            .InputMessage = Sheet3.Range("$G$" & i).Value
        End With
    End If
    i = i + 1
Loop

Thank you - I think this is exactly what I was looking for, I just wasn't sure how to connect target & the output

 

[Peter_SSs]

Yes that is correct

In that case I don't think that you need any looping at all. See if this does the job.

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
End Sub

 

[scott_od]

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 End Sub

@Peter_SSs that works perfectly - thank you so much!
How would I adapt that solution to another variation which already has target range (same sheet as the previous scenario & input message from the same sheet)?

If Not Application.Intersect(Target, Range("H2:L2")) Is Nothing Then
With Worksheets("Gantt Chart").Range("$H$3").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "FTE"
.InputMessage = Sheet3.Range("$Z$2").Value
End With
End If

If Not Application.Intersect(Target, Range("H3:L3")) Is Nothing Then
With Worksheets("Gantt Chart").Range("$H$4").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "FTE"
.InputMessage = Sheet3.Range("$Z$3").Value
End With
End If

If Not Application.Intersect(Target, Range("H4:L4")) Is Nothing Then
With Worksheets("Gantt Chart").Range("$H$5").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "FTE"
.InputMessage = Sheet3.Range("$Z$4").Value
End With
End If

If Not Application.Intersect(Target, Range("H5:L5")) Is Nothing Then
With Worksheets("Gantt Chart").Range("$H$6").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.InputTitle = "FTE"
.InputMessage = Sheet3.Range("$Z$5").Value
End With
End If

 

[Peter_SSs]

How would I adapt that solution to another variation which already has target range (same sheet as the previous scenario & input message from the same sheet)?

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