Show/Hide Dropdown List

[tm1274]

Hello Everyone,

I have been searching but have not been able to find a way to show or hide an in cell dropdown list based upon the value in an adjacent cell. To explain it simply, if A1 = 1 then Cell B1 should show the dropdown list however if A1 is blank or empty then B1 should not show the dropdown list.
The ultimate goal would be, if cell A1 equals a vaue of 1-15 entered and then the adjacent cells B1:P1 would show the dropdown list based upn the number entered. So if 5 were entered, then B1,C1,D1,E1 and F1 would show the dropdown and all others (G1:P1) would not show the list. As the number increases then the list appears in more cells in row 1 but as the number decreases then the list only appears in the "quantity" entered into cell A1. Not sure if I am explaining it well enough.
I keep hitting a wall on making it show or hide based upon if a specific value is entered in cell A1. Does anyone have any suggestion on how to accomplish this or any place online I can go to to learn the simplest method of making this work?

 

[kevin9999]

This thread has come a long way since you marked post #6 as the "solution"

OK, let's try this. The logic is that if either B1 or B2 are blank/zero then the grid will be blank, because there cannot be any horizontal cells formatted if B1 is blank - and there cannot be any vertical cells formatted if B2 is blank. You can put a data validation in cells B1 & B2 requiring that a number from 1-15 only is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Range("B1:B2"), Target) Is Nothing Then
        Range("C1:Q15").Clear
        Dim horz As Long, vert As Long, rng As Range
        horz = Range("B1").Value
        vert = Range("B2").Value
        
        If horz * vert > 0 Then
            With Range("C1")
                .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Formula1:="=Options"
                .Interior.Color = vbYellow
                .BorderAround ColorIndex:=1, Weight:=xlThin
                .Copy
                .Resize(vert, horz).PasteSpecial xlPasteValidation
                .Resize(vert, horz).PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                .Select
            End With
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[tm1274]

Hi kevin9999,

I understand completely that it has come a long way, unfortunately it was bad planning on my part.
In regards to your last code, what about if there is just a horizontal row or cell, or a vertical column or cell needed? For instance if I put a 1 in the B1 Horizontal but a zero in the B2 Vertical, C1 should have the formatting and dropdown, but this would be the only cell. This would be same if it were zero in B1 but 1 in B2 or any other combination like 5 in B1 and zero in B2 or 5 in B2 but zero in B1.

 

[kevin9999]

Hi kevin9999,

I understand completely that it has come a long way, unfortunately it was bad planning on my part.
In regards to your last code, what about if there is just a horizontal row or cell, or a vertical column or cell needed? For instance if I put a 1 in the B1 Horizontal but a zero in the B2 Vertical, C1 should have the formatting and dropdown, but this would be the only cell. This would be same if it were zero in B1 but 1 in B2 or any other combination like 5 in B1 and zero in B2 or 5 in B2 but zero in B1.

I don't agree with the logic. If there's a zero in B2 then isn't there zero vertical cells - which would include C1 (which is the first/top vertical cell of the vertical range?). Why not just set, through data validation, that B1 & B2 must accept whole numbers between 1 and 15 inclusive?

 

[tm1274]

I think that I am just over thinking this in hopes to avoid my previous bad planning. The idea is to do calculations in the spreadsheet outside of the "grid" of dropdowns that use the selections to give a numerical value to plot points in another program. In this specific single cell question, I think that by using 1 in both cells accomplishes the same thing giving a single cell and building from there if higher values are entered. Basically I am the only person going to use the sheet so I will already know if I want one point, then place a 1 in both B1 & B2 will do the job.

 

[kevin9999]

I think that I am just over thinking this in hopes to avoid my previous bad planning. The idea is to do calculations in the spreadsheet outside of the "grid" of dropdowns that use the selections to give a numerical value to plot points in another program. In this specific single cell question, I think that by using 1 in both cells accomplishes the same thing giving a single cell and building from there if higher values are entered. Basically I am the only person going to use the sheet so I will already know if I want one point, then place a 1 in both B1 & B2 will do the job.

Correct!

 

[tm1274]

I apologize for all the trouble and really appreciate you taking the time to help me. I have almost no experience with VBA and each time I try something new it seems I have a major fail and just move on to something else. I believe your solution is in fact going to work and very nicely.

 

[kevin9999]

I apologize for all the trouble and really appreciate you taking the time to help me. I have almost no experience with VBA and each time I try something new it seems I have a major fail and just move on to something else. I believe your solution is in fact going to work and very nicely.

You're more than welcome tm, and please return to this forum with any issues you have in the future.