I would like to add some VBA functionality to a workbook in order to set a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

Did you put the GetMonth in a General module?

But I'm confused about what Me!Month is supposed to be.

If you're working inside a userform and have a textbox named Month, then maybe:

Me.Month.value = getmonth

(the exclamation is a holdover from your Access days???)

If you're trying to use that function in a cell in the same workbook that owns
that function:

=getmonth()

If you're trying to use that function in a cell in a different workbook:

=book1.xls!getmonth()



wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to set a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

Dave,

Yes, I placed the functions in the General section of the worksheet, and
also tried placing them in the General section of the workbook, but got the
error message either way.

Month and Year in my worksheet are both combo box dropdowns so the user can
only enter valid months and years. But I would like to default them when
the workbook is opened to values like May and 2009, based on the current
date.

You are right that the Me!Month comes from my Access programming background.
In Excel how would I reference the Month cell for example, if it can only
accept values from the dropdown list?

Bill


"Dave Peterson" wrote in message
...
Did you put the GetMonth in a General module?

But I'm confused about what Me!Month is supposed to be.

If you're working inside a userform and have a textbox named Month, then
maybe:

Me.Month.value = getmonth

(the exclamation is a holdover from your Access days???)

If you're trying to use that function in a cell in the same workbook that
owns
that function:

=getmonth()

If you're trying to use that function in a cell in a different workbook:

=book1.xls!getmonth()



wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to set
a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in
this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

I didn't mean a general section of the worksheet module. I really meant a new
general module--insert|Module in the VBE.

But the real problem is now twofold. First, you put the code in the wrong spot
for the way it's written. Second, that name of the combobox interferes with the
name of the VBA function (month).

So you have a few ways of resolving the issue.

#1. You can keep the getmonth function in the worksheet module. But you have
to rename that combobox to something nicer (CBOMonth, maybe???).

#2. You can keep the getmonth function in the worksheet module, but change the
function so that it knows to use VBA's Month function--not something assocated
with your combobox:

Me.Month.Value = VBA.Month(Date)

(It'll work, but I don't like this one!)

#3. You can move the function to a General module and leave everything as-is.
(I'd still rename that combobox, if I were you!)

========
If that function is only called by procedures in that worksheet's module, it
wouldn't bother me to keep it in that module.

But I couldn't live with that combobox having the name of Month. (Have I said
that I'd change it enough!) I think it's just screaming for someone to get
confused by it.

And for something like this, I wouldn't use a function at all. I'd just use:

me.cboMonth.value = month(date)

I think the call to the function is just clutter for something so
straightforward.



But I still wouldn't name the combobox Month. It looks too much like the
=month() worksheet function for my tastes.

I'm not sure I'd create a function to do what you want, but:
me.month.value = month(date)

will work.
Should have worked.

But I still wouldn't name the combobox Month. It looks too much like the
=month() worksheet function for my tastes.



wmdmurphy wrote:

Dave,

Yes, I placed the functions in the General section of the worksheet, and
also tried placing them in the General section of the workbook, but got the
error message either way.

Month and Year in my worksheet are both combo box dropdowns so the user can
only enter valid months and years. But I would like to default them when
the workbook is opened to values like May and 2009, based on the current
date.

You are right that the Me!Month comes from my Access programming background.
In Excel how would I reference the Month cell for example, if it can only
accept values from the dropdown list?

Bill

"Dave Peterson" wrote in message
...
Did you put the GetMonth in a General module?

But I'm confused about what Me!Month is supposed to be.

If you're working inside a userform and have a textbox named Month, then
maybe:

Me.Month.value = getmonth

(the exclamation is a holdover from your Access days???)

If you're trying to use that function in a cell in the same workbook that
owns
that function:

=getmonth()

If you're trying to use that function in a cell in a different workbook:

=book1.xls!getmonth()



wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to set
a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in
this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

--

Dave Peterson

I started a message and didn't delete the response that I started.

This is the same message, but I've deleted the junk that should have been
deleted earlier:

I didn't mean a general section of the worksheet module. I really meant a new
general module--insert|Module in the VBE.

But the real problem is now twofold. First, you put the code in the wrong spot
for the way it's written. Second, that name of the combobox interferes with the
name of the VBA function (month).

So you have a few ways of resolving the issue.

#1. You can keep the getmonth function in the worksheet module. But you have
to rename that combobox to something nicer (CBOMonth, maybe???).

#2. You can keep the getmonth function in the worksheet module, but change the
function so that it knows to use VBA's Month function--not something assocated
with your combobox:

Me.Month.Value = VBA.Month(Date)

(It'll work, but I don't like this one!)

#3. You can move the function to a General module and leave everything as-is.
(I'd still rename that combobox, if I were you!)

========
If that function is only called by procedures in that worksheet's module, it
wouldn't bother me to keep it in that module.

But I couldn't live with that combobox having the name of Month. (Have I said
that I'd change it enough!) I think it's just screaming for someone to get
confused by it.

And for something like this, I wouldn't use a function at all. I'd just use:

me.cboMonth.value = month(date)

I think the call to the function is just clutter for something so
straightforward.

Dave Peterson wrote:
<<snipped

wmdmurphy wrote:

Dave,

Yes, I placed the functions in the General section of the worksheet, and
also tried placing them in the General section of the workbook, but got the
error message either way.

Month and Year in my worksheet are both combo box dropdowns so the user can
only enter valid months and years. But I would like to default them when
the workbook is opened to values like May and 2009, based on the current
date.

You are right that the Me!Month comes from my Access programming background.
In Excel how would I reference the Month cell for example, if it can only
accept values from the dropdown list?

Bill

"Dave Peterson" wrote in message
...
Did you put the GetMonth in a General module?

But I'm confused about what Me!Month is supposed to be.

If you're working inside a userform and have a textbox named Month, then
maybe:

Me.Month.value = getmonth

(the exclamation is a holdover from your Access days???)

If you're trying to use that function in a cell in the same workbook that
owns
that function:

=getmonth()

If you're trying to use that function in a cell in a different workbook:

=book1.xls!getmonth()



wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to set
a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in
this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave,

I took your advice and moved all the code into Module1. And I renamed the
month and year cells to avoid confusion.

In the worksheet activate code can I now put code like the following so that
on startup the default value will be put into the appropriate cell:

me.cboMonth.value = month(date)

Bill


"Dave Peterson" wrote in message
...
I started a message and didn't delete the response that I started.

This is the same message, but I've deleted the junk that should have been
deleted earlier:

I didn't mean a general section of the worksheet module. I really meant a
new
general module--insert|Module in the VBE.

But the real problem is now twofold. First, you put the code in the wrong
spot
for the way it's written. Second, that name of the combobox interferes
with the
name of the VBA function (month).

So you have a few ways of resolving the issue.

#1. You can keep the getmonth function in the worksheet module. But you
have
to rename that combobox to something nicer (CBOMonth, maybe???).

#2. You can keep the getmonth function in the worksheet module, but
change the
function so that it knows to use VBA's Month function--not something
assocated
with your combobox:

Me.Month.Value = VBA.Month(Date)

(It'll work, but I don't like this one!)

#3. You can move the function to a General module and leave everything
as-is.
(I'd still rename that combobox, if I were you!)

========
If that function is only called by procedures in that worksheet's module,
it
wouldn't bother me to keep it in that module.

But I couldn't live with that combobox having the name of Month. (Have I
said
that I'd change it enough!) I think it's just screaming for someone to
get
confused by it.

And for something like this, I wouldn't use a function at all. I'd just
use:

me.cboMonth.value = month(date)

I think the call to the function is just clutter for something so
straightforward.

Dave Peterson wrote:
<<snipped

wmdmurphy wrote:

Dave,

Yes, I placed the functions in the General section of the worksheet,
and
also tried placing them in the General section of the workbook, but got
the
error message either way.

Month and Year in my worksheet are both combo box dropdowns so the user
can
only enter valid months and years. But I would like to default them
when
the workbook is opened to values like May and 2009, based on the
current
date.

You are right that the Me!Month comes from my Access programming
background.
In Excel how would I reference the Month cell for example, if it can
only
accept values from the dropdown list?

Bill

"Dave Peterson" wrote in message
...
Did you put the GetMonth in a General module?

But I'm confused about what Me!Month is supposed to be.

If you're working inside a userform and have a textbox named Month,
then
maybe:

Me.Month.value = getmonth

(the exclamation is a holdover from your Access days???)

If you're trying to use that function in a cell in the same workbook
that
owns
that function:

=getmonth()

If you're trying to use that function in a cell in a different
workbook:

=book1.xls!getmonth()



wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to
set
a
default value for a cell named Month and another named Year. I've
opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or
function
not defined". Are there other steps needed to activate visual basic
in
this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

And just as an aside, I try not to use names that look like VBA reserved words,
I't use something like:
TBoxMonth
instead.

It may not confuse excel, but it may confuse me later.


wmdmurphy wrote:

I would like to add some VBA functionality to a workbook in order to set a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in this
workbook?

Also, can named cells be referred to as Me as in the above example?

Bill

--

Dave Peterson

The keyword Me refers to the object holing the executing code, ThisWorkbook,
Sheet, Userform, etc.

It looks like you are trying to use Month as both a constant and a variable.
That will not work. You should use something like myMonth for the variable.
Your function needs to be in the general code Module1 for it to be
recognized when called from code located elsewhere.

"wmdmurphy" wrote in message
...
I would like to add some VBA functionality to a workbook in order to set a
default value for a cell named Month and another named Year. I've opened
the visual basic editor and added some code like the following:

Function GetMonth()
GetMonth = Month(Date)
End Function

Me!Month = GetMonth()

When I try to run these functions I get an error message "Sub or function
not defined". Are there other steps needed to activate visual basic in
this workbook?

Also, can named cells be referred to as Me as in the above example?

Bill