imback2nite
Board Regular
- Joined
- Oct 30, 2004
- Messages
- 211
- Office Version
- 2003 or older
- Platform
- Windows
I'm having trouble with this code working as a deactivate. As of right now I have it working as a 'Update Name' Command Button which works fine but users are forgetting to 'Hit' the button and the names they are looking for are not showing up in the updated list. So, if I could have the list update when they leave the page or even a Worksheet Change that would be great. Thank you once again for helping!
Code:
Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False 'Update Names'
ActiveSheet.Unprotect "123"
Columns("I:I").Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("D:D").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E2:E500").ClearContents
Range("E2") = "=IF(D2="""","""",COUNTIF(Galleys!$B$21:$AS$65,D2)+SUMPRODUCT((Galleys!$B$20:$AS$20=""LARGE"")*(Galleys!$B$21:$AS$65=D2)))"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & Cells(Rows.Count, "D").End(xlUp).Row)
Sheet2.CommandButton1.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A2")
Sheet2.CommandButton3.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A3")
Sheet2.CommandButton4.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A4")
Sheet2.CommandButton2.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A5")
Sheet2.CommandButton6.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A6")
Sheet2.CommandButton7.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A7")
Sheet2.CommandButton11.Caption = ThisWorkbook.Sheets("Disciplines").Range("$A8")
Application.CutCopyMode = False
ActiveSheet.Protect "123"
Application.ScreenUpdating = True
End Sub