Hi Cal,
Hey, you're right! I didn't think of it...Worksheet Change doesn't work with the linked values. However, I tried Worksheet Calculate (which is similar) and found it (Calculate) does work...
As to an extra button...Have to keep in mind the end-user, there will be a lot of buttons to click anyway, and if there are too many, this becomes very annoying (and energy-consuming) for them. So, IF at all possible - avoid any more than is absolutely neccessary (that's why I went for the linked s/s solution).
Was the code you gave intended for one very specific round or for all rounds on one definition sheet? - I could only get #ERROR#-Not Found whenever I ran it...
In the meantime, I needed some code to validate any new rounds the user may add in the DB_30 and 36 sheets. (I.E. This code tells whether any particular round already exists - whether it's known by a different name or not)
This is done by assigning a unique binary number to each round (row), and this number is dependent on which columns there are entries in. We then check if there are any two rows that have the same number assigned to it, and if there are, there is a duplicate... (see my post, Array Question - look for duplicates)
Then I thought - this idea can be easily modified and extended as a quick & simple way to find the name of the round from the entries that are made on the Temp sheet (as the temp sheet is laid out in exactly the same way as the DB_30 and 36 sheets {whereas the input sheet is not}).
I.E. assign this number as an ID number on the DB_30 (or 36) sheet in the empty A column, search this sheets A column until a row is found with an ID number that matches the number obtained similarly from the temp sheet and - Voila! The name is in the B column on that row. E.G.
Option Explicit
'code for sheets (DB_30 & 36) where all rounds are defined
Private Sub CheckIsNew36RoundButton_Click()
Dim Found, i%, N%, BinaryNum#, BinarySum#, IdNumber#
Application.ScreenUpdating = False
For N = 6 To 56 'rows 6 to 56
With Worksheets("DB_36").Rows(N)
Set Found = .Find(what:="*", LookIn:=xlValues, searchorder:=xlByRows)
If Not Found Is Nothing Then
BinarySum = 0
For i = 1 To 7
Set Found = .FindNext(Found)
BinaryNum = 2 ^ (Found.Column)
BinarySum = BinarySum + BinaryNum
Next i
'//put unique ID numbers in column A
Range("A" & N) = BinarySum
End If
End With
Next N
Application.ScreenUpdating = True
Range("A65536").End(xlUp).Select
IdNumber = Range("A65536").End(xlUp).Value
With Range("A6:" & Selection.Offset(-1, 0).Address)
Set Found = .Find(what:=IdNumber, LookIn:=xlValues)
If Not Found Is Nothing Then
'//there is a duplicate
MsgBox "Sorry, your entry for a " & ActiveCell.Offset(0, 1) & _
" round" & vbLf & _
"duplicates a pre-existing round and will be deleted", _
, "ERROR ! - Duplicated entry."
ActiveCell.EntireRow.ClearContents
ActiveCell.Offset(0, 1).Select
Else
'//there is no duplicate
ActiveCell.Offset(1, 1).Select
MsgBox "Congratulations, your " & ActiveCell.Offset(-1, 0) _
& " round is indeed a new round", , "This New Round Has Been Listed..."
End If
End With
End Sub
Option Explicit
'code for the temp sheets
Private Sub Worksheet_Calculate()
Dim FindIt, Found, i%, BinarySum#, BinaryNum#
Application.ScreenUpdating = False
If Range("30Temp!A6") = Empty And Range("30Temp!AD6") <> 0 Then
MsgBox "Entering the date will stop this really" & vbLf & _
"annoying message from popping up :o)", , "WHAT DATE??..."
End If
With Worksheets("30Temp").Rows(6)
Set Found = .Find(what:="*", LookIn:=xlValues, searchorder:=xlByRows)
If Not Found Is Nothing Then
BinarySum = 0
For i = 1 To 7
Set Found = .FindNext(Found)
BinaryNum = 2 ^ (Found.Column)
BinarySum = BinarySum + BinaryNum
'(this number matches the number for the Geelong
'round on sheet DB_30, needs to be checked more)
Next i
End If
End With
'//the following is not working yet - (ideas needing to be worked on)
With Worksheets("DB_30").Range("A6:A56")
Set FindIt = .Find(what:=BinarySum, LookIn:=xlValues, searchorder:=xlByColumns)
If Not FindIt Is Nothing Then
FindIt.Address.Select
Range("30Temp!B6") = Selection.Offset(0, 1)
End If
Application.ScreenUpdating = True
End With
'//do some other things here
End Sub
John