Making different ranges appear to fill out according to the number of changes selected

[AndrwT]

Hi all, good afternoon!

Had a look in the search bar, however didn't find any thread similar to what I'm looking for.

Basically; I have the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Change reason?[/TD]
[TD]Dropdown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Nb of Changes?[/TD]
[TD]Dropdown
(max 5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Surname[/TD]
[TD]Surname[/TD]
[TD]Surname[/TD]
[TD]Surname[/TD]
[TD]Surname[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[/TR]
</tbody>[/TABLE]

What I need is, when selecting in B2 the number of changes required, the respective ranges in columns C to G appear so that the user can fill out the information.

This is also depending on the reason dropdown list in B1, if scneario 1, the ranges will show fields to fill according to the reason; those fields will change if another reason is selected (all the data is stored in another tab in the same workbook).

So, if I select 3 changes with scenario 1, I would like ranges C3:C7; D3:D7 and E3:E7 to appear to be filled out with the required cells for scenario 1, and I would like ranges F3:F7 and G3:G7 to show nothing and stay blank.
If selecting 1 change, only range C3:C7; and if 5, all ranges show fields to be filled.

I know there is a code to do this, however I haven't got the enough knowledge to create it.

PS: my priority is to learn the code to make the ranges appear or not.

Thank you all for your help!!

Andrew

 

[JLGWhiz]

This worksheet event code should be copied to the worksheet code module of the sheet where your dropdown box resides.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Target.Address = "$B$2" And Target <> "" Then
        Columns("C:G").Hidden = True
        On Error Resume Next
        Range("C1").Resize(1, Target.Value).EntireColumn.Hidden = False
        On Error GoTo 0
    End If
Application.EnableEvents = True
End Sub

Once installed, the code will execute only if there is a change in cell B2 values. It will make no changes to the applicable columns if cell B2 is rendered blank.

 

[AndrwT]

Hi JLGWhiz;

Thank you for your reply!

Sorry to answer so late, I was out for several days and have only been able to catch up now.

Will try in the days to come and let you know if it works

Thanks!

Andrew

This worksheet event code should be copied to the worksheet code module of the sheet where your dropdown box resides.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Target.Address = "$B$2" And Target <> "" Then
        Columns("C:G").Hidden = True
        On Error Resume Next
        Range("C1").Resize(1, Target.Value).EntireColumn.Hidden = False
        On Error GoTo 0
    End If
Application.EnableEvents = True
End Sub

Once installed, the code will execute only if there is a change in cell B2 values. It will make no changes to the applicable columns if cell B2 is rendered blank.