Cell's value determines range's entry or creates drop down menu

MrMurphy

New Member
Joined
Mar 15, 2018
Messages
12
Hi,
I'm curious whether it would be possible (likely VBA) to allow a cell's value to either update a range to mirror the value of a cell or to insert a drop down menu into each cell in the range.
Specifically, I have a dropdown menu in B1 that allows for three options. If Option1 or Option2 is selected in B1, the range (C1-C20) would mirror the value in B1. If Option 3 is selected in B1, I'd like to have a drop down menu in each cell of the range with three options (Option1, Option2 or null); yes Option1 and Option2 in the dropdown would be the same as available in B1 dropdown, but now excluding Option 3 and adding null.

I'd also like for the values in the range change whenever the value in B1 changes.


Any thoughts or information you'd be able to provide would be greatly appreciated.


Thanks and regards
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. make a selection in B1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim Choices As String
    Choices = "Option1, Option2, Null"
    Range("C1:C20").Validation.Delete
    Range("C1:C20").ClearContents
    Select Case Target.Value
        Case "Option1", "Option2"
            Range("C1:C20") = Target
        Case "Option3"
            With Range("C1:C20").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
    End Select
    Application.ScreenUpdating = True
End Sub
 

MrMurphy

New Member
Joined
Mar 15, 2018
Messages
12

ADVERTISEMENT

As I've been working this spreadsheet, I've come to learn more about what the Worksheet_Change sub does. I'm looking to add another change command, to show/hide a command button, but can't figure out how to combine the two Worksheet_Change subs. Any help is appreciated. Below is what I have...not working.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B20")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim Choices As String
Choices = "Parts, Tires, "
Range("B27:B76").Validation.Delete
Range("B27:B76").ClearContents
Select Case Target.Value
Case "Parts", "Tires"
Range("B27:B76") = Target
Case "Both"
With Range("B27:B76").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
End With
End Select
Application.ScreenUpdating = True


If Not Intersect(Target, Range("F22")) Is Nothing Then Exit Sub
Appplication.ScreenUpdating = False
If Range("F22").Value = "10" Then
Me.CommandButton1.Visible = True
Else
Me.CommandButton1.Visible = False
Application.ScreenUpdating = True
End If

End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B20,F22")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim Choices As String
    Choices = "Parts, Tires"
    If Target.Column = 2 Then
        Range("B27:B76").Validation.Delete
        Range("B27:B76").ClearContents
        Select Case Target.Value
            Case "Parts", "Tires"
                Range("B27:B76") = Target
            Case "Both"
                With Range("B27:B76").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
                End With
        End Select
    ElseIf Target.Column = 6 Then
        If Range("F22").Value = "10" Then
            Me.CommandButton1.Visible = True
        Else
            Me.CommandButton1.Visible = False
        End If
    Application.ScreenUpdating = True
End Sub
 

MrMurphy

New Member
Joined
Mar 15, 2018
Messages
12

ADVERTISEMENT

Thanks a lot for the quick response. I had entered the code, but receiving Compile Error, Block If without End If.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Oops! Forgot "End if"
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B20,F22")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim Choices As String
    Choices = "Parts, Tires"
    If Target.Column = 2 Then
        Range("B27:B76").Validation.Delete
        Range("B27:B76").ClearContents
        Select Case Target.Value
            Case "Parts", "Tires"
                Range("B27:B76") = Target
            Case "Both"
                With Range("B27:B76").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
                End With
        End Select
    ElseIf Target.Column = 6 Then
        If Range("F22").Value = "10" Then
            Me.CommandButton1.Visible = True
        Else
            Me.CommandButton1.Visible = False
        End If
    End If
    Application.ScreenUpdating = True
End Sub
 

MrMurphy

New Member
Joined
Mar 15, 2018
Messages
12
Thanks again for the quick response. Unfortunately it's not yet working, neither with the auto-populate from the top section nor the button show hide of the second section (else if). For the first section, the auto-population/drop down menu in B27-B76 is driven by the drop down menu in B20. The button show/hide is to be driven by a sum total in F22. If the total in F22 equals 10, the button is to show. Otherwise, F22<>10, the button should be hidden.
Thanks for all your help!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

Forum statistics

Threads
1,144,578
Messages
5,725,095
Members
422,590
Latest member
Mikeyyy

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