1) This code in the Summary for Invoicing sheet module will hide rows when you add a date to column A.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
If Target.Row > 3 Then
If IsDate(Target) Then Rows(Target.Row).Hidden = True
End If
End If
End If
End Sub
2) This code in each of the subsequent sheets module will hide a tab when you enter a Y in column S. At the moment it will hide the sheet if a Y is placed anywhere past row 5 in the sheet, but could be restricted to just one cell if that would work better for you.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Columns("S:S")) Is Nothing Then
If Target.Row > 5 Then
If Target = "Y" Then Me.Visible = False
End If
End If
End If
End Sub
3) I've added an icon to the Summary for Invoicing sheet that when clicked will open a simple UserForm. This form will ask for some limited details on new candidates and when you fill them out it will make a copy of the template sheet, name it appropriately and add the details you entered on the form to the relevant fields before setting up formulas on the Summary sheet to link a new row to the new candidate's sheet.
Code for that form is below.
Private Sub cbContinue_Click()
Dim ws As Worksheet
If tbLastName <> "" Then
If tbFirstName <> "" Then
If tbAgency <> "" Then
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set ws = Sheets(Sheets.Count)
With ws
.Name = tbLastName & ", " & tbAgency
.Range("B6") = tbLastName
.Range("C6") = tbFirstName
.Range("D6") = tbAgency
End With
Sheets("Summary for Invoicing").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1, 16).FormulaR1C1 = "='" & ws.Name & "'!R6C"
Unload Me
Else
MsgBox "You must record an Agency.", , ""
tbAgency.SetFocus
End If
Else
MsgBox "You must record a First Name.", , ""
tbFirstName.SetFocus
End If
Else
MsgBox "You must record a Last Name.", , ""
tbLastName.SetFocus
End If
End Sub
See attached for it all working.
Hopefully it gets you somewhere near the desired results, but let me know if you need anything changing in it.
BSB
Bookmarks