Hello, I am creating a data entry form in Excel. I would like the number of tables under Section 3 to dynamically display based on a cell C8's value entered by a user. For example, if a user enters 3 in C8, then 3 spaces are available for them to enter data under section 3. If they enter 2, then 2 subsections are displayed (unhidden). I have a functioning macro to hide rows based on the cell's value. The macro works for values when a user enters 4 then changes 3 then 2. But it does not work if they first enter a lower number then change to a higher number since the macro only hides rows but does not unhide them. Ideally, they would enter the correct value on the first try but in case they need to update/change.
Alternatively, if there is another easier way to do this, I am welcome to trying that. I have written code for 4 subsections but will need to add more and currently the code is being updated manually so I will need to write each IF statement for the number of potential subsections a user will need to fill out.
Thanks in advance for assistance! The code is pasted below and the sample workbook is attached.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 5 And Target.Row = 8 Then
If Target.Value = "1" Then
Application.Rows("54:85").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "3" Then
Application.Rows("75:85").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "2" Then
Application.Rows("65:85").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "4" Then
Application.Rows("86").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub
I am sure people on here will know a better way, but until then.......
If the number of Funding Sources is unlimited then i think you will need to get a loop going and create each instance rather than hiding/unhiding.
For example
Sub CreateRows()
Dim fSource As Integer 'number of funding sources
Dim I As Integer 'loops
Dim sRow As Integer ' row to start the input from
sRow = 11 ' starting row
fSource = Range("e8") 'number user typed in e8
'start loop
For I = 1 To fSource
Range("c" & sRow + I) = I 'create list of numbers
Range("e" & sRow + I).Interior.Color = vbYellow ' colour cell to show user
'create data validation in each cell
With Range("e" & sRow + I).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$O$2:$O$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next I
're-sum start row for next inut part
sRow = sRow + fSource + 3
End Sub
The next part from 18 downwards would then carry on and re-sum your start row as sRow = sRow + fSource + 3 (blankrows), it can get a bit unweildy with the numbers but once correct it will be more flexible
Where the banners will be placed might be a little tricky but i would deal with that later, at the moment its is working out the calculations behind where the boxes go.
Hi Nicole,
You could try the attached code which works for the 4 sections you currently have in the document. If the maximum number of tables under Section 3 isn't too large you can easily expand this code to accommodate, but if you will have lots of tables then a loop in the code would probably be better as Purfleet said.
This code uses named ranges to hide the entire row where a table resides e.g.
Range("Activity_Table_2").EntireRow.Hidden = True
and uses SELECT CASE which I find easier to read than lots of IF's 🙂
I've created 4 named ranges named (surprise!) Activity_Table_1, Activity_Table_2 etc.
These named ranges only need to refer to the first cell in each row as that's enough for the EntireRow method to work.
My code assumes that the first table will always be visible. If it is not then you can hide it in the Worksheet_Activate event.
Regards
Phil
