[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

Macro to populate cells or allow for validation input

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
0
Bright01
Asked:
Bright01
  • 5
  • 5
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Your request is not clear, at least to me.
Would you please elaborate it to let us know your exact requirement?
0
 
Bright01Author 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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bright01Author Commented:
Neeraj,

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

B.
0
 
Bright01Author 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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
Bright01Author 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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
Bright01Author Commented:
Thank you!  Now it works perfectly.

B.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now