T
tarquin
I am trying to use the worksheet_change sub to automatically calculate
(and display) new values in cells on a worksheet, as the user enters
data on that same worksheet.
In the case below, the user enters a number for a tape roll (eg:
"A100") in a list which is in the range B6:B105.
The code then seperates the "A" and the "100" and looks at a 'next
roll' list that displays the NEXT roll number the user shoud use.
If the list doesnt have an A roll number, the entry is considered new
and added to the 'next roll' list (+ 1 to make it the 'next roll
number, ie: A101)
If the 'next roll' list holds an A roll which is larger than the entry,
the enntry will be ignored.
If the 'next roll' list holds an A roll which is less than the entry,
the entry (+1) becomes the new A roll on the 'new roll' list
I am trying to use the worksheet_change sub so this all happens in the
background as the user is entering data. The problem is that this sends
the code into an infinate loop - I presume because part of the code
involves updating the value of another cell, which then triggers
another worksheet_change event, etc etc.
I have also tryed using the worksheet_calculate event with the same
results.
I interpret from the excel help that in:
Sub Worksheet_Change(ByVal Target As Range)
target can be changed to represent a specific range of cells, thus if
the cells that my code is updating is outside of the targeted range the
loop will be avoided. I have tried to substitute 'target' for my range
B6:B105 / "B6:B105" / ("B6:B105"), etc, but all are rejected as bad
syntax.
Is this possible, the answer to the problem, am I doing something
wrong?
Also, whenever I use code that references another worksheet
(eg: if range("Sheet6!A1").value = true then)
the code produces an error, however if the same code is executed via a
sub that is activated from a button click (ie not through the
worksheet_change sub), it works fine.
any help would be appreciated.
here is the code I am using:
********************************************
Sub Worksheet_Change(ByVal Target As Range)
For counter = 6 To 105
If Range("b" & counter).Value = Empty Then
Exit For
End If
this_roll = Range("B" & counter).Value
this_roll_length = Len(this_roll)
this_roll_letter = Left(this_roll, 1)
this_roll_number = Right(this_roll, this_roll_length - 1)
new_roll = True
For counter_1 = 6 To 15
If Range("O" & counter_1).Value = Empty Then
Exit For
End If
If this_roll_letter = Range("O" & counter_1).Value Then
new_roll = False
If Range("P" & counter_1).Value - 1 - this_roll_number <= 0 Then
Range("P" & counter_1).Value = this_roll_number + 1
Exit For
End If
End If
Next
If new_roll = True Then
Range("O" & counter_1).Value = this_roll_letter
Range("P" & counter_1).Value = this_roll_number + 1
End If
Next
End Sub
(and display) new values in cells on a worksheet, as the user enters
data on that same worksheet.
In the case below, the user enters a number for a tape roll (eg:
"A100") in a list which is in the range B6:B105.
The code then seperates the "A" and the "100" and looks at a 'next
roll' list that displays the NEXT roll number the user shoud use.
If the list doesnt have an A roll number, the entry is considered new
and added to the 'next roll' list (+ 1 to make it the 'next roll
number, ie: A101)
If the 'next roll' list holds an A roll which is larger than the entry,
the enntry will be ignored.
If the 'next roll' list holds an A roll which is less than the entry,
the entry (+1) becomes the new A roll on the 'new roll' list
I am trying to use the worksheet_change sub so this all happens in the
background as the user is entering data. The problem is that this sends
the code into an infinate loop - I presume because part of the code
involves updating the value of another cell, which then triggers
another worksheet_change event, etc etc.
I have also tryed using the worksheet_calculate event with the same
results.
I interpret from the excel help that in:
Sub Worksheet_Change(ByVal Target As Range)
target can be changed to represent a specific range of cells, thus if
the cells that my code is updating is outside of the targeted range the
loop will be avoided. I have tried to substitute 'target' for my range
B6:B105 / "B6:B105" / ("B6:B105"), etc, but all are rejected as bad
syntax.
Is this possible, the answer to the problem, am I doing something
wrong?
Also, whenever I use code that references another worksheet
(eg: if range("Sheet6!A1").value = true then)
the code produces an error, however if the same code is executed via a
sub that is activated from a button click (ie not through the
worksheet_change sub), it works fine.
any help would be appreciated.
here is the code I am using:
********************************************
Sub Worksheet_Change(ByVal Target As Range)
For counter = 6 To 105
If Range("b" & counter).Value = Empty Then
Exit For
End If
this_roll = Range("B" & counter).Value
this_roll_length = Len(this_roll)
this_roll_letter = Left(this_roll, 1)
this_roll_number = Right(this_roll, this_roll_length - 1)
new_roll = True
For counter_1 = 6 To 15
If Range("O" & counter_1).Value = Empty Then
Exit For
End If
If this_roll_letter = Range("O" & counter_1).Value Then
new_roll = False
If Range("P" & counter_1).Value - 1 - this_roll_number <= 0 Then
Range("P" & counter_1).Value = this_roll_number + 1
Exit For
End If
End If
Next
If new_roll = True Then
Range("O" & counter_1).Value = this_roll_letter
Range("P" & counter_1).Value = this_roll_number + 1
End If
Next
End Sub