Macro to populate cells or allow for validation input

Bright01
Bright01 used Ask the Experts™
on
EE Pros,

I have a Worksheet that has a 3 dropdown cells.  In the first cell, if you select "Consistently", then the other two cells identified, are auto. populated.  If "Consistently" is not selected, you can put any other choice from the list box in the cells.

That's it!  Much thanks in advance.

B.
Macro-for-populating-Cells.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Your request is not clear, at least to me.
Would you please elaborate it to let us know your exact requirement?

Author

Commented:
Neeraj,  my apologies.

I have three cells where there are list box drop downs.  In the first cell (E8) you can select 6 different choices.  What I want a Macro to do is that if the first choice in the selection for E8, "Consistently", is selected, I want it to automatically place the choices for the other two boxes.  For F8, it would place "Achieve" in the box and for F10, "n/a".  IF "Consistently" is not selected for E8, then any dropdown selection can be choosen for all three boxes.

Does that make sense?

B.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Place the following code on Sheet1 Module. To do that, right click the Sheet1 Tab --> View Code and place the following code into the opened code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "E8" Then
    Application.EnableEvents = False
    Range("F8").Value = "ACHIEVE"
    Range("H8").Value = "n/a"
    Application.EnableEvents = True
End If
End Sub

Open in new window

Does this help?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
Neeraj,

You nailed it!  Thank you very much. Great job.  Works perfectly.

B.

Author

Commented:
Neeraj,

Quick question.  Let's say I need the same capability down the E column, say E8:E26, how would I set up the macro to adapt to choices in other E selections between that range?  In other words, if I went to E25 and selected "Consistently" in E25, how would I get it to properly populate the other F & H25 cells?

B.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.

In that case replace the existing code with the following one...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E26")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = "ACHIEVE"
    Target.Offset(0, 3).Value = "n/a"
    Application.EnableEvents = True
End If
End Sub

Open in new window

Author

Commented:
This works, but one small problem.  In the original ask, I had said, "if the first choice in the selection for E8, "Consistently", is selected...."  The way the macro is presently written, if any selection is made in E8:E26, it populates F8:26 with "Achieve".  It's only suppose to do that when "Consistently" is choosen in the E Cells.

B.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Ah.. okay got it.
Please try the below code.....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E26")) Is Nothing Then
    If Target = "CONSISTENTLY" Then
        Application.EnableEvents = False
        Target.Offset(0, 1).Value = "ACHIEVE"
        Target.Offset(0, 3).Value = "n/a"
        Application.EnableEvents = True
    End If
End If
End Sub

Open in new window

Author

Commented:
Thank you!  Now it works perfectly.

B.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial