Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
I have three data cells in my table that I need to check for data. If one of
the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
With data validation perhaps. Assuming the 3 cells in question are A1 - A3,
select them and then Data|validation|Settings|Custom and use the formula =COUNTA(A$1:A$3)<=1 Mike "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
I'm afraid data validation doesn't do it, because I have it already in use.
Cell 1 has values 1-2, cell 2 has values 3-4 and cell 3 has value 5. If I insert number into cell 2, then I would not be able to input any numbers into cells 1 and 3, and so on. "Mike H" wrote: With data validation perhaps. Assuming the 3 cells in question are A1 - A3, select them and then Data|validation|Settings|Custom and use the formula =COUNTA(A$1:A$3)<=1 Mike "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
Try this small worksheet event mcro:
Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
I couldn't get this to work at all in Excel 2000 on Windows XP pro.
Dan On Sep 26, 3:10 am, Gary''s Student wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
Where did you place the code?
It is worksheet event code. Right-click on the sheet tab and "View Code". Copy/paste into that sheet module. Gord Dibben MS Excel MVP On Thu, 27 Sep 2007 23:11:08 -0000, dan dungan wrote: I couldn't get this to work at all in Excel 2000 on Windows XP pro. Dan On Sep 26, 3:10 am, Gary''s Student wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
Hi Gord,
Running the procedure returned the Compile error, "variable not defined". So I dimmed r as range. Then it worked fine. I guess that because use Option Explicit Dan On Sep 27, 4:54 pm, Gord Dibben <gorddibbATshawDOTca wrote: Where did you place the code? It is worksheet event code. Right-click on the sheet tab and "View Code". Copy/paste into that sheet module. Gord Dibben MS Excel MVP On Thu, 27 Sep 2007 23:11:08 -0000, dan dungan wrote: I couldn't get this to work at all in Excel 2000 on Windows XP pro. Dan On Sep 26, 3:10 am, Gary''s Student wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
OK, I got this event macro to work, but now I have another problem. I need to
run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
If you have several of these triplets, how are they organized ??
We can alter/expand the code, but we need to know where the triplets are. -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: OK, I got this event macro to work, but now I have another problem. I need to run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
The triplets are organized like this:
I N S 13 13 13 17 17 17 21 21 21 25 25 25 30 30 30 37 37 37 40 40 40 43 43 43 46 46 46 52 52 52 56 56 56 60 60 60 64 64 64 67 67 67 73 73 73 76 76 76 81 81 81 85 85 85 88 88 88 96 96 96 100 100 100 109 109 109 112 112 112 116 116 116 120 120 120 Phew, let me know how to go from here. "Gary''s Student" wrote: If you have several of these triplets, how are they organized ?? We can alter/expand the code, but we need to know where the triplets are. -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: OK, I got this event macro to work, but now I have another problem. I need to run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
This code REPLACES the code I posted befo
Private Sub Worksheet_Change(ByVal Target As Range) Dim r(24) As Range Dim v As Variant Set r(0) = Range("I13,N13,S13") Set r(1) = Range("I17,N17,S17") Set r(2) = Range("I21,N21,S21") Set r(3) = Range("I25,N25,S25") Set r(4) = Range("I30,N30,S30") Set r(5) = Range("I37,N37,S37") Set r(6) = Range("I40,N40,S40") Set r(7) = Range("I43,N43,S43") Set r(8) = Range("I46,N46,S46") Set r(9) = Range("I52,N52,S52") Set r(10) = Range("I56,N56,S56") Set r(11) = Range("I60,N60,S60") Set r(12) = Range("I64,N64,S64") Set r(13) = Range("I67,N67,S67") Set r(14) = Range("I73,N73,S73") Set r(15) = Range("I76,N76,S76") Set r(16) = Range("I81,N81,S81") Set r(17) = Range("I85,N85,S85") Set r(18) = Range("I88,N88,S88") Set r(19) = Range("I96,N96,S96") Set r(20) = Range("I100,N100,S100") Set r(21) = Range("I109,N109,S109") Set r(22) = Range("I112,N112,S112") Set r(23) = Range("I116,N116,S116") Set r(24) = Range("I120,N120,S120") For i = 0 To 24 If Not Intersect(Target, r(i)) Is Nothing Then v = Target.Value Application.EnableEvents = False r(i).Clear Target.Value = v Application.EnableEvents = True Exit Sub End If Next End Sub -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: The triplets are organized like this: I N S 13 13 13 17 17 17 21 21 21 25 25 25 30 30 30 37 37 37 40 40 40 43 43 43 46 46 46 52 52 52 56 56 56 60 60 60 64 64 64 67 67 67 73 73 73 76 76 76 81 81 81 85 85 85 88 88 88 96 96 96 100 100 100 109 109 109 112 112 112 116 116 116 120 120 120 Phew, let me know how to go from here. "Gary''s Student" wrote: If you have several of these triplets, how are they organized ?? We can alter/expand the code, but we need to know where the triplets are. -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: OK, I got this event macro to work, but now I have another problem. I need to run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
Ok now it works as it should work. Thank's very much for the tips, your help
is highly appreciated. "Gary''s Student" wrote: This code REPLACES the code I posted befo Private Sub Worksheet_Change(ByVal Target As Range) Dim r(24) As Range Dim v As Variant Set r(0) = Range("I13,N13,S13") Set r(1) = Range("I17,N17,S17") Set r(2) = Range("I21,N21,S21") Set r(3) = Range("I25,N25,S25") Set r(4) = Range("I30,N30,S30") Set r(5) = Range("I37,N37,S37") Set r(6) = Range("I40,N40,S40") Set r(7) = Range("I43,N43,S43") Set r(8) = Range("I46,N46,S46") Set r(9) = Range("I52,N52,S52") Set r(10) = Range("I56,N56,S56") Set r(11) = Range("I60,N60,S60") Set r(12) = Range("I64,N64,S64") Set r(13) = Range("I67,N67,S67") Set r(14) = Range("I73,N73,S73") Set r(15) = Range("I76,N76,S76") Set r(16) = Range("I81,N81,S81") Set r(17) = Range("I85,N85,S85") Set r(18) = Range("I88,N88,S88") Set r(19) = Range("I96,N96,S96") Set r(20) = Range("I100,N100,S100") Set r(21) = Range("I109,N109,S109") Set r(22) = Range("I112,N112,S112") Set r(23) = Range("I116,N116,S116") Set r(24) = Range("I120,N120,S120") For i = 0 To 24 If Not Intersect(Target, r(i)) Is Nothing Then v = Target.Value Application.EnableEvents = False r(i).Clear Target.Value = v Application.EnableEvents = True Exit Sub End If Next End Sub -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: The triplets are organized like this: I N S 13 13 13 17 17 17 21 21 21 25 25 25 30 30 30 37 37 37 40 40 40 43 43 43 46 46 46 52 52 52 56 56 56 60 60 60 64 64 64 67 67 67 73 73 73 76 76 76 81 81 81 85 85 85 88 88 88 96 96 96 100 100 100 109 109 109 112 112 112 116 116 116 120 120 120 Phew, let me know how to go from here. "Gary''s Student" wrote: If you have several of these triplets, how are they organized ?? We can alter/expand the code, but we need to know where the triplets are. -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: OK, I got this event macro to work, but now I have another problem. I need to run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking cells for value
You are very welcome.
-- Gary''s Student - gsnu200748 "T.Lindholm" wrote: Ok now it works as it should work. Thank's very much for the tips, your help is highly appreciated. "Gary''s Student" wrote: This code REPLACES the code I posted befo Private Sub Worksheet_Change(ByVal Target As Range) Dim r(24) As Range Dim v As Variant Set r(0) = Range("I13,N13,S13") Set r(1) = Range("I17,N17,S17") Set r(2) = Range("I21,N21,S21") Set r(3) = Range("I25,N25,S25") Set r(4) = Range("I30,N30,S30") Set r(5) = Range("I37,N37,S37") Set r(6) = Range("I40,N40,S40") Set r(7) = Range("I43,N43,S43") Set r(8) = Range("I46,N46,S46") Set r(9) = Range("I52,N52,S52") Set r(10) = Range("I56,N56,S56") Set r(11) = Range("I60,N60,S60") Set r(12) = Range("I64,N64,S64") Set r(13) = Range("I67,N67,S67") Set r(14) = Range("I73,N73,S73") Set r(15) = Range("I76,N76,S76") Set r(16) = Range("I81,N81,S81") Set r(17) = Range("I85,N85,S85") Set r(18) = Range("I88,N88,S88") Set r(19) = Range("I96,N96,S96") Set r(20) = Range("I100,N100,S100") Set r(21) = Range("I109,N109,S109") Set r(22) = Range("I112,N112,S112") Set r(23) = Range("I116,N116,S116") Set r(24) = Range("I120,N120,S120") For i = 0 To 24 If Not Intersect(Target, r(i)) Is Nothing Then v = Target.Value Application.EnableEvents = False r(i).Clear Target.Value = v Application.EnableEvents = True Exit Sub End If Next End Sub -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: The triplets are organized like this: I N S 13 13 13 17 17 17 21 21 21 25 25 25 30 30 30 37 37 37 40 40 40 43 43 43 46 46 46 52 52 52 56 56 56 60 60 60 64 64 64 67 67 67 73 73 73 76 76 76 81 81 81 85 85 85 88 88 88 96 96 96 100 100 100 109 109 109 112 112 112 116 116 116 120 120 120 Phew, let me know how to go from here. "Gary''s Student" wrote: If you have several of these triplets, how are they organized ?? We can alter/expand the code, but we need to know where the triplets are. -- Gary''s Student - gsnu200748 "T.Lindholm" wrote: OK, I got this event macro to work, but now I have another problem. I need to run the same macro to multiple others rows, with the same functions. How/Where should I place the macro/code, do I have to type it for each row separately or what? "Gary''s Student" wrote: Try this small worksheet event mcro: Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Set r = Range("A1:A3") If Intersect(Target, r) Is Nothing Then Exit Sub v = Target.Value Application.EnableEvents = False r.Clear Target.Value = v Application.EnableEvents = True End Sub If you enter a value in the range A1 thru A3, the other cells in that range will be cleared. -- Gary''s Student - gsnu200747 "T.Lindholm" wrote: I have three data cells in my table that I need to check for data. If one of the three cell has data in it, the other two can not be filled with anything, somekind a pop up should be good too. I hope someone understand what I mean, and can also help me out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking two different cells | Excel Worksheet Functions | |||
Checking two cells | Excel Programming | |||
Checking range of cells for entry then checking for total | Excel Programming | |||
Checking the Cells in Sheet1 with Cells in Sheet2 and replace | Excel Worksheet Functions | |||
copying cells by checking color of the cells | Excel Programming |