Limit data entre!

S

Scott_goddard

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.
 
B

Bob Phillips

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)
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
S

Scott_goddard

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
 
B

Bob Phillips

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)
 
R

Rick Rothstein \(MVP - VB\)

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
 
G

Gord Dibben

"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
 
S

Scott_goddard

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?
 
R

Rick Rothstein \(MVP - VB\)

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
 
Top