Allowing only one cell in a column to have value

[gryphonrise]

I'm using Excel 2003. How do I make it so that when one cell in a selected column has a number greater than zero, all other cells in the column MUST be zero? I want to set it so that only one cell (any cell) in the column can have a value greater than zero. For example, in the column A1 through A10, I only want ONE cell to be able to contain a number value. It can be any cell in the selected column, but that one cell's value must force all the other cells to be empty.

I'm using the spreadhseet for a class schedule and I can only teach one class a day, so when a person has signed up for a particular class, the other classes cannot be taught.

I appreciate any help anyone can give me. I apologize if i'm not explaining myself very well, this is my first time to use the Excel program. Thank you all for anything you can suggest.

 

[Gates Is Antichrist]

Do you notice any difference between the code and your formulas?

I am fluent in VBA including Union and Intersect and well understand your code, but rather than responding I'll just conclude that your message is posed to the O.P. in the interest of altruistic assistance

 

[Gates Is Antichrist]

Do you notice any difference between the code and your formulas?

I am fluent in VBA including Union and Intersect and well understand your code, but rather than responding I'll just conclude that your message is posed to the O.P. in the interest of altruistic assistance. Neat code, by the way

 

[gryphonrise]

I aplogize for not being more clear in my question. The columns represent different days and need to be independent of each other. For example, column A needs to have one (and only one) value in it, column B needs another and so forth.

Again, I'm so sorry if I'm not making myself clear. This is all so new to me and I'm just beyond lost. You've both been so much help. Thank you very much. I appreciate all your help so far and any more help or tips you're willing/able to offer.

 

[gryphonrise]

Aaron Blood,

It works! It works! It works!

I tried the second code you gave me for the expanded area and it works! I'm using it on cells F12:Q18, and all the columns are independent of each other! I'm so happy!

Is there any way to use the same code in seperate blocks on the same sheet? I want to apply it to F12:Q18, F27:Q33, and F42:Q48. I tried copying and pasting the same code with the different cells, but I got an error message with "ambiguous name detected: Worksheet_Change"

Is there a change I can make so that this wonderful, working, brilliant code can be applied to those three blocks of cells?

Gates is Anti-Christ,

Thank you for all your help, as well. And thank you SO much for recommending that I make copies of my work before I try these changes. I would never have thought to do that. Trust me, that advice has helped me out at least a dozen times with Excel today alone.

Both of you have been terrific. I can't believe there are people willing to offer their help like this. Thank you both so much.

 

[Derek]

Hi there

Just for fun you might like to try the doubleclick event macro

Right click your sheet tab, left click View Code and paste this code in the white area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range
Set r = Range(Target.Address)
Dim var As Variant
var = Target.Formula
Target.EntireColumn.ClearContents
r.Value = var
End Sub

After you have typed a new entry in a cell, doubleclick that cell and it will clear anything else in the column. This will also preserve the formula if your entry was a formula.

If you have column headers in row 1 you can use this code instead.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range
Set r = Range(Target.Address)
Dim var As Variant
var = Target.Formula
Range(Cells(2, Target.Column), Cells(65536, Target.Column)).ClearContents
r.Value = var
End Sub


regards
Derek

 

[gryphonrise]

(hanging my head in abject humiliation)

Gates is AntiChrist,

You were absolutely right (OK, you knew that, but what man doesn't like to hear he's right? Zero was a bad idea for the other cells in the column because they're being counted in the count function of the row.

Aaron Blood,

Is there any way to change the code so that the other cells in the column are empty? Oh, and the different cell blocks thing is still giving me fits (my last post with another pathetic question for you is floating around here somewhere...) Thank you so so so much for anything and everything.

Derek,

Thank you for your help. I haven't tried your suggestion yet, but I'll let you know how it turns out.

Everyone,

I hate to keep bothering everyone here with all these questions. I have NO Excel tips for anyone, so I'm afraid the only thing I can give here is my complete thanks and awe. Thnak you all.

 

[Gates Is Antichrist]

Is there any way to change the code so that the other cells in the column are empty?

Change Aaron's code from 0 to "" (pair of double quotes). You could also change 0 to Empty. (See my first post in this thread, hee hee). This would make your count be one for the affected columns, if I understand what you want here. Also, using Derek's ".ClearContents" will keep the nuked cells out of the count.

Earlier I spoke of "working from a copy" of source data" which may or may not be useful for you (by that I'm talking about "not destroying" the data, as opposed to working from a copy of the .XLS itself, which is just general play-it-safe advice). Here's a little perspective on what I meant by that.

Nowadays (okay, maybe the last 20 odd years ) cells are "cheap." In _many_ cases you can add columns or add sheets without bloating or slowing down the application (ask any Microsoft developer, ROFL). So commonly I add an entirely different sheet, that visually imitates the original source data sheet, but has modified values. That would be something like...
10
90
50
40
on sheet "OrigData"
and
10
0
0
0
on sheet "ParedData" or whatever you name it.

This isn't mandatory, but I would just say that it makes ME sleep a little better when I try new or untested code on data, to know that I can start over again and the source hasn't been wiped out. I speak from experience on this

As to using VBA code versus using logic within the worksheet cells, often you have a choice. Often you can come up with slick and efficient code solutions such as Aaron and Derek did. Just to give you something to consider on that, I write a real lot of VBA code myself, yet usually end up with cell formula results for 95% of the calculations (instead of code) - in fact, 100% on the great majority of my spreadsheets.

I just thought you might wonder why I went the way I did with my completely different answer. There's nothing wrong with using code and it's usually much more "fun" to get results that way. It's in part a philosophical issue, as to using VBA when you can get the answer on the sheet itself. I'm not going to war over it or even saying that I'm correct, but it's just a perspective to consider in going for solutions. (Besides, I'm not sure it met your objectives well anyway, now that you've explained it further.)

At any rate it's impressive that you picked it up so quickly, since most "newbies" need a while before stepping into using code. Good work!

 

[gryphonrise]

(blush)

Thank you, Gates is Antichrist. This spreadshseet has been a trial by fire for me and I appreciate all your, Aaron Blood's and Derek's help.

 

[Aaron Blood]

Aaron Blood,

Is there any way to use the same code in seperate blocks on the same sheet? I want to apply it to F12:Q18, F27:Q33, and F42:Q48. I tried copying and pasting the same code with the different cells, but I got an error message with "ambiguous name detected: Worksheet_Change"

I believe you could modify the code as follows to process multiple non-contiguous ranges on the same sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Set watch_range = Range("F12:Q18, F27:Q33, F42:Q48")
    For Each watch_area In watch_range.Areas
        If Union(watch_area, Target).Areas.Count = 1 Then
            Set subrange = Intersect(watch_area, Target.EntireColumn)
            n = Target.Value
            subrange.Value = ""
            Target.Value = n
        End If
    Next watch_area
    Application.EnableEvents = True
End Sub

 

[Aaron Blood]

Earlier I spoke of "working from a copy" of source data" which may or may not be useful for you (by that I'm talking about "not destroying" the data, as opposed to working from a copy of the .XLS itself, which is just general play-it-safe advice). Here's a little perspective on what I meant by that.

Nowadays (okay, maybe the last 20 odd years ) cells are "cheap." In _many_ cases you can add columns or add sheets without bloating or slowing down the application (ask any Microsoft developer, ROFL). So commonly I add an entirely different sheet, that visually imitates the original source data sheet, but has modified values. That would be something like...
10
90
50
40
on sheet "OrigData"
and
10
0
0
0
on sheet "ParedData" or whatever you name it.

This isn't mandatory, but I would just say that it makes ME sleep a little better when I try new or untested code on data, to know that I can start over again and the source hasn't been wiped out. I speak from experience on this

I'm still trying to follow your logic here with the formulas...

Are you saying that your formulas would be able to look at sheet "OrigData" and understand that the value 10 was the last value input in the column? What if 50 was the last value input? Would the formulas be able to return:
0
0
50
0

You seem pretty confident, so I'm sure I must be missing something. When you have a free moment, would you mind sending me an example workbook.

I too would prefer to use cell formulas if VBA can be avoided.

Thanks.
-AB