Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
Dear Experts:
Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
The code you posted is not a macro (macros are Sub's that do not have any
arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
Just a correction (to a fast finger response???)...
Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
I didn't mean to imply subroutines cannot have arguments... what I said
macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
I guess there's a difference in the way we define macros.
I would include all functions and all subroutines in the macro category--whether or not the function or subroutine has arguments passed to it. And I'd even those old 123 macros and xlm macros as macros, too. If you're trying to make a distinction between subs/procedures that show up in the tools|macro|macros dialog, then even though subs with arguments don't show up, they still are available. For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' And I saw a msgbox showing me "hi". ======= The reason I mentioned functions is that (in my opinion), the biggest (only???) difference between functions and subroutines is that one returns something--a value, an object, or something. And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Rick Rothstein wrote: I didn't mean to imply subroutines cannot have arguments... what I said macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
Just a typo correction...
And I'd even **INCLUDE** those old 123 macros and xlm macros as macros, too. Dave Peterson wrote: I guess there's a difference in the way we define macros. I would include all functions and all subroutines in the macro category--whether or not the function or subroutine has arguments passed to it. And I'd even those old 123 macros and xlm macros as macros, too. If you're trying to make a distinction between subs/procedures that show up in the tools|macro|macros dialog, then even though subs with arguments don't show up, they still are available. For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' And I saw a msgbox showing me "hi". ======= The reason I mentioned functions is that (in my opinion), the biggest (only???) difference between functions and subroutines is that one returns something--a value, an object, or something. And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Rick Rothstein wrote: I didn't mean to imply subroutines cannot have arguments... what I said macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
For instance, I had this in a General module:
Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' ..... ..... And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Hmm, I wasn't aware you could do that. Okay, then, so there goes "my distinction" out the window as to what a macro is.<g -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... I guess there's a difference in the way we define macros. I would include all functions and all subroutines in the macro category--whether or not the function or subroutine has arguments passed to it. And I'd even those old 123 macros and xlm macros as macros, too. If you're trying to make a distinction between subs/procedures that show up in the tools|macro|macros dialog, then even though subs with arguments don't show up, they still are available. For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' And I saw a msgbox showing me "hi". ======= The reason I mentioned functions is that (in my opinion), the biggest (only???) difference between functions and subroutines is that one returns something--a value, an object, or something. And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Rick Rothstein wrote: I didn't mean to imply subroutines cannot have arguments... what I said macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
You do not "run" that code.
It is sheet event code which runs when you change the value of E4 Make sure the code is stored in the sheet module. Right-click on sheet tab and "View Code" Copy/paste into that module. Alt + q to return to Excel. Enter a word in E4 Your sheet name will change to that word. Gord Dibben MS Excel MVP On Thu, 17 Dec 2009 08:51:55 -0800 (PST), andreashermle wrote: Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
I think the definition of macro is pretty nebulous.
For me, it's what I want it to be when I'm using the term <vbg. Rick Rothstein wrote: For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' ..... ..... And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Hmm, I wasn't aware you could do that. Okay, then, so there goes "my distinction" out the window as to what a macro is.<g -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... I guess there's a difference in the way we define macros. I would include all functions and all subroutines in the macro category--whether or not the function or subroutine has arguments passed to it. And I'd even those old 123 macros and xlm macros as macros, too. If you're trying to make a distinction between subs/procedures that show up in the tools|macro|macros dialog, then even though subs with arguments don't show up, they still are available. For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' And I saw a msgbox showing me "hi". ======= The reason I mentioned functions is that (in my opinion), the biggest (only???) difference between functions and subroutines is that one returns something--a value, an object, or something. And functions can be called from the tools|macro|macros dialog, too. It just takes some typing. Rick Rothstein wrote: I didn't mean to imply subroutines cannot have arguments... what I said macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to enter a macro name?
On Dec 17, 11:14*pm, Dave Peterson wrote:
I think the definition of macro is pretty nebulous. * For me, it's what I want it to be when I'm using the term <vbg. Rick Rothstein wrote: For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) * *MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' ..... ..... And functions can be called from the tools|macro|macros dialog, too. *It just takes some typing. Hmm, I wasn't aware you could do that. Okay, then, so there goes "my distinction" out the window as to what a macro is.<g -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... I guess there's a difference in the way we define macros. I would include all functions and all subroutines in the macro category--whether or not the function or subroutine has arguments passed to it. *And I'd even those old 123 macros and xlm macros as macros, too. If you're trying to make a distinction between subs/procedures that show up in the tools|macro|macros dialog, then even though subs with arguments don't show up, they still are available. For instance, I had this in a General module: Option Explicit Sub testme(myStr As String) * *MsgBox myStr End Sub And I hit alt-F8 and typed this: 'testme "hi"' And I saw a msgbox showing me "hi". ======= The reason I mentioned functions is that (in my opinion), the biggest (only???) difference between functions and subroutines is that one returns something--a value, an object, or something. And functions can be called from the tools|macro|macros dialog, too. *It just takes some typing. Rick Rothstein wrote: I didn't mean to imply subroutines cannot have arguments... what I said macros are subroutines that do not any arguments; in other words, subroutines that have arguments cannot be a macro... they are simply subroutines with arguments. I'm a little unclear why you added the distinction between functions and subroutines... the OP didn't mention anything about functions... he posted an event procedure and asked why it didn't show up in the macro's dialog box. -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... Just a correction (to a fast finger response???)... Subroutines can have arguments. *But functions return something--and subs don't. Rick Rothstein wrote: The code you posted is not a macro (macros are Sub's that do not have any arguments)... what you posted is know as event code and is meant to be run automatically by Excel when the triggering event occurs (in the case of your code, when a value changes on the worksheet whose worksheet code window it is in. Exactly what were you expecting this code to do for you? -- Rick (MVP - Excel) "andreashermle" wrote in message ... Dear Experts: Whenever I run this macro a dialog field pops up asking me to enter a macro name. Why is this so? Sub Worksheet_Change(ByVal Target As Excel.Range) If Len(Range("E4")) < 32 And Range("E4") < "" Then *ActiveSheet.Name = Range("E4").Value End If End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - Dear All, ok, thank you so much to all of you. I got the message, it is an event code! Great help of all of you. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to enter a value, tab to next cell in the row, enter ... | Excel Programming | |||
Macro to enter a value, tab to next cell in the row, enter ... | Excel Programming | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
Macro prompt | Excel Discussion (Misc queries) | |||
Prompt User to Enter Data with a macro | Excel Programming |