Hello VBA Heroes!
I have another question that seems to have me pretty stumped.
The short story to my problem is that a Custom Data Validation doesn't work with my current workbook that is laden with VBA. I hope someone might be able to lend a hand or point me in the right direction.
I tried this formula in the custom code portion of Data Validation:
=ISNA(VLOOKUP(D3,D$1:D9,1,FALSE))
As I am sure you all are able to tell, is it will give an error if a duplicate value is entered.
This works great as long as you ae on the sheet where the data is, and you physically "Enter". But my current spreadsheet puts the data into the sheet without actually having this sheet active.
My workbook is keeping track of mileages on car parts, and the workbook has 3 main worksheets (For now. This will be expanded as parts are added)
The first sheet simply keeps track of mileages, and date, and timestamps when the miles are input into the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Row = 4 And Target.Offset(1, 0).Value = "" Then
Target.Offset(1, 0) = Format(Now(), "m/d/yyyy")
Target.Offset(2, 0) = Format(Now(), "HH:MM:SS")
Range("B7").Formula = "=SUM(B4:XFD4)"
End If
End Sub
The second worksheet is where I indicate what parts are currently being used:
Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Address = "$B$2" Then 'Test if the change occurred in cell B2
' If Target.Column = 2 Then 'Test if the change occurred in column B
'If Target.Cells.Offset(0, -1).Value = "TOELINK" Then
If Target.Address = "$B$2" Then
With Sheet7
With .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
.Value = Target.Value
.Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
.Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
.Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
End With
End With
'ElseIf Target.Cells.Offset(0, -1).Value = "STEERING BLOCK" Then
ElseIf Target.Address = "$B$3" Then
With Sheet8
With .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
.Value = Target.Value
.Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
.Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
.Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
End With
End With
End If
Cells.Columns.AutoFit
End Sub
And then finally, the third sheet is where the data is recorded for the part. Somewhat like a history for the part. It also indicates if a part is active or obsolete:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then ' Only copy uniques when the change is to column A
Application.EnableEvents = False
' Copy uniques to column G
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G:G"), Unique:=True
' Add "Obsolete" to column H and "Active" to the last value in column
Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(0, 1)).Value = "Obsolete"
Range("G" & Rows.Count).End(xlUp).Offset(0, 1).Value = "Active"
Application.EnableEvents = True
End If
Cells.Columns.AutoFit
End Sub
The third sheet also has this formula, as I have'nt figured out how to get it into VBA yet:
=SUMIF(A:A,$G2,D:D)
So the way data is input, I enter the mileage on sheet1, which then time and date stamps the entry.
Then I will go to the second sheet and start entering part numbers. The cell adjacent to the active cell determines what area the part is located, and the active cell is the part number itself.
This part number is then recorded on the third sheet with the date, time, and mileage from the first sheet. and on the third sheet it keeps a running total for that part mileage.
The ISNA....statement works fine, as long as you are in the third sheet. But this is never the case as data is input while being active in the second sheet.
What I want to be able to do, is only record the part number for a single mileage (Since theoretically, I could enter a part at the EXACT instant in time, and I will enter parts more than once in a day.) The mileage will never be the same.
What i dont want to happen, is the mileage to increment if i accidentally scan the same part number twice for a given mileage.
Thanks in advance, I really appreciate any input.
I have another question that seems to have me pretty stumped.
The short story to my problem is that a Custom Data Validation doesn't work with my current workbook that is laden with VBA. I hope someone might be able to lend a hand or point me in the right direction.
I tried this formula in the custom code portion of Data Validation:
=ISNA(VLOOKUP(D3,D$1:D9,1,FALSE))
As I am sure you all are able to tell, is it will give an error if a duplicate value is entered.
This works great as long as you ae on the sheet where the data is, and you physically "Enter". But my current spreadsheet puts the data into the sheet without actually having this sheet active.
My workbook is keeping track of mileages on car parts, and the workbook has 3 main worksheets (For now. This will be expanded as parts are added)
The first sheet simply keeps track of mileages, and date, and timestamps when the miles are input into the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Row = 4 And Target.Offset(1, 0).Value = "" Then
Target.Offset(1, 0) = Format(Now(), "m/d/yyyy")
Target.Offset(2, 0) = Format(Now(), "HH:MM:SS")
Range("B7").Formula = "=SUM(B4:XFD4)"
End If
End Sub
The second worksheet is where I indicate what parts are currently being used:
Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Address = "$B$2" Then 'Test if the change occurred in cell B2
' If Target.Column = 2 Then 'Test if the change occurred in column B
'If Target.Cells.Offset(0, -1).Value = "TOELINK" Then
If Target.Address = "$B$2" Then
With Sheet7
With .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
.Value = Target.Value
.Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
.Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
.Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
End With
End With
'ElseIf Target.Cells.Offset(0, -1).Value = "STEERING BLOCK" Then
ElseIf Target.Address = "$B$3" Then
With Sheet8
With .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
.Value = Target.Value
.Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
.Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
.Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
End With
End With
End If
Cells.Columns.AutoFit
End Sub
And then finally, the third sheet is where the data is recorded for the part. Somewhat like a history for the part. It also indicates if a part is active or obsolete:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then ' Only copy uniques when the change is to column A
Application.EnableEvents = False
' Copy uniques to column G
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G:G"), Unique:=True
' Add "Obsolete" to column H and "Active" to the last value in column
Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(0, 1)).Value = "Obsolete"
Range("G" & Rows.Count).End(xlUp).Offset(0, 1).Value = "Active"
Application.EnableEvents = True
End If
Cells.Columns.AutoFit
End Sub
The third sheet also has this formula, as I have'nt figured out how to get it into VBA yet:
=SUMIF(A:A,$G2,D:D)
So the way data is input, I enter the mileage on sheet1, which then time and date stamps the entry.
Then I will go to the second sheet and start entering part numbers. The cell adjacent to the active cell determines what area the part is located, and the active cell is the part number itself.
This part number is then recorded on the third sheet with the date, time, and mileage from the first sheet. and on the third sheet it keeps a running total for that part mileage.
The ISNA....statement works fine, as long as you are in the third sheet. But this is never the case as data is input while being active in the second sheet.
What I want to be able to do, is only record the part number for a single mileage (Since theoretically, I could enter a part at the EXACT instant in time, and I will enter parts more than once in a day.) The mileage will never be the same.
What i dont want to happen, is the mileage to increment if i accidentally scan the same part number twice for a given mileage.
Thanks in advance, I really appreciate any input.
