| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
default name and value in input cell
Can someone help me decipher the code I need to use below? I don't
understand how to customize it. I am a finance manager and do not have any coding experience. My spreadsheet basically looks like this: Jan Feb Mar New members <enter <enter <enter I want the user to input a value where it says <enter (which I want defaulted to = 0). If the user subsequently deletes a number, I want the cell to default back to "<enter" with a "0" value. Please help. Thanks. ************************** I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » From: Bob Phillips - view profile Date: Fri, Nov 10 2006 5:13 pm Email: "Bob Phillips" Groups: microsoft.public.excel.worksheet.functions Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author Put <enter in those cells to begin, and then add Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "G1,H5,K6,L10" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, rang(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = "<enter" End If End If ws_exit: Application.EnableEvents = True End Sub This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... - Hide quoted text - - Show quoted text - I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » Rate this post: Text for clearing space |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
default name and value in input cell
Just replace:
Target.Value = "<enter" with Target.Value = 0 -- Gary's Student " wrote: Can someone help me decipher the code I need to use below? I don't understand how to customize it. I am a finance manager and do not have any coding experience. My spreadsheet basically looks like this: Jan Feb Mar New members <enter <enter <enter I want the user to input a value where it says <enter (which I want defaulted to = 0). If the user subsequently deletes a number, I want the cell to default back to "<enter" with a "0" value. Please help. Thanks. ************************** I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » From: Bob Phillips - view profile Date: Fri, Nov 10 2006 5:13 pm Email: "Bob Phillips" Groups: microsoft.public.excel.worksheet.functions Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author Put <enter in those cells to begin, and then add Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "G1,H5,K6,L10" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, rang(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = "<enter" End If End If ws_exit: Application.EnableEvents = True End Sub This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... - Hide quoted text - - Show quoted text - I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » Rate this post: Text for clearing space |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
default name and value in input cell
Just correct the following line in the code by adding an e..........
From: If Not Intersect(Target, rang(WS_RANGE)) Is Nothing Then To: If Not Intersect(Target, range(WS_RANGE)) Is Nothing Then Then it works fine in my Excel 97 and the cells with <enter in them add as zeros. Vaya con Diso, Chuck, CABGx3 " wrote: Can someone help me decipher the code I need to use below? I don't understand how to customize it. I am a finance manager and do not have any coding experience. My spreadsheet basically looks like this: Jan Feb Mar New members <enter <enter <enter I want the user to input a value where it says <enter (which I want defaulted to = 0). If the user subsequently deletes a number, I want the cell to default back to "<enter" with a "0" value. Please help. Thanks. ************************** I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » From: Bob Phillips - view profile Date: Fri, Nov 10 2006 5:13 pm Email: "Bob Phillips" Groups: microsoft.public.excel.worksheet.functions Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author Put <enter in those cells to begin, and then add Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "G1,H5,K6,L10" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, rang(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = "<enter" End If End If ws_exit: Application.EnableEvents = True End Sub This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... - Hide quoted text - - Show quoted text - I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » Rate this post: Text for clearing space |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
default name and value in input cell
Try this modified version............
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "b2,c2,d2,e2" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = 0 Target.NumberFormat = "# ""<enter""" Else Target.NumberFormat = "General" End If End If ws_exit: Application.EnableEvents = True End Sub hth Vaya con Dios, Chuck, CABGx3 " wrote: Okay, below this is the latest version that works but I need the <enter value to = 0. Right now, I get a #VALUE error in my calculation field because it does not recognize <enter as any value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F10:N10" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = "<enter" End If End If ws_exit: Application.EnableEvents = True End Sub wrote: Can someone help me decipher the code I need to use below? I don't understand how to customize it. I am a finance manager and do not have any coding experience. My spreadsheet basically looks like this: Jan Feb Mar New members <enter <enter <enter I want the user to input a value where it says <enter (which I want defaulted to = 0). If the user subsequently deletes a number, I want the cell to default back to "<enter" with a "0" value. Please help. Thanks. ************************** I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » From: Bob Phillips - view profile Date: Fri, Nov 10 2006 5:13 pm Email: "Bob Phillips" Groups: microsoft.public.excel.worksheet.functions Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author Put <enter in those cells to begin, and then add Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "G1,H5,K6,L10" '<=== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, rang(WS_RANGE)) Is Nothing Then If Target.Value = "" Then Target.Value = "<enter" End If End If ws_exit: Application.EnableEvents = True End Sub This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... - Hide quoted text - - Show quoted text - I'm creating an excel template that I need users to populate. I want to mark the required input cells as "<enter"; which should equate to zero. If users subsequently delete their number, I want the field to default back to "<enter". What's the simplest way to do this? Mike Reply » Rate this post: Text for clearing space |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
