Simplifying this Macro Help

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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.

Code:
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello nymyth,

Code:
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
 
Last edited:
Upvote 0

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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.
 
Upvote 0

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello nymyth,

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

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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:

Code:
=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.
 
Last edited:
Upvote 0

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.
 
Upvote 0

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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.
 
Upvote 0

Forum statistics

Threads
1,187,060
Messages
5,961,350
Members
438,537
Latest member
Yertion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top