I have 5 spreadsheets with dozens of sheets on each. There are events such as selection_change, activate - example below:
If Not Application.Intersect(Range("F1"), Target) Is Nothing Then
If Mid(ActiveSheet.Name, 3, 1) = "." Then
MsgBox "Date already selected, cannot be changed."
Exit Sub
End If
With Calendar1
.Left = Target.Left + Target.Width - Calendar1.Width
.Top = Target.Top + Target.Height
.Height = 200
.Width = 300
.Visible = True
'select Today's date in the Calendar
.Value = Date
End With
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
The problem is that I am having to update around 100 sheets' code every time I change it - it is the same in all of them. To avoid this, I have condensed the worksheet code into the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Call WorksheetChange
End Sub
Private Sub Calendar1_Click()
Call Calendar1Click
End Sub
Private Sub Calendar2_Click()
Call Calendar2Click
End Sub
Private Sub Calendar3_Click()
Call Calendar3Click
End Sub
Private Sub Calendar4_Click()
Call Calendar4Click
End Sub
Private Sub Worksheet_Activate()
Call WorksheetActivate
End Sub
So now I call subs from a module in each workbook, and only have to update 5 modules. I have managed to get them all to work, except for the one that involves Target - I can't use it in standard modules or find a way to pass the variable to a standard module.
Any suggestions on how to use Target in a standard module (whether it's even possible), or how to better manage code in multiple sheet modules will be much most welcome.
Thanks in advance.
Antony.
Bookmarks