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 ***
>
Similar Threads
