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

 

[MrMurphy]

Thanks, agreed and understood. I was able to get the first part working (using the drop down boxes). I'm curious if the next issue will be easy to address, now that I realized the error in my way. I think the way the code was checking for changes, was if there was cursor movement from column F (ElseIf Target.Column = 6 Then...). I still don't completely understand the Target command, and was even more thrown off by the column being used. However, what I'm ultimately trying to accomplish is if the value of the cell (F22) changes, the Button will show/hide. If the value in F22 is 10, the button should show. otherwise, it's not visible. The value of cell F22 is determined by a sum of cells F2 through F21. At no point in time will a cursor actually touch any of the cells between F2-F21, they are populated by formulas to determine whether values were entered in other cells. The goal is to show the button only when the user populates the 10 required fields in column B.

Again, THANK YOU!

 

[mumps]

A Worksheet_Change macro is not triggered by the change in a cell that is the result of a formula so you will need a separate Worksheet_Calculate macro to show/hide the button.

Private Sub Worksheet_Change(ByVal Target As Range)
    If 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
End Sub

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

 

[MrMurphy]

That makes sense...there's so much that can be done and many ways to do it!

I'm receiving a "Run-time error '424': Object Required" error. Looks like it's for the Worksheet_Calculate sub.

This may be unrelated, but I did notice all instances of 'range' in the code I copied and entered are being pasted as lowercase 'range', not 'Range". I just noticed that's different from how you entered it and from what I remembered seeing before. Not sure if it's related to the error.

 

[mumps]

Is it possible to upload a copy of your file as I suggested in Post #10 ?

 

[MrMurphy]

Yes, attached is a portion of the spreadsheet, but I believe everything that is needed for the coding.

https://gopenske.box.com/s/eq6v6vg8neb968tevb33ene7gvbo3ckf

Thanks

 

[mumps]

The link you posted takes me to the Penske site and asks me to sign in to my account, which I of course don't have. Can you use box.com or dropbox.com?

 

[MrMurphy]

Sorry about that...https://app.box.com/s/fo4sj1bjfi2x1rkfgm98dxknhgfy8ddc

 

[mumps]

Try:

Private Sub Worksheet_Calculate()
    If Range("F22").Value = 10 Then
        Me.CommandButton1.Visible = True
    Else
        Me.CommandButton1.Visible = False
    End If
End Sub

 

[MrMurphy]

Excellent! That did it! Thank you VERY much!

 

[mumps]

My pleasure.