I have a spread sheet that contains data for a project hourly estimator. I have code that will allow selection of % utilization of a resource and bring back cost estimates. The code will limit the % utilization and doesn't allow exceeding 100% by using a drop down list of data validation. I also added code to add new rows which will allow copying the formulas from the row above it. When I run the Insert New row macro it will insert a new row without issues, however everything breaks when I click on any cell in the spreadsheet. And when I click the delete button to delete the new inserted row it automatically re-inserts a new row. I'm not sure how to fix it.
What I am trying to accomplish is to allow a user to add a new row and have the formulas from the row above copy into the new inserted row. As the sheet grows I also don't want to limit the data validation rules to a certain area in the spreadsheet which is what I have now. Is there a way to make the scenario above work while not limiting the data validation to a certain area? If you can't help me with the data validation piece can you at least help me figure out the issue I am having when inserting the new row and copying the formulas?
The Private Sub Worksheet_SelectionChange(ByVal Target As Range) is for the Data Validation and the InsertRow is to insert a new row
Please keep in mind that I have both codes residing in the sheet itself and I didn't create them as modules. The spreadsheet is part of a bigger spreadsheet that has other macros, sometimes when I run the insert row macro it opens the other spreadsheet ... weird!!
Code is below... I am not sure how to share the actual spreadsheet directly but here is a link to download it https://dmainc.sharefile.com/d-sd41fa72fc9c47aba .
What I am trying to accomplish is to allow a user to add a new row and have the formulas from the row above copy into the new inserted row. As the sheet grows I also don't want to limit the data validation rules to a certain area in the spreadsheet which is what I have now. Is there a way to make the scenario above work while not limiting the data validation to a certain area? If you can't help me with the data validation piece can you at least help me figure out the issue I am having when inserting the new row and copying the formulas?
The Private Sub Worksheet_SelectionChange(ByVal Target As Range) is for the Data Validation and the InsertRow is to insert a new row
Please keep in mind that I have both codes residing in the sheet itself and I didn't create them as modules. The spreadsheet is part of a bigger spreadsheet that has other macros, sometimes when I run the insert row macro it opens the other spreadsheet ... weird!!
Code is below... I am not sure how to share the actual spreadsheet directly but here is a link to download it https://dmainc.sharefile.com/d-sd41fa72fc9c47aba .
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Macro to allow data validation selection to decrease as allocations are chosen
'Set the columns and row ranges for data validation
ColumnRange = "H:M"
RowRange = "9:17,21:26,29:37,40:45,48:53,58:61,64:70,73:79,83:90,93:98,102:104,107:111"
If Target.CountLarge > 1 Then
Exit Sub
ElseIf Intersect(Target, Columns(ColumnRange)) Is Nothing Then
Exit Sub
ElseIf Intersect(Target, Range(RowRange)) Is Nothing Then
Exit Sub
'Set the column pattern (1'consecutive 2'1 skip 1 etc 3'1 skip 2 etc)
ElseIf Target.Column Mod 1 <> 0 Then
Exit Sub
Else
'Ensures that selection will not be repetitve for values to equal more than 100%
SumPercent = Range("G" & Target.Row).Value
Select Case SumPercent
Case Is = 0
List = "0.25,0.50,0.75,1"
Case Is = 0.25
List = "0.25,0.50,0.75"
Case Is = 0.5
List = "0.25,0.50"
Case Is = 0.75
List = "0.25"
Case Is = 1
List = " "
End Select
'Allows the data validation to use a list format and displays the values allowed for selection
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=List
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End Sub
Sub InsertRow()
Selection.EntireRow.Insert
Selection.EntireRow.FillDown
Dim sRow, lCol As Integer
sRow = ActiveCell.Row
lCol = Cells(sRow, Columns.Count).End(xlToLeft).Column
For Each Cell In Range(Cells(sRow, 1), Cells(sRow, lCol))
If Cell.HasFormula = False Then Cell.ClearContents
Next Cell
End Sub