Limit data entre!
 

Right i have two colums of data C and F. All i want to do is limit data to
only one of the colums. For example if someone types £3.00 in C19, then F 19
would be blocked.

Limit data entre!
 

One way


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C:C,F:F" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 3 Then
If .Offset(0, 3).Value < "" Then .Offset(0, 3).Select
Else
If .Offset(0, -3).Value < "" Then .Offset(0, -3).Select
End If
End With
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

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Scott_goddard" wrote in message
...
Right i have two colums of data C and F. All i want to do is limit data
to
only one of the colums. For example if someone types £3.00 in C19, then F
19
would be blocked.

Limit data entre!
 

Right i have two colums of data C and F. All i want to do is limit data
to
only one of the colums. For example if someone types £3.00 in C19, then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick

Limit data entre!
 

Bob,

Thanks i like this verision. Now i would like to protect some of the sheet,
and only allow them to enter data in some of the cells. Ihave tried several
ways but its not work, does your macro stop it.

I would like them only to be able to enter data int colum C, F and rows 1 - 6

"Bob Phillips" wrote:

One way


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C:C,F:F" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 3 Then
If .Offset(0, 3).Value < "" Then .Offset(0, 3).Select
Else
If .Offset(0, -3).Value < "" Then .Offset(0, -3).Select
End If
End With
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

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Scott_goddard" wrote in message
...
Right i have two colums of data C and F. All i want to do is limit data
to
only one of the colums. For example if someone types £3.00 in C19, then F
19
would be blocked.

Limit data entre!
 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If .Column = 3 Or .Column = 6 Then
If .Row 6 Then
.Value = ""
.Offset(6 - .Row, 0).Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C:C,F:F" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 3 Then
If .Offset(0, 3).Value < "" Then .Offset(0, 3).Select
Else
If .Offset(0, -3).Value < "" Then .Offset(0, -3).Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Scott_goddard" wrote in message
...
Bob,

Thanks i like this verision. Now i would like to protect some of the
sheet,
and only allow them to enter data in some of the cells. Ihave tried
several
ways but its not work, does your macro stop it.

I would like them only to be able to enter data int colum C, F and rows
1 - 6

"Bob Phillips" wrote:

One way


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C:C,F:F" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 3 Then
If .Offset(0, 3).Value < "" Then .Offset(0, 3).Select
Else
If .Offset(0, -3).Value < "" Then .Offset(0, -3).Select
End If
End With
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

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Scott_goddard" wrote in message
...
Right i have two colums of data C and F. All i want to do is limit
data
to
only one of the colums. For example if someone types £3.00 in C19,
then F
19
would be blocked.

Limit data entre!
 

Hi Rick. Nice idea but it did nto work. Any idea's?

"Rick Rothstein (MVP - VB)" wrote:

Right i have two colums of data C and F. All i want to do is limit data
to
only one of the colums. For example if someone types £3.00 in C19, then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick

Limit data entre!
 

You are not doing it for the entire column then, are you? If not, change the
cell reference in the two formulas to the first cell in the range you have
selected. That is, if your selected range is C4:C100, then put this in
Custom formula area of the Data Validation screen...

=LEN(F4)=0

and make the similar change to F4:F100's range. The method does work because
I tried it before posting it the first time and now again.

Rick


"Scott_goddard" wrote in message
...
Hi Rick. Nice idea but it did nto work. Any idea's?

"Rick Rothstein (MVP - VB)" wrote:

Right i have two colums of data C and F. All i want to do is limit
data
to
only one of the colums. For example if someone types £3.00 in C19,
then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick

Limit data entre!
 

"did not work" means what?

Following Rick's instructions I was able to produce a restriction in C and F if
either column had a value.

If you want to restrict just column F leave out the first step where you select
column C

You may also want to set up an input message and/or an error message that
describes the restriction.


Gord Dibben MS Excel MVP


On Thu, 9 Aug 2007 03:16:01 -0700, Scott_goddard
wrote:

Hi Rick. Nice idea but it did nto work. Any idea's?

"Rick Rothstein (MVP - VB)" wrote:

Right i have two colums of data C and F. All i want to do is limit data
to
only one of the colums. For example if someone types £3.00 in C19, then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick

Limit data entre!
 

Sorry Rick it does work - sort of. If i open a blank sheet and input the
fomula t works fine. When i put it in to my spread sheet with if staements
etc, it sort of works. The cell ref five cells above its self. So if you
put some thing into F10 then it will not allow you to input into C5. ANy
ideas why?

"Rick Rothstein (MVP - VB)" wrote:

You are not doing it for the entire column then, are you? If not, change the
cell reference in the two formulas to the first cell in the range you have
selected. That is, if your selected range is C4:C100, then put this in
Custom formula area of the Data Validation screen...

=LEN(F4)=0

and make the similar change to F4:F100's range. The method does work because
I tried it before posting it the first time and now again.

Rick


"Scott_goddard" wrote in message
...
Hi Rick. Nice idea but it did nto work. Any idea's?

"Rick Rothstein (MVP - VB)" wrote:

Right i have two colums of data C and F. All i want to do is limit
data
to
only one of the colums. For example if someone types £3.00 in C19,
then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick

Limit data entre!
 

You are going to have to give us more information about your set up so that
we can duplicate what you have in order to be able to test it out. What cell
range in Column C and what cell range in Column F are you trying to create
the relationship for? Now, highlight the range you just told me about for
Column C and tell me the Data Validation formula you put in for it. Do not
do this from memory... actually check it out... remember, we are trying to
debug a problem. Okay, now highlight the range you just told me about for
Column F and tell me the Data Validation formula you put in for it. Finally,
explain what you meant when you said that you put my solution into your
spreadsheet "with IF statements, etc.". The two ranges you are reporting to
me above should all be empty... they are for values that will be typed in...
there should be no IF, etc. in them at all.

Rick


"Scott_goddard" wrote in message
...
Sorry Rick it does work - sort of. If i open a blank sheet and input the
fomula t works fine. When i put it in to my spread sheet with if
staements
etc, it sort of works. The cell ref five cells above its self. So if you
put some thing into F10 then it will not allow you to input into C5. ANy
ideas why?

"Rick Rothstein (MVP - VB)" wrote:

You are not doing it for the entire column then, are you? If not, change
the
cell reference in the two formulas to the first cell in the range you
have
selected. That is, if your selected range is C4:C100, then put this in
Custom formula area of the Data Validation screen...

=LEN(F4)=0

and make the similar change to F4:F100's range. The method does work
because
I tried it before posting it the first time and now again.

Rick


"Scott_goddard" wrote in message
...
Hi Rick. Nice idea but it did nto work. Any idea's?

"Rick Rothstein (MVP - VB)" wrote:

Right i have two colums of data C and F. All i want to do is limit
data
to
only one of the colums. For example if someone types £3.00 in C19,
then F
19
would be blocked.

Select all of Column C... click Data/Data Validation from Excel's
menu...
select Custom from the Allow drop-down and put this...

=LEN(F1)=0

in the Formula field and leave Ignore Blank checked.

Next, select all of Column F... click Data/Data Validation from
Excel's
menu... select Custom from the Allow drop-down and put this...

=LEN(C1)=0

in the Formula field and leave Ignore Blank checked.

For a given row, either C or F, but not both, can have an entry.

Rick