Hello guys, I'm new here and also with the use of VBA. I started last week to build my code and it goes well until now. Basically, I write two separate codes and I want to run all of them in the same sheet but when I try to combinate VBA says that there is an ambiguous name detected. How can I solve this?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("I14:I270")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
' Display a message when one of the designated cells has been changed.
' MsgBox "Cell " & Target.Address & " has changed."
If ActiveCell.Value = "OFFLOADED" Then
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = Now
Selection.NumberFormat = "dd-mm-yyyy"
ActiveCell.Offset(0, -4).Range("A1").Select
End If
If ActiveCell.Value = "REQUESTED" Then
ActiveCell.Offset(0, -7).Range("A1").Select
ActiveCell.FormulaR1C1 = Now
Selection.NumberFormat = "dd-mm-yyyy"
ActiveCell.Offset(0, 7).Range("A1").Select
End If
If ActiveCell.Value = "LOADED" Then
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = Now
Selection.NumberFormat = "dd-mm-yyyy"
ActiveCell.Offset(0, -1).Range("A1").Select
End If
If ActiveCell.Value = "REQUESTED" Then
ActiveCell.Offset(0, -6).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Hey, normally when they request a pile, we receive the ID pile number, check on your email if you have one and make sure you will add before continuing.", vbCritical, "Check"
End If
ActiveCell.Offset(0, 6).Range("A1").Select
End If
If ActiveCell.Value = "OFFLOADED" Then
ActiveCell.Offset(0, -6).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Hey, that is strange. No pile request. Please add ID pile.", vbCritical, "Check"
End If
ActiveCell.Offset(0, 6).Range("A1").Select
End If
If ActiveCell.Value = "OFFLOADED" Then
ActiveCell.Offset(0, -7).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Be careful, this is not a right sequence. You can not OFFLOAD a pile that is not even requested.", vbCritical, "Check"
End If
ActiveCell.Offset(0, 7).Range("A1").Select
End If
If ActiveCell.Value = "OFFLOADED" Then
ActiveCell.Offset(0, 1).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Be sure, your pile is not loaded yet. Select LOADED and then you can continue.", vbCritical, "Check"
End If
ActiveCell.Offset(0, -1).Range("A1").Select
End If
If ActiveCell.Value = "LOADED" Then
ActiveCell.Offset(0, -6).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Hey, that is strange. No pile request. Please add ID pile.", vbCritical, "Check"
End If
ActiveCell.Offset(0, 6).Range("A1").Select
End If
If ActiveCell.Value = "LOADED" Then
ActiveCell.Offset(0, -7).Range("A1").Select
If ActiveCell.FormulaR1C1 = "" Then ' Check if cell is empty
MsgBox "Don't be to excited. You missed the requested date, select REQUESTED and then you can LOAD your pile.", vbCritical, "Check"
End If
ActiveCell.Offset(0, 7).Range("A1").Select
End If
End If
End Sub
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("O20:O255")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Application.ScreenUpdating = Folse
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -3).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -5).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -12).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
If ActiveCell.Value = "LOCK" Then
ActiveCell.Offset(0, -13).Range("A1").Select
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("PILE MATRIX TRACKING LIST").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:= _
True
End If
Application.ScreenUpdating = True
End If
End Sub
Last edited by a moderator: