Data Validation

  • Thread starter Thread starter Lorraine
  • Start date Start date
L

Lorraine

I have a workbook with 3 worksheets.
- If data is entered in D16:P57 on Sheet 1, I want D16:P57
on Sheets 2 and 3 disabled so they can't be typed in.
- If data is entered in D16:P57 on Sheet 2, I want
D16:P57 on Sheets 1 and 3 disabled so they can't be typed
in.
- If data is entered in D16:P57 on Sheet 3, I want D16:P57
on Sheets 1 and 2 disabled so they can't be typed in.

Is it possible to do this?

There was a similar post recently for disabling one cell.
I've tried to follow those instructions but it's not
working with multiple rows and columns. Some cells I can
type in and some I can't, even though nothing is typed in
the corresponding cell on the other two worksheets.
 
Lorraine,
There's going to be some VB involved i think, but you
could just color the cells with conditional formatting to
advise the users that there is data elsewhere.
in sheet1 starting at D1:end of data, your conditional
formatting would look like this:
=if(or(sheet2!d1<>"",sheet3!d1<>""),true,false) then
format the cell as red, on sheet two and three you would
just make reference to the other two pages accordingly.
it won't stop the user from putting something in there,
but it would be easily understood that they shouldn't put
data in there if the cell is red already.
HTH
Kevin M
 
I really don't want colour formatting. This report is
printed and forwarded to our Head Office and I don't want
to confuse them! This was the answer for doing just one
cell, but I can't get it to work for a range of cells:
--------------
You will need to create a named range called Test
(anywhere in your workbook is fine).*

In Test put the formula:
=Sheet1!D16 & Sheet2!D16 & Sheet3!D16

Select Sheet1!D16 and from the menu choose
Data -> Validation then
Under the Settings tab choose Allow Custom and in
the Formula field put "=D16=Test" without quotes
Under the Error Alert tab enter a custom error
message if you choose or use excel's default.

Repeat for each sheet (2 and 3)

Then a user can only have 1 of either Sheet1!D16,
Sheet2!D16 or Sheet3!D16

* To create a named range from the menu select
Insert -> Name -> Define
Put in Test and set it to whichever cell you choose.
This allows the use of 1 cell in all data validation.
 
Lorraine,

A couple of questions:

1. When you say you want it for the entire range does that
mean that if someone entered something in cell D16 on
Sheet1 that Sheet2 (D16:D57) entries and Sheet3 (D16:D57)
entries are disabled or just D16 on the other sheets?

2. Are macros out of the question?

Dan E
 
1. Just D16 would be disabled on every sheet when D16 is
filled in on one of the sheets. But this applies to all
cells ranging from row D to row P (D16:P57)

2. No.

Thank you.
-----Original Message-----
Lorraine,

A couple of questions:

1. When you say you want it for the entire range does that
mean that if someone entered something in cell D16 on
Sheet1 that Sheet2 (D16:D57) entries and Sheet3 (D16:D57)
entries are disabled or just D16 on the other sheets?

2. Are macros out of the question?

Dan E

"Lorraine" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Lorraine,

Here is a sub for the workbook_sheetchange event that will
prevent duplicate entries. As of now it displays no warnings
and just deletes anything entered if an entry has been made
on a previous sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim CC As String
Application.ScreenUpdating = False
CC = Target.Address
If Target.Value = "" Then Exit Sub
'Check if your in column D if not exit sub
If Mid(CC, InStr(1, CC, "$") + 1, InStrRev(CC, "$") - InStr(1, CC, "$") - 1) <> "D" Then Exit Sub
'Check if your between rows 16 and 57 if not exit sub
If Right(CC, Len(CC) - InStrRev(CC, "$")) < 16 Or Right(CC, Len(CC) - InStrRev(CC, "$")) > 57 Then Exit Sub
CurrSheet = ActiveSheet.Name
'Pick what to do based on what sheet is active
Select Case CurrSheet
Case "Sheet1"
'your on sheet1 if sheet2 and sheet3 of the same cell are empty exit sub
If Sheet2.Range(CC).Value = "" And Sheet3.Range(CC).Value = "" Then Exit Sub
Case "Sheet2"
'your on sheet2 if sheet1 and sheet3 of the same cell are empty exit sub
If Sheet1.Range(CC).Value = "" And Sheet3.Range(CC).Value = "" Then Exit Sub
Case "Sheet3"
'your on sheet3 if sheet1 and sheet2 of the same cell are empty exit sub
If Sheet1.Range(CC).Value = "" And Sheet2.Range(CC).Value = "" Then Exit Sub
End Select
'If you've made it this far then an entry has been made for this cell empty it.
Target.Value = ""
Application.ScreenUpdating = True
End Sub

This sub should be placed in the ThisWorkbook Code window
Press Alt + F11
Expand VBAProject(Your_Book_Name.xls)
Double click on ThisWorkbook and paste the above code into
the code window

watch out for wordwrap. the only lines that aren't indented are
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim CC As String
End Sub

Dan E
 
I just noticed a slight bug with this code. In order to improve it
please add

If Target.Count > 1 Then Exit Sub

underneath the CC = Target.Address

Dan E
 
Back
Top