VBA code to input a set value into a cell if another equals a defined value

[paulralph86]

Hi there!

My apologies if this is lesson 1 VBA however, I am a complete beginner at this.

I have the following code that works, however, I am required to click somewhere in the worksheet for it to complete the run.
The first section works fine, as soon as I click the F8 cell the F9:F20 field resets to empty. It is the second action that requires me to to click anywhere in the worksheet to complete it.
Is there a way to make it run from start to finish automatically?
He is the macro I have....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("F8").Address Then
Range("F9:F20").Value = ""
End If
If Range("F14").Value = "No Surface Treatment/Coating" Then
Range("F16,F18,F20").Value = "No Surface Treatment/Coating"


End If
End Sub

I appreciate any help/advice you can give me.
Many Thanks,
Paul

 

[Joe4]

Welcome to the Board!

Event procedures are VBA code that run automatically upon some event happening. You have chosen "Worksheet_SelectionChange", which runs automatically upon some pre-determined cells being selected/activated.
There is also "Worksheet_Change", which runs automatically upon some cell being manually updated, and "Worksheet_Calculate", which runs whenever a recalculation is done. And there are others (but these are some of the most common ones, as it pertains to Worksheets - there are Workbook events which run on opening, saving, etc).

So, the first thing you need to do is to determine exactly what "event" should happen that triggers your code to run?
If you let us know what that is, we should be able to help you make it work the way you want.

 

[paulralph86]

Hi Joe,

Thank you very much for the kind welcome.

So I have created a "menu" screen where by the user can make a choice of various different elements of an item and it provides them with a part number for that unique configuration. Part of that screen is 4 data validation drop down lists that all use the same list and they refer to a treatment/coating and there can be multiple coatings/treatment per item. With regards to the 4 drop down lists, we want a fail safe system that prevents a part number having anything different to "No Surface Treatment/Coating" in selections 2,3, or 4 if selection 1 reads "No Surface Treatment/Coating". The idea behind the second section of my macro is if the user selects "No Surface Treatment/Coating" in the first drop down list, then by default the remaining three auto-complete with the same "No Surface Treatment/Coating". This would also apply at any point if the user tries to force a different selection in boxes 2,3, or 4.

I hope this makes sense.

Many Thanks,
Paul

 

[Joe4]

OK, let's use a Worksheet_Change event procedure that see if any cell in F14, F16, F18, or F20 is updated, and if one is, check the value in F14.
If F14 equals "No Surface Treatment/Coating", set the other cells equal to this value.

The code might look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ListRange As Range
    
    Set ListRange = Range("F14,F16,F18,F20")
    
'   Exit if updated cell not in ListRange
    If Intersect(Target, ListRange) Is Nothing Then Exit Sub
    
'   Check value in cell F14, and update other cells if necessary
    If Range("F14").Value = "No Surface Treatment/Coating" Then
        Application.EnableEvents = False
        Range("F16,F18,F20").Value = "No Surface Treatment/Coating"
        Application.EnableEvents = True
    End If
    
End Sub