• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Macro for selection choices

EE Pros,

I have a simple WS that should have 2 options for a selection in two cells.  If the button/macro is currently selected,  it produces a 1 or a 2.  If it is 1, then it should allow the user to select a choice by a predetermined drop box.  If 2, it should take the selection from another set of two cells.

Attached is the WS.

Thank you in advance.

B.
Macro-to-drive-selection.xlsm
0
Bright01
Asked:
Bright01
  • 4
  • 3
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place the following code on Maturity_Model Sheet module for change event and see if this is what you were trying to achieve....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Application.ScreenUpdating = False
If Not Intersect(Target, Range("D29:D30")) Is Nothing Then
    Application.EnableEvents = False
    If Range("L49").Value <> 1 Then
        Application.Undo
        Range("D29").Value = Range("L64").Value
        Range("D30").Value = Range("M64").Value
    End If
    Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub

Open in new window

1
 
Bright01Author Commented:
Neeraj,

Greetings and thank you for jumping in.

I think I may not have been as clear as I should have been in describing what I was looking for.  I added your code and it does work..... but not exactly how I imagined it would.  And it may have to do with the enable events line.

When you select the button (Use Selection), I'm trying to get it to toggle between the choices.  If there is no selection from the dropdown or there is no selection from L64, M64, the cells in D29 and E30 would simply be blank.

Does that make sense?  Sorry I wasn't more clear up front.  Hopefully that is an easy fix.

B.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Does this work for you?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Application.ScreenUpdating = False
If Not Intersect(Target, Range("D29:D30")) Is Nothing Then
    If Target <> "" Then
        Application.EnableEvents = False
        If Range("L49").Value <> 1 Then
            Application.Undo
            Range("D29").Value = Range("L64").Value
            Range("D30").Value = Range("M64").Value
        End If
        Application.EnableEvents = True
    End If
End If
Application.ScreenUpdating = True
End Sub

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bright01Author Commented:
Not quite.  It still doesn't "toggle" between the two selections when the button is pushed.

B.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well I am not able to understand your requirement correctly.
Do you have issue with the toggle button or the change event code?
BTW I have inserted a toggle button on the sheet, please use that button and let me know what else you want it to do.
Macro-to-drive-selection.xlsm
0
 
Bright01Author Commented:
Neeraj,

Your "fix" is perfect!  Thank you very much!

B.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad your issue is fixed. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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