Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
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 |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
You sometimes write Cell and sometimes Combobox. If you really meant a cell
that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
Dave,
In this case my combo box is a single cell named cboMonth. The user can manually select a month with this combo box. So I suppose that means I can't put a formula directly in this cell since it would interfere with the manual month selection. I've tried the following code in the activate event of the worksheet to put a default value into the cell: Private Sub Worksheet_Activate() Stop Me.Range("cboMonth").Value = month(date) End Sub I thought this event would fire when the user opens the workbook, but when I open it nothing seems to happen. It seems that the Stop should cause execution to stop, but it doesn't. And cboMonth does not get updated. Do you think I need to somehow be more specific about which worksheet contains the combo box (cell)? It's located on Sheet1. Bill "Dave Peterson" wrote in message ... You sometimes write Cell and sometimes Combobox. If you really meant a cell that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
A cell and a combobox are not the same thing.
The combobox is an object that floats over the worksheet (a collection of cells). The cells are updated by typing in the formulabar. That's different from clicking on the combobox, using the dropdown and selecting something from a list. And if the this is the activesheet when the workbook opens, then the sheet is not activated--because it's already active. If you only want the default month value to go into that combobox when the workbook is opened -- not each time the user activates a different worksheet in the same workbook and then comes back to this worksheet, you can dump the worksheet_activate event. Put this in a a general module: Option Explicit Sub auto_Open() Worksheets("Sheet1").CBXMonth.Value = Month(Date) End Sub Change the name of the sheet to what you need. If you really wanted a cell (not a combobox): Option Explicit Sub auto_Open() Worksheets("Sheet1").Range("cboMonth").Value = Month(Date) End Sub (You could also tie into the Workbook_Open event that's located in the ThisWorkbook module.) wmdmurphy wrote: Dave, In this case my combo box is a single cell named cboMonth. The user can manually select a month with this combo box. So I suppose that means I can't put a formula directly in this cell since it would interfere with the manual month selection. I've tried the following code in the activate event of the worksheet to put a default value into the cell: Private Sub Worksheet_Activate() Stop Me.Range("cboMonth").Value = month(date) End Sub I thought this event would fire when the user opens the workbook, but when I open it nothing seems to happen. It seems that the Stop should cause execution to stop, but it doesn't. And cboMonth does not get updated. Do you think I need to somehow be more specific about which worksheet contains the combo box (cell)? It's located on Sheet1. Bill "Dave Peterson" wrote in message ... You sometimes write Cell and sometimes Combobox. If you really meant a cell that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
Dave,
That worked perfectly! I put the code into the workbook open event, and updated the cell. I really appreciate your time and patience. Although I have a lot of Access programming experience, I'm still in the learning stages with Excel programming. Bill "Dave Peterson" wrote in message ... A cell and a combobox are not the same thing. The combobox is an object that floats over the worksheet (a collection of cells). The cells are updated by typing in the formulabar. That's different from clicking on the combobox, using the dropdown and selecting something from a list. And if the this is the activesheet when the workbook opens, then the sheet is not activated--because it's already active. If you only want the default month value to go into that combobox when the workbook is opened -- not each time the user activates a different worksheet in the same workbook and then comes back to this worksheet, you can dump the worksheet_activate event. Put this in a a general module: Option Explicit Sub auto_Open() Worksheets("Sheet1").CBXMonth.Value = Month(Date) End Sub Change the name of the sheet to what you need. If you really wanted a cell (not a combobox): Option Explicit Sub auto_Open() Worksheets("Sheet1").Range("cboMonth").Value = Month(Date) End Sub (You could also tie into the Workbook_Open event that's located in the ThisWorkbook module.) wmdmurphy wrote: Dave, In this case my combo box is a single cell named cboMonth. The user can manually select a month with this combo box. So I suppose that means I can't put a formula directly in this cell since it would interfere with the manual month selection. I've tried the following code in the activate event of the worksheet to put a default value into the cell: Private Sub Worksheet_Activate() Stop Me.Range("cboMonth").Value = month(date) End Sub I thought this event would fire when the user opens the workbook, but when I open it nothing seems to happen. It seems that the Stop should cause execution to stop, but it doesn't. And cboMonth does not get updated. Do you think I need to somehow be more specific about which worksheet contains the combo box (cell)? It's located on Sheet1. Bill "Dave Peterson" wrote in message ... You sometimes write Cell and sometimes Combobox. If you really meant a cell that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
In this case my combo box is a single cell named cboMonth.
A cell and a combobox are not the same thing. Maybe the OP means the drop down list that results from a Validation List being assigned to a cell? If that is the case, then I would think this assignment would do what he wants... Worksheets("Sheet1").Range("cboMonth").Value = MonthName(Month(Now), True) where the actual Name assigned to the cell would be used in place of the cboMonth example name I used. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... A cell and a combobox are not the same thing. The combobox is an object that floats over the worksheet (a collection of cells). The cells are updated by typing in the formulabar. That's different from clicking on the combobox, using the dropdown and selecting something from a list. And if the this is the activesheet when the workbook opens, then the sheet is not activated--because it's already active. If you only want the default month value to go into that combobox when the workbook is opened -- not each time the user activates a different worksheet in the same workbook and then comes back to this worksheet, you can dump the worksheet_activate event. Put this in a a general module: Option Explicit Sub auto_Open() Worksheets("Sheet1").CBXMonth.Value = Month(Date) End Sub Change the name of the sheet to what you need. If you really wanted a cell (not a combobox): Option Explicit Sub auto_Open() Worksheets("Sheet1").Range("cboMonth").Value = Month(Date) End Sub (You could also tie into the Workbook_Open event that's located in the ThisWorkbook module.) wmdmurphy wrote: Dave, In this case my combo box is a single cell named cboMonth. The user can manually select a month with this combo box. So I suppose that means I can't put a formula directly in this cell since it would interfere with the manual month selection. I've tried the following code in the activate event of the worksheet to put a default value into the cell: Private Sub Worksheet_Activate() Stop Me.Range("cboMonth").Value = month(date) End Sub I thought this event would fire when the user opens the workbook, but when I open it nothing seems to happen. It seems that the Stop should cause execution to stop, but it doesn't. And cboMonth does not get updated. Do you think I need to somehow be more specific about which worksheet contains the combo box (cell)? It's located on Sheet1. Bill "Dave Peterson" wrote in message ... You sometimes write Cell and sometimes Combobox. If you really meant a cell that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sub or function not defined
Rick,
You are right. See my previous message to Dave. His suggestion and yours worked great. Bill "Rick Rothstein" wrote in message ... In this case my combo box is a single cell named cboMonth. A cell and a combobox are not the same thing. Maybe the OP means the drop down list that results from a Validation List being assigned to a cell? If that is the case, then I would think this assignment would do what he wants... Worksheets("Sheet1").Range("cboMonth").Value = MonthName(Month(Now), True) where the actual Name assigned to the cell would be used in place of the cboMonth example name I used. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... A cell and a combobox are not the same thing. The combobox is an object that floats over the worksheet (a collection of cells). The cells are updated by typing in the formulabar. That's different from clicking on the combobox, using the dropdown and selecting something from a list. And if the this is the activesheet when the workbook opens, then the sheet is not activated--because it's already active. If you only want the default month value to go into that combobox when the workbook is opened -- not each time the user activates a different worksheet in the same workbook and then comes back to this worksheet, you can dump the worksheet_activate event. Put this in a a general module: Option Explicit Sub auto_Open() Worksheets("Sheet1").CBXMonth.Value = Month(Date) End Sub Change the name of the sheet to what you need. If you really wanted a cell (not a combobox): Option Explicit Sub auto_Open() Worksheets("Sheet1").Range("cboMonth").Value = Month(Date) End Sub (You could also tie into the Workbook_Open event that's located in the ThisWorkbook module.) wmdmurphy wrote: Dave, In this case my combo box is a single cell named cboMonth. The user can manually select a month with this combo box. So I suppose that means I can't put a formula directly in this cell since it would interfere with the manual month selection. I've tried the following code in the activate event of the worksheet to put a default value into the cell: Private Sub Worksheet_Activate() Stop Me.Range("cboMonth").Value = month(date) End Sub I thought this event would fire when the user opens the workbook, but when I open it nothing seems to happen. It seems that the Stop should cause execution to stop, but it doesn't. And cboMonth does not get updated. Do you think I need to somehow be more specific about which worksheet contains the combo box (cell)? It's located on Sheet1. Bill "Dave Peterson" wrote in message ... You sometimes write Cell and sometimes Combobox. If you really meant a cell that was named (insert|name), you could use: me.range("MonthCell").value = month(date) or just use a formula directly in that cell: =month(today()) But I bet you really meant a combobox. So you can ignore this message. wmdmurphy wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct function / VB user defined function | Excel Discussion (Misc queries) | |||
user defined function | Excel Worksheet Functions | |||
Max of a defined function | Excel Worksheet Functions | |||
User Defined Function | Excel Discussion (Misc queries) | |||
User-defined function | Excel Worksheet Functions |