Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-15-2008, 12:34 PM   #1
Keith
Newly Registered User
 
Keith's Avatar
 
Join Date: May 2000
Location: Northampton,England
Posts: 129
Thanks: 28
Thanked 0 Times in 0 Posts
Keith
Send a message via Skype™ to Keith
Change cell value code problem

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.

Keith is offline   Reply With Quote
Old 07-16-2008, 12:15 AM   #2
chergh
blah
 
chergh's Avatar
 
Join Date: Jun 2004
Posts: 1,414
Thanks: 0
Thanked 3 Times in 2 Posts
chergh will become famous soon enough chergh will become famous soon enough
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?
chergh is offline   Reply With Quote
Old 07-16-2008, 05:41 AM   #3
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 547 Times in 529 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
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
Code:
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

Code:
Public Sub CommandButton1_Click()
Run leaveyear
End Sub
Leaveyear has to be a Function in a module

Code:
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

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 07-16-2008, 06:26 AM   #4
Keith
Newly Registered User
 
Keith's Avatar
 
Join Date: May 2000
Location: Northampton,England
Posts: 129
Thanks: 28
Thanked 0 Times in 0 Posts
Keith
Send a message via Skype™ to Keith
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
Keith is offline   Reply With Quote
Old 07-16-2008, 06:36 AM   #5
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 547 Times in 529 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
That's good, happy to have some input.

Brian

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lastworkingday Code Problem JohnLee Modules & VBA 2 05-14-2008 11:57 PM
problem with Popup Calendar code for Access 95 Ariid Modules & VBA 3 11-24-2005 12:47 AM
Change to design view of report in code marathonlady Reports 5 07-23-2003 11:21 AM
Report no data code problem Matt Brown General 0 03-14-2003 01:14 AM
[SOLVED] How Can I Change The Print Device For A Report Using Code jbonacorda Modules & VBA 2 11-25-2002 02:52 AM




All times are GMT -8. The time now is 06:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World