Hi Matt
I had not even looked to see if there was any VBA in the workbook - I was
just giving a formula solution.
I have uploaded a new version of the book with revised event code at
http://www.box.net/shared/iuudnyqz1g
The revised code is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim name As String, group As String
Dim wsg As Worksheet, wss As Worksheet, tr As Long, tc As Long
Set wsg = ThisWorkbook.Sheets("APP Groups")
Set wss = ThisWorkbook.Sheets("APP Sheets")
tc = Target.Column
Select Case tc
Case 2, 5
If Not Intersect(Target, Range("names_range")) Is Nothing Then
name = Target.Value
Application.EnableEvents = False
If WorksheetFunction.CountIf(Range("B3:E5"), name) > 1 Then
MsgBox "You have entered name " & name & " more than once"
Target.Value = ""
GoTo exit_Sub
End If
End If
GoTo exit_Sub
Case 3, 6
If Not Intersect(Target, Range("names_range").Offset(0, 1)) Is
Nothing Then
name = Target.Offset(0, -1)
Application.EnableEvents = False
name = Target.Offset(0, -1)
group = Target.Value
On Error Resume Next
tr = Application.Match(name, wss.Range("C:C"), 0)
On Error GoTo 0
If tr > 0 Then
wss.Cells(tr, "D") = group
Else
tr = wss.Cells(Rows.Count, 3).End(xlUp).Row + 2
wss.Cells(tr, "C") = name
wss.Cells(tr, "D") = group
End If
End If
Case Else
End Select
exit_Sub:
Application.EnableEvents = True
End Sub
The reason why you had to select a name again, before their group was
updating, was because you were only applying event code to the Name entry,
not the Group entry.
I created a new dynamic range range called Names in column A of APP Sheets
(hide the column if you wish, or move it elsewhere) so you can add any
number of new Names that you wish.
Equally, you can add those new names in column C and the code will enter a
group for them in column D.
If the new name has not been added in column C, then the code will generate
a new pair of entries 2 rows below your last entry on the sheet.
--
Regards
Roger Govier
mj_bowen said:
Hi Roger,
Thank you for the message, however, I need the drop down menu in the cells
in column C and F on 'Sheet APP' groups. It is the data in APP groups that
is
entered into APP sheets!
For example if Bernie is in 'Group one' on the APP group tab
his score may be 2a (which is fed into cell D15 on the APP sheet tab). The
next day he may be in Group 3 and have the score of 1a, which updates onto
cell D15 on the APP sheet tab.
this would also apply to the other names in the file!
Any advice would be warmly welcomed!
Here is an updated version!
http://www.box.net/shared/hxr0isxpfi
regards,
Matt
__________ Information from ESET Smart Security, version of virus
signature database 4833 (20100203) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4833 (20100203) __________
The message was checked by ESET Smart Security.
http://www.eset.com
,2,0))