A11 Mighty
Member
Hello - Looking for some assistance to see if there are ways to help simplify and speed the Macros listed below.
This code was developed by a previous colleague.
Attached file for reference.
Also, remove the Unprotect and Protect Active sheet as it's not required.
Add lines command button
Delete Line
Also, remove the Unprotect and Protect Active sheet as it's not required.
This code was developed by a previous colleague.
Attached file for reference.
Also, remove the Unprotect and Protect Active sheet as it's not required.
Add lines command button
Code:
Private Sub Assembly_BOM_Add_Lines_Click()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
'Find the end of the BOM section
Dim r As Integer
Dim EndRow As Integer
For r = 12 To 112
If Sheets("GM Analysis").Range("A" & r).Value = "Total value for of Materials" Then
EndRow = r - 2
GoTo A
End If
Next r
MsgBox ("The sheet already has the maximum number of line items.")
GoTo Z
A: 'Find where to insert rows and check to make sure it is in the BOM section
Dim InsertRow As Integer
InsertRow = ActiveCell.Row
If InsertRow < 12 Or InsertRow > EndRow Then
MsgBox ("Where do you want to insert the row? Please select a cell in the Bill of Materials section and click the button again.")
GoTo Z
End If
'Ask how many rows to insert and check to make sure that it will not cause the BOM section to exceed 100 line items
Dim MaxRows As Integer
MaxRows = 100 - EndRow + 11
Dim NumberRows As Variant
B: NumberRows = InputBox("How many rows would you like to insert? You can insert up to " & MaxRows & ".")
If NumberRows = Cancel Then GoTo Z
If Not NumberRows <= MaxRows Then
MsgBox ("You have specified too many lines.")
GoTo B
End If
'Copy row from Extra Lines tab and insert it into the BOM section
Dim n As Integer
For n = 1 To NumberRows
Sheets("Extra Lines").Rows(2).Copy
Sheets("GM Analysis").Activate
ActiveSheet.Rows(InsertRow).Select
Selection.Insert Shift:=xlDown
Next n
Z:
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub
Delete Line
Also, remove the Unprotect and Protect Active sheet as it's not required.
Code:
Private Sub Assembly_BOM_Delete_Line_Click()
ActiveSheet.Unprotect
Application.ScreenUpdating = True
'Find the end of the BOM section
Dim r As Integer
Dim EndRow As Integer
For r = 12 To 112
If Sheets("GM Analysis").Range("A" & r).Value = "Total value for Bill of Materials" Then
EndRow = r - 2
GoTo A
End If
'MsgBox (Selection.Rows.Count)
Next r
MsgBox ("Error: Cannot find the end of the BOM section.")
GoTo Z
A: 'Loop thru selections in BOM section and ask user if they want to delete each one
Dim CurrentRow As Integer
Dim LineItem As Integer
Dim UserResponse As Variant
For CurrentRow = EndRow To 12 Step -1
If Not Intersect(Rows(CurrentRow), Selection) Is Nothing Then
LineItem = Sheets("GM Analysis").Range("A" & CurrentRow)
UserResponse = MsgBox("You are about to delete line item number " & LineItem & ". You will not be able to undo this action. Do you want to continue?", vbYesNoCancel)
If UserResponse = vbCancel Then GoTo Z
If UserResponse = vbNo Then GoTo B
ActiveSheet.Rows(CurrentRow).Delete Shift:=xlUp
End If
B: Next CurrentRow
Z:
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub