I have 4 command buttons on a worksheet named 'Calendar' that I want select the quarter. The calendar is populated from a worksheet named 'Calcs'. I am having difficulty in setting the start date on the Calcs worksheet. Leave_Year is a named range in worksheet 'Input' and is general format containing the year ie. 2008

The code that I have is:

Private Sub cboAprJun_Click()
Worksheets!Calcs!C1.Value = DateValue("Leave_Year,4,1")
End Sub

I get the following error; Runtime Error 13 Type mismatch

It's probably something simple but my feeble brain is struggling. :)

Well I've never seen the bang operator used in referencing a cell and also you have your named range inside quotes. How many cells are within your "leave_year" named range?

Hi Keith , never addressed a named range before in VBA so thought that I would have a mess about so what I've come up with may not be the best, but it was all I could get to work'
The code you want is
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 4, 1)

However that will not execute off a worksheet only off a module or workbook code so I had to do the following to get it to work, I'm using a commandbutton for my fiddling not a combo as your are but I assume it will be the same

Public Sub CommandButton1_Click()
Run leaveyear
End Sub

Leaveyear has to be a Function in a module

Public Function leaveyear()
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 4, 1)
End Function


Hope this helps if nobody offers a better solution.

Brian

Thanks Brian a brilliant solution. I am using command buttons, I was tired when naming them.
This is my function;

Public Function leaveyear(qtr)

Select Case qtr
Case 1
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 4, 1)
Case 2
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 7, 1)
Case 3
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 10, 1)
Case 4
Worksheets("Calcs").Range("C1").Value = DateSerial(Range(Names("leave_year")).Value, 13, 1)
End Select

End Function

Keith

That's good, happy to have some input.

Brian