Hi All,
I have a sheet called "new project requiring board". in column H I have a Yes/No drop down validation list.
If the user selects yes I would like a template / worksheet tab called 'Project Board Template' to be duplicated and renamed to the project name in the corresponding row in col A of "new project requiring board"
My code to trigger this event is on lines 11 -16 of the following script, it would be great if someone could help with this.
Many thanks =D
I have a sheet called "new project requiring board". in column H I have a Yes/No drop down validation list.
If the user selects yes I would like a template / worksheet tab called 'Project Board Template' to be duplicated and renamed to the project name in the corresponding row in col A of "new project requiring board"
My code to trigger this event is on lines 11 -16 of the following script, it would be great if someone could help with this.
Many thanks =D
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As String
Dim NewValue As String
' don't trigger any events if user is making some sort of change to multiple
' rows or columns simultaneously
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then ThisWorkbook.Save
If Not Intersect(Target, Columns("F")) Is Nothing Then
If Target.Value = "YES" Then
Call Create_Project_Board(Target.Row)
ThisWorkbook.Save
End If
End If
If Not Intersect(Target, Columns("F")) Is Nothing Then
Select Case Target.Value
Case "YES"
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
Application.EnableEvents = True
If OldValue = "NO" Then
ThisWorkbook.Save
If Target.Value = "YES" Then
MsgBox "Project Board Already Created"
End If
End If
Case "NO"
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
Application.EnableEvents = True
If OldValue = "YES" Then
ThisWorkbook.Save
If Target.Value = "NO" Then
MsgBox "Should we offer to delete the board here?"
End If
End If
End Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("B")) Is Nothing Then
If IsEmpty(Target) Then
ThisWorkbook.Save
End If
End If
End Sub