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

[MrMurphy]

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

 

[mumps]

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.

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]

That is PERFECT!

Thank you so much! Your practising is paying off!

 

[mumps]

You are very welcome.

 

[MrMurphy]

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]

Try:

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]

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

 

[mumps]

Oops! Forgot "End if"

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]

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]

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.