Calling a function from Select case
 

Hello. On a form which adds a record to the active sheet, I am trying
to call a choice of 3 functions from their modules, using Select Case
where whatever the result of the function gets placed into the relevant
cell on the active sheet. I can't get it to work and was wondering if
someone could show me what is missing from it. I am getting error 449
(argument not optional) on the line ': Application.run' etc. Here is
the relevant part of my coding:

Select Case ActiveSheet.Name
Case "Sheet 1", _
"Sheet 2", _
"Sheet 3" _
: Application.Run "StartofMonth5yrs"
Cells(Nextrow, 3) = StartOfMonth5yrs(Date)

Case "Sheet 4", _
"Sheet 5", _
"Sheet 6" _
: Application.Run "StartofMonth6yrs"
Cells(Nextrow, 3) = StartOfMonth6yrs(Date)

Case "Sheet 7", _
"Sheet 8" _
: Application.Run "StartOfMonth2yrs"
Cells(Nextrow, 3) = StartOfMonth2yrs(Date)
End Select

'Here is one of the 3 function codes which takes today's date, and
calculates to the first day of the following month adding either 5, 6
or 2 years depending on the Select Case choice:

Function StartOfMonth5yrs(InputDate)
If IsDate(InputDate) Then
StartOfMonth5yrs = DateSerial(Year(InputDate) + 5,
Month(InputDate) + 1, 1)
Else
StartOfMonth5yrs = Empty
End If
End Function

Thank you.


--
Blondegirl
------------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...o&userid=29615
View this thread: http://www.excelforum.com/showthread...hreadid=539683

Calling a function from Select case
 

Select Case ActiveSheet.Name
Case "Sheet 1", _
"Sheet 2", _
"Sheet 3"
Cells(NextRow,3) = StartOfMonth5yrs(Date)

Case "Sheet 4", _
"Sheet 5", _
"Sheet 6"
Cells(Nextrow, 3) = StartOfMonth6yrs(Date)

Case "Sheet 7", _
"Sheet 8"
Cells(Nextrow, 3) = StartOfMonth2yrs(Date)
End Select

--
Regards,
Tom Ogilvy

"Blondegirl" wrote
in message ...

Hello. On a form which adds a record to the active sheet, I am trying
to call a choice of 3 functions from their modules, using Select Case
where whatever the result of the function gets placed into the relevant
cell on the active sheet. I can't get it to work and was wondering if
someone could show me what is missing from it. I am getting error 449
(argument not optional) on the line ': Application.run' etc. Here is
the relevant part of my coding:

Select Case ActiveSheet.Name
Case "Sheet 1", _
"Sheet 2", _
"Sheet 3" _
: Application.Run "StartofMonth5yrs"
Cells(Nextrow, 3) = StartOfMonth5yrs(Date)

Case "Sheet 4", _
"Sheet 5", _
"Sheet 6" _
: Application.Run "StartofMonth6yrs"
Cells(Nextrow, 3) = StartOfMonth6yrs(Date)

Case "Sheet 7", _
"Sheet 8" _
: Application.Run "StartOfMonth2yrs"
Cells(Nextrow, 3) = StartOfMonth2yrs(Date)
End Select

'Here is one of the 3 function codes which takes today's date, and
calculates to the first day of the following month adding either 5, 6
or 2 years depending on the Select Case choice:

Function StartOfMonth5yrs(InputDate)
If IsDate(InputDate) Then
StartOfMonth5yrs = DateSerial(Year(InputDate) + 5,
Month(InputDate) + 1, 1)
Else
StartOfMonth5yrs = Empty
End If
End Function

Thank you.


--
Blondegirl
------------------------------------------------------------------------
Blondegirl's Profile:
http://www.excelforum.com/member.php...o&userid=29615
View this thread: http://www.excelforum.com/showthread...hreadid=539683

Calling a function from Select case
 

Thank you very much for your help, it is really appreciated! :

--
Blondegir
-----------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=53968