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
 

MrMurphy

New Member
Joined
Mar 15, 2018
Messages
12
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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.
Code:
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

New Member
Joined
Mar 15, 2018
Messages
12
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Is it possible to upload a copy of your file as I suggested in Post #10 ?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Try:
Code:
Private Sub Worksheet_Calculate()
    If Range("F22").Value = 10 Then
        Me.CommandButton1.Visible = True
    Else
        Me.CommandButton1.Visible = False
    End If
End Sub
 

Forum statistics

Threads
1,141,284
Messages
5,705,495
Members
421,398
Latest member
Rahat Anwar

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