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?

 

[tm1274]

That worked except if the value in cell B2 equals blank, zero or 1 then C2 should be blank. Although it works for blank and zero, if 1 is the value cell C2 has the dropdown and the formatting. C2 should be blank with 1 as the value in B2. Is this possible?

 

[tm1274]

After more testing, it appears I was wrong in my last post. If if Cell B2 equals blank or zero, then cell C1 clears. If B1 (horizontal) has a value of 1 then it should still show the formatting and the dropdown but it doesnt

 

[kevin9999]

That worked except if the value in cell B2 equals blank, zero or 1 then C2 should be blank. Although it works for blank and zero, if 1 is the value cell C2 has the dropdown and the formatting. C2 should be blank with 1 as the value in B2. Is this possible?

Change the line

If vert > 0 Then

To

If vert > 1 Then

 

[tm1274]

I apologize for my mistake, it appears I was wrong in my last post. If if Cell B2 equals blank or zero, then cell C1 clears. If B1 (horizontal) has a value of 1 then it should still show the formatting and the dropdown but it doesnt. I have attached another screenshot that hopefully will explain better.

 

[kevin9999]

Unfortunately, I can't reproduce the problem you're having. Here's what I get:

Before

tm1274.xlsm
	A	B	C	D	E
1	Horizontal	2
2	Vertical	2
3
4
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C1:C3	List	=Options
D1	List	=Options

After

tm1274.xlsm
	A	B	C	D	E
1	Horizontal	2
2	Vertical	0
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C1:D1	List	=Options

Make sure that this is the actual code you're using now ("Options" changed to your validation list of course)

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

 

[tm1274]

I replaced the code with what you sent and it appears to be working the same as your last message now. I didnt think I change anything but may have while trying to figure this out. There is an issue that I am unsure can be fixed though. Cell C1 is sort of a corner or intersection so actually it should be the first cell in both the horizontal and vertical. So when 2 is selected in B1, then cells C1 & D1 should show the format and dropdown, also if cell B2 has a value of 2, then cells C1 and C2 should show the dropdown with formatting. I do not know if that is even possible.

 

[kevin9999]

I replaced the code with what you sent and it appears to be working the same as your last message now. I didnt think I change anything but may have while trying to figure this out. There is an issue that I am unsure can be fixed though. Cell C1 is sort of a corner or intersection so actually it should be the first cell in both the horizontal and vertical. So when 2 is selected in B1, then cells C1 & D1 should show the format and dropdown, also if cell B2 has a value of 2, then cells C1 and C2 should show the dropdown with formatting. I do not know if that is even possible.

OK, understood. Try this:

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

 

[tm1274]

Basically the idea is that if I enter a value in B1 and B2 then the cells are like a grid with the entered values triggering the cells to fill with the dropdown selection and formatting. For instance if I enter 2 in B1 and 2 in B2 then 2 cells in both directions will be filled with the format and dropdown. if I were to enter 5 in B1 and 2 in B2 then there would be 5 cells wide and 2 cells high filled, or if I entered 15 in B1 and 15 in B2 then there would be 15 cells wide by 15 high filled. I am not sure if this makes sense but the overall goal is to allow me to enter those values then change all of the dropdowns that appear allowing calculations based upon the selections and then the results of those calculations would tell me where to place points in an AutoCad drawing to place blocks in a sketch. Currently I have to make calculations one at a time for each row and column, think a front view of an electrical control panel with holes for switches and gauges spaced to allow everything to fit. It may just be a dream but I was hoping to accomplish this but it has gotten incredibly complicated.

 

[kevin9999]

And does the code in post #17 not give you that?

 

[tm1274]

Yes for the most part. My inability to fully explain the overall goal has been the problem. The code gives the horizontal and vertical but not the cells inside of the grid. Attached is another screenshot., if you wouldnt mind taking a look. I apologize for the inconvenience.