If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
assign a value to a cell
is it possible to assign a cell or range of cells to have a value of 1 no
matter what you type into that cell |
#2
|
|||
|
|||
assign a value to a cell
Yes, using VB code; however, it is unclear what you want to do if the user
tries to delete the value. The following code assume the user cannot delete the value... Right click the tab at the bottom of the worksheet, select View Code, and copy/paste the following code into the code window that appeared... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* Note: Change the range in the first statement (starts with Const) to the actual range you want to apply this functionality to. If you want to allow the user to be able to delete the entry, use this code instead... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False If Target.Value "" Then Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* -- Rick (MVP - Excel) "totally lost" totally wrote in message ... is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#3
|
|||
|
|||
assign a value to a cell
Rick, that works (the one where you can delete the entry) excpet no matter
what I type in the cell it changes it to 1, can we do the same thing except when I type something in the cell it will stay eg. I type 67451 and it will show 67451 but will still only be "counted" as 1. "Rick Rothstein" wrote: Yes, using VB code; however, it is unclear what you want to do if the user tries to delete the value. The following code assume the user cannot delete the value... Right click the tab at the bottom of the worksheet, select View Code, and copy/paste the following code into the code window that appeared... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* Note: Change the range in the first statement (starts with Const) to the actual range you want to apply this functionality to. If you want to allow the user to be able to delete the entry, use this code instead... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False If Target.Value "" Then Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* -- Rick (MVP - Excel) "totally lost" totally wrote in message ... is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#4
|
|||
|
|||
assign a value to a cell
I don't understand... whatever is in the cell is in the cell... it can't
display one value and, at the same time, also have a totally different value. The only way to do what I **think** you want is to modify any and all formulas (if we are talking about worksheet interaction) or any and all subroutines/functions (if we are talking about VB code) so that they ignore the value in the cell and use 1 instead. As I said, whatever is in the cell is in the cell... you can't change that. -- Rick (MVP - Excel) "totally lost" wrote in message ... Rick, that works (the one where you can delete the entry) excpet no matter what I type in the cell it changes it to 1, can we do the same thing except when I type something in the cell it will stay eg. I type 67451 and it will show 67451 but will still only be "counted" as 1. "Rick Rothstein" wrote: Yes, using VB code; however, it is unclear what you want to do if the user tries to delete the value. The following code assume the user cannot delete the value... Right click the tab at the bottom of the worksheet, select View Code, and copy/paste the following code into the code window that appeared... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* Note: Change the range in the first statement (starts with Const) to the actual range you want to apply this functionality to. If you want to allow the user to be able to delete the entry, use this code instead... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False If Target.Value "" Then Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* -- Rick (MVP - Excel) "totally lost" totally wrote in message ... is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#6
|
|||
|
|||
assign a value to a cell
Oh... yeah.... I think you might be right; that may very well be what the OP
is asking for. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... COUNTA??? -- Don Guillett Microsoft MVP Excel SalesAid Software "totally lost" wrote in message ... Rick, that works (the one where you can delete the entry) excpet no matter what I type in the cell it changes it to 1, can we do the same thing except when I type something in the cell it will stay eg. I type 67451 and it will show 67451 but will still only be "counted" as 1. "Rick Rothstein" wrote: Yes, using VB code; however, it is unclear what you want to do if the user tries to delete the value. The following code assume the user cannot delete the value... Right click the tab at the bottom of the worksheet, select View Code, and copy/paste the following code into the code window that appeared... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* Note: Change the range in the first statement (starts with Const) to the actual range you want to apply this functionality to. If you want to allow the user to be able to delete the entry, use this code instead... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target, Range(RangeAddress)) Is Nothing Then Application.EnableEvents = False If Target.Value "" Then Target.Value = 1 Application.EnableEvents = True End If End Sub ' ************* End of Code ************* -- Rick (MVP - Excel) "totally lost" totally wrote in message ... is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#7
|
|||
|
|||
assign a value to a cell
I read totally's caveat below that he wants to put any number in the cell,
but have that cell provide a value of only 1 to any formula. totally, can you say a little more about what you are trying to accomplish? There are almost always several ways to accomplish the same goal when using Excel, and I think so far we're off on the wrong foot with the info you provided. -J "totally lost" wrote: is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#8
|
|||
|
|||
assign a value to a cell
As I'm new too, I think the OP(whats op anyways?), HE wants to put a value in
any cell within a range and make sure no one can erase this value, and the second part is to count those cells as 1 each ( COUNTA? by DON). "Jon" wrote: I read totally's caveat below that he wants to put any number in the cell, but have that cell provide a value of only 1 to any formula. totally, can you say a little more about what you are trying to accomplish? There are almost always several ways to accomplish the same goal when using Excel, and I think so far we're off on the wrong foot with the info you provided. -J "totally lost" wrote: is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
#9
|
|||
|
|||
assign a value to a cell
Sorry for the confusion guys!! what Rick said is exactly what I trying to
accomplish. I quote "modify any and all formulas (if we are talking about worksheet interaction) or any and all subroutines/functions (if we are talking about VB code) so that they ignore the value in the cell and use 1 instead." "Lerner" wrote: As I'm new too, I think the OP(whats op anyways?), HE wants to put a value in any cell within a range and make sure no one can erase this value, and the second part is to count those cells as 1 each ( COUNTA? by DON). "Jon" wrote: I read totally's caveat below that he wants to put any number in the cell, but have that cell provide a value of only 1 to any formula. totally, can you say a little more about what you are trying to accomplish? There are almost always several ways to accomplish the same goal when using Excel, and I think so far we're off on the wrong foot with the info you provided. -J "totally lost" wrote: is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell |
Thread Tools | |
Display Modes | |
|
|