Hi!

I am new to this so forgive me if this is a simple request. I have an
excel workbook and I am trying to centralize the code. I have 12
worksheets (Jan - Dec) with the exact same code. All this code does is
manipulate a calendar object and selects a date. I want to put the code
in a module and have each sheet call the sub in the module and I can not
figure out how to pass the correct variable.

This works on each worksheet fine;

Private Sub cCalendar01_Click()
ActiveCell.Value = CDbl(cCalendar01.Value)
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Select
cCalendar01.Visible = False

'This process will set active cell as the cell immediately to the
right of the
ActiveCell.Offset(0, 1).Select

'Range("A1").Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A3:A28"), Target) Is Nothing
Then
cCalendar01.Left = Target.Left + Target.Width -
cCalendar01.Width
cCalendar01.Top = Target.Top + Target.Height
cCalendar01.Visible = True
cCalendar01.Value = Date

ElseIf Not Application.Intersect(Range("F3:F28"), Target) Is Nothing
Then
cCalendar01.Left = Target.Left + Target.Width -
cCalendar01.Width
cCalendar01.Top = Target.Top + Target.Height
cCalendar01.Visible = True
cCalendar01.Value = Date

ElseIf cCalendar01.Visible Then cCalendar01.Visible = False
End If

End Sub

How do I transfer this to a public sub run from a module? I modify 12
worksheets each time I need to make a change and would rather have to
make only one.

Thanks!

Len

*** Sent via Developersdex http://www.developersdex.com ***

You neeed to put a small amount of code on each sheet. The click and Change
event function must be on the same sheet as the event. but these Subs can
call a main routine to do some of the common code. See code below.

'needed on each sheet.
Private Sub cCalendar01_Click()
Call Common_Calendar01_Click(ActiveCell,cCalendar01)

'This process will set active cell as the cell immediately to the
'right of the
ActiveCell.Offset(0, 1).Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
Call Common_SelectionChange(Target, ActiveSheet, cCalendar01)
End If

End Sub


----------------------------------------
'Put this code into a module
Sub Common_Calendar01_Click(oCell,oCalendar)
oCell.Value = CDbl(oCalendar.Value)
oCell.NumberFormat = "mm/dd/yy"
oCalendar.Visible = False

End Sub

Sub Common_SelectionChange(ByVal Target As Range, Sht, oCalendar)

If Not Application.Intersect(Sht.Range("A3:A28"), Target) Is Nothing Then
oCalendar01.Left = Target.Left + Target.Width - oCalendar01.Width
oCalendar01.Top = Target.Top + Target.Height
oCalendar01.Visible = True
oCalendar01.Value = Date

Else
If Not Application.Intersect(Sht.Range("F3:F28"), Target) Is Nothing
Then
oCalendar01.Left = Target.Left + Target.Width - oCalendar01.Width
oCalendar01.Top = Target.Top + Target.Height
oCalendar01.Visible = True
oCalendar01.Value = Date

Else
If oCalendar01.Visible Then
oCalendar01.Visible = False
End If
end if
end if
End Sub


"Len" wrote:

Hi!

I am new to this so forgive me if this is a simple request. I have an
excel workbook and I am trying to centralize the code. I have 12
worksheets (Jan - Dec) with the exact same code. All this code does is
manipulate a calendar object and selects a date. I want to put the code
in a module and have each sheet call the sub in the module and I can not
figure out how to pass the correct variable.

This works on each worksheet fine;

Private Sub cCalendar01_Click()
ActiveCell.Value = CDbl(cCalendar01.Value)
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Select
cCalendar01.Visible = False

'This process will set active cell as the cell immediately to the
right of the
ActiveCell.Offset(0, 1).Select

'Range("A1").Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A3:A28"), Target) Is Nothing
Then
cCalendar01.Left = Target.Left + Target.Width -
cCalendar01.Width
cCalendar01.Top = Target.Top + Target.Height
cCalendar01.Visible = True
cCalendar01.Value = Date

ElseIf Not Application.Intersect(Range("F3:F28"), Target) Is Nothing
Then
cCalendar01.Left = Target.Left + Target.Width -
cCalendar01.Width
cCalendar01.Top = Target.Top + Target.Height
cCalendar01.Visible = True
cCalendar01.Value = Date

ElseIf cCalendar01.Visible Then cCalendar01.Visible = False
End If

End Sub

How do I transfer this to a public sub run from a module? I modify 12
worksheets each time I need to make a change and would rather have to
make only one.

Thanks!

Len

*** Sent via Developersdex http://www.developersdex.com ***