Simplifying this Macro Help

[nymyth]

Howdy all,

I need some assistance to make this macro more streamlined. I have a worksheet that has certain cells that are input cells, but I want to have text in the cell that reads "Please Select Program"

What my simple macro skills have allowed me to do is the following: The code is input into the Worksheet itself and does the trick, but I was hoping there was a cleaner way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("H83").Value = "" Then
        Range("H83").Value = "Please Select Program"
ElseIf Range("L83").Value = "" Then
        Range("L83").Value = "Please Select Program"
ElseIf Range("Q83").Value = "" Then
        Range("Q83").Value = "Please Select Program"
ElseIf Range("U83").Value = "" Then
        Range("U83").Value = "Please Select Program"
ElseIf Range("Y83").Value = "" Then
        Range("Y83").Value = "Please Select Program"
ElseIf Range("AD83").Value = "" Then
        Range("AD83").Value = "Please Select Program"
ElseIf Range("AH83").Value = "" Then
        Range("AH83").Value = "Please Select Program"
ElseIf Range("AL83").Value = "" Then
        Range("AL83").Value = "Please Select Program"
ElseIf Range("AQ83").Value = "" Then
        Range("AQ83").Value = "Please Select Program"
ElseIf Range("AU83").Value = "" Then
        Range("AU83").Value = "Please Select Program"
ElseIf Range("AZ83").Value = "" Then
        Range("AZ83").Value = "Please Select Program"
ElseIf Range("BD83").Value = "" Then
        Range("BD83").Value = "Please Select Program"
   
End If


If Range("H84").Value = "" Then
        Range("H84").Value = "Please Select Program"
ElseIf Range("L84").Value = "" Then
        Range("L84").Value = "Please Select Program"
ElseIf Range("Q84").Value = "" Then
        Range("Q84").Value = "Please Select Program"
ElseIf Range("U84").Value = "" Then
        Range("U84").Value = "Please Select Program"
ElseIf Range("Y84").Value = "" Then
        Range("Y84").Value = "Please Select Program"
ElseIf Range("AD84").Value = "" Then
        Range("AD84").Value = "Please Select Program"
ElseIf Range("AH84").Value = "" Then
        Range("AH84").Value = "Please Select Program"
ElseIf Range("AL84").Value = "" Then
        Range("AL84").Value = "Please Select Program"
ElseIf Range("AQ84").Value = "" Then
        Range("AQ84").Value = "Please Select Program"
ElseIf Range("AU84").Value = "" Then
        Range("AU84").Value = "Please Select Program"
ElseIf Range("AZ84").Value = "" Then
        Range("AZ84").Value = "Please Select Program"
ElseIf Range("BD84").Value = "" Then
        Range("BD84").Value = "Please Select Program"
End If




If Range("H131").Value = "" Then
        Range("H131").Value = "Please Select Program"
ElseIf Range("L131").Value = "" Then
        Range("L131").Value = "Please Select Program"
ElseIf Range("Q131").Value = "" Then
        Range("Q131").Value = "Please Select Program"
ElseIf Range("U131").Value = "" Then
        Range("U131").Value = "Please Select Program"
ElseIf Range("Y131").Value = "" Then
        Range("Y131").Value = "Please Select Program"
ElseIf Range("AD131").Value = "" Then
        Range("AD131").Value = "Please Select Program"
ElseIf Range("AH131").Value = "" Then
        Range("AH131").Value = "Please Select Program"
ElseIf Range("AL131").Value = "" Then
        Range("AL131").Value = "Please Select Program"
ElseIf Range("AQ131").Value = "" Then
        Range("AQ131").Value = "Please Select Program"
ElseIf Range("AU131").Value = "" Then
        Range("AU131").Value = "Please Select Program"
ElseIf Range("AZ131").Value = "" Then
        Range("AZ131").Value = "Please Select Program"
ElseIf Range("BD131").Value = "" Then
        Range("BD131").Value = "Please Select Program"
   
End If


If Range("H132").Value = "" Then
        Range("H132").Value = "Please Select Program"
ElseIf Range("L132").Value = "" Then
        Range("L132").Value = "Please Select Program"
ElseIf Range("Q132").Value = "" Then
        Range("Q132").Value = "Please Select Program"
ElseIf Range("U132").Value = "" Then
        Range("U132").Value = "Please Select Program"
ElseIf Range("Y132").Value = "" Then
        Range("Y132").Value = "Please Select Program"
ElseIf Range("AD132").Value = "" Then
        Range("AD132").Value = "Please Select Program"
ElseIf Range("AH132").Value = "" Then
        Range("AH132").Value = "Please Select Program"
ElseIf Range("AL132").Value = "" Then
        Range("AL132").Value = "Please Select Program"
ElseIf Range("AQ132").Value = "" Then
        Range("AQ132").Value = "Please Select Program"
ElseIf Range("AU132").Value = "" Then
        Range("AU132").Value = "Please Select Program"
ElseIf Range("AZ132").Value = "" Then
        Range("AZ132").Value = "Please Select Program"
ElseIf Range("BD132").Value = "" Then
        Range("BD132").Value = "Please Select Program"
   
End If


End Sub

Thank you.

 

[Leith Ross]

Hello nymyth,

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim col As Variant
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row <> 83 Then Exit Sub
    
        Application.EnableEvents = False
    
        col = Split(Cell.Address(True, False, xlA1), "$")(0)
        
        Select Case col
            Case Is = "H", "L", "Q", "U", "Y", "AD", "AH", "AL", "AQ", "AZ", "BD"
                If Target.Value = "" Then Target.Value = "Please Select Program"
        End Select


        Application.EnableEvents = True


End Sub

 

[nymyth]

This is awesome, thanks so much. There is one issue, the cells in reference are dropdowns, and when I pick an item I see the following error:

Run-time error '424':
object required

Any ideas?

Thanks again.

 

[Leith Ross]

Hello nymyth,

Can you give me an example of the drop down reference?

 

[nymyth]

Hello nymyth,

Can you give me an example of the drop down reference?

So, the drop-down list is in another tab based off of Table.

The drop-down in one of those cells is as follows:

=OFFSET(OffProgram_Cal!$F$2,MATCH(zoneSelect,OffProgram_Cal!$E$3:$E$7,0),,,COUNTIF(OFFSET(OffProgram_Cal!$F$2,MATCH(zoneSelect,OffProgram_Cal!$E$3:$E$7,0),,1,50),"?*"))

The Drop-Down lists are dynamic, and are dependent on another selection.

 

[Leith Ross]

Hello nymyth,

The result of a formula calculation does not trigger a Worksheet_Change event. However, a Data Validation list that uses cell values or has values hard coded into it, will trigger a Worksheet_Change event.

Unfortunately, I do not have a code workaround for this problem.

 

[nymyth]

Hello nymyth,

The result of a formula calculation does not trigger a Worksheet_Change event. However, a Data Validation list that uses cell values or has values hard coded into it, will trigger a Worksheet_Change event.

Unfortunately, I do not have a code workaround for this problem.

No worries, thanks for trying.