Validation of 2 rows of cells, only 'X' or 'x' allowed.

H

Herrie

Hello,

After roaming through the Search resluts on "Validation" I still have not
found a solution to my question.

I have a worksheet, that will be sent to a list of volunteers, who can mark
theu availability on this sheet by putting a "x" or "x" (a cross) in 2 rows
of cells.
(These rows are weekdays, morning/afternoon/evening)

I ONLY want an "X" (or "x") in these 2 x 21 cells.

I tried this snipet

[snippet]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19" Then
Debug.Print "oke"
Application.EnableEvents = False
Target.Text = Trim(Target.Value)
If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True

End Sub
[/snippet]
on the first cell (D19) but nothing happens, no msgbox, nothing....

Where do I go wrong?

Any suggestions?

YT

Harry
 
R

Roger Govier

Hi Herrie

I suspect that Application.EnableEvents is set to false, as you probably
crashed after switching it off.

In the Immediate window, type Application.EnableEvents followed by return

You aslo have an End If missing (from your first If statement)
To cover all of the cells in your range, use something like

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Range("D19:J22")' Change to suit

If Not Intersect(Target, myRng) Is Nothing Then
Debug.Print "oke"
Application.EnableEvents = False

If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If

End If
Application.EnableEvents = True

End Sub
 
B

Bernie Deitrick

Herrie,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("$D$19:X20")) Is Nothing Then Exit Sub
Debug.Print "oke"
Application.EnableEvents = False
If UCase(Trim(Target.Value)) <> "X" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True
End Sub


You can do the same thing with data validation....

HTH,
Bernie
MS Excel MVP
 
R

Rick Rothstein \(MVP - VB\)

Instead of warning the user to put an X in the cell within your ranges, why
not just convert the entry to an X for them...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

Here I assumed the two rows were 19 and 23 and the 21 columns covered
Columns D thru Y.

Rick
 
H

Herrie

Rick,

I cannot simply convber 'any' value into an 'X', since I don't know WHAT the
user wanted to fill in.

This sheet is part of a bigger thing. The next step I'm workin on is Filling
the 'Master' with the availabilities from the users sheets.
I'm still working on that.

I think of using a reference to the users sheet. VLookup
For this I'm looking for a trick to 'build' the sheetname from a given
cellvalue in the Master.

Better suggestions would be welcome.

(It is a sort of planning for volunteers participating in a cultural
festival www.culturanova.nl)

CXell "A2" has "1002" as value, the user's sheet name to which the
references will point will be '1002.xls' + cell addresses.


Rick Rothstein (MVP - VB) said:
Instead of warning the user to put an X in the cell within your ranges, why
not just convert the entry to an X for them...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

Here I assumed the two rows were 19 and 23 and the 21 columns covered
Columns D thru Y.

Rick


Herrie said:
Hello,

After roaming through the Search resluts on "Validation" I still have not
found a solution to my question.

I have a worksheet, that will be sent to a list of volunteers, who can
mark
theu availability on this sheet by putting a "x" or "x" (a cross) in 2
rows
of cells.
(These rows are weekdays, morning/afternoon/evening)

I ONLY want an "X" (or "x") in these 2 x 21 cells.

I tried this snipet

[snippet]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19" Then
Debug.Print "oke"
Application.EnableEvents = False
Target.Text = Trim(Target.Value)
If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True

End Sub
[/snippet]
on the first cell (D19) but nothing happens, no msgbox, nothing....

Where do I go wrong?

Any suggestions?

YT

Harry
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

The code I posted will ONLY change the values typed into the range YOU
SPECIFY to an "X", no other cells will be affected. In the sample code I
posted (repeated here so you don't have to look it up)...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

look at the If..Then statement... only cells IN row 19 or 23 (I had to guess
at the two rows you wanted this functionality for as you didn't mention them
in your postings) and only if they are IN or BETWEEN columns D and Y (again,
I had to guess at the columns to have this functionality) will the typed in
entry be changed to an "X". Stated another way, If the user types anything
into D19:Y19 or into D23:Y23, that entry will be changed to an "X"...
anything else the user types in will stay just as they typed it. Simply
change the limits in the If..Then statement to match your actual conditions.

Rick


Herrie said:
Rick,

I cannot simply convber 'any' value into an 'X', since I don't know WHAT
the
user wanted to fill in.

This sheet is part of a bigger thing. The next step I'm workin on is
Filling
the 'Master' with the availabilities from the users sheets.
I'm still working on that.

I think of using a reference to the users sheet. VLookup
For this I'm looking for a trick to 'build' the sheetname from a given
cellvalue in the Master.

Better suggestions would be welcome.

(It is a sort of planning for volunteers participating in a cultural
festival www.culturanova.nl)

CXell "A2" has "1002" as value, the user's sheet name to which the
references will point will be '1002.xls' + cell addresses.


Rick Rothstein (MVP - VB) said:
Instead of warning the user to put an X in the cell within your ranges,
why
not just convert the entry to an X for them...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

Here I assumed the two rows were 19 and 23 and the 21 columns covered
Columns D thru Y.

Rick


Herrie said:
Hello,

After roaming through the Search resluts on "Validation" I still have
not
found a solution to my question.

I have a worksheet, that will be sent to a list of volunteers, who can
mark
theu availability on this sheet by putting a "x" or "x" (a cross) in 2
rows
of cells.
(These rows are weekdays, morning/afternoon/evening)

I ONLY want an "X" (or "x") in these 2 x 21 cells.

I tried this snipet

[snippet]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19" Then
Debug.Print "oke"
Application.EnableEvents = False
Target.Text = Trim(Target.Value)
If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True

End Sub
[/snippet]
on the first cell (D19) but nothing happens, no msgbox, nothing....

Where do I go wrong?

Any suggestions?

YT

Harry
Click to expand...
Click to expand...
Click to expand...
 
H

Herrie

Rick,

All clear! but I have to 'read out' te values... so only 'x' = valid there


Rick Rothstein (MVP - VB) said:
The code I posted will ONLY change the values typed into the range YOU
SPECIFY to an "X", no other cells will be affected. In the sample code I
posted (repeated here so you don't have to look it up)...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

look at the If..Then statement... only cells IN row 19 or 23 (I had to guess
at the two rows you wanted this functionality for as you didn't mention them
in your postings) and only if they are IN or BETWEEN columns D and Y (again,
I had to guess at the columns to have this functionality) will the typed in
entry be changed to an "X". Stated another way, If the user types anything
into D19:Y19 or into D23:Y23, that entry will be changed to an "X"...
anything else the user types in will stay just as they typed it. Simply
change the limits in the If..Then statement to match your actual conditions.

Rick


Herrie said:
Rick,

I cannot simply convber 'any' value into an 'X', since I don't know WHAT
the
user wanted to fill in.

This sheet is part of a bigger thing. The next step I'm workin on is
Filling
the 'Master' with the availabilities from the users sheets.
I'm still working on that.

I think of using a reference to the users sheet. VLookup
For this I'm looking for a trick to 'build' the sheetname from a given
cellvalue in the Master.

Better suggestions would be welcome.

(It is a sort of planning for volunteers participating in a cultural
festival www.culturanova.nl)

CXell "A2" has "1002" as value, the user's sheet name to which the
references will point will be '1002.xls' + cell addresses.


Rick Rothstein (MVP - VB) said:
Instead of warning the user to put an X in the cell within your ranges,
why
not just convert the entry to an X for them...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

Here I assumed the two rows were 19 and 23 and the 21 columns covered
Columns D thru Y.

Rick


Hello,

After roaming through the Search resluts on "Validation" I still have
not
found a solution to my question.

I have a worksheet, that will be sent to a list of volunteers, who can
mark
theu availability on this sheet by putting a "x" or "x" (a cross) in 2
rows
of cells.
(These rows are weekdays, morning/afternoon/evening)

I ONLY want an "X" (or "x") in these 2 x 21 cells.

I tried this snipet

[snippet]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19" Then
Debug.Print "oke"
Application.EnableEvents = False
Target.Text = Trim(Target.Value)
If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True

End Sub
[/snippet]
on the first cell (D19) but nothing happens, no msgbox, nothing....

Where do I go wrong?

Any suggestions?

YT

Harry
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Your original post said "I ONLY want an "X" (or "x") in these 2 x 21 cells",
so I gave you "X"... if you want "x", then change the upper case X to a
lower case one inside the Left function call in the last line inside the
If..Then block of code.

Rick


Herrie said:
Rick,

All clear! but I have to 'read out' te values... so only 'x' = valid there


Rick Rothstein (MVP - VB) said:
The code I posted will ONLY change the values typed into the range YOU
SPECIFY to an "X", no other cells will be affected. In the sample code I
posted (repeated here so you don't have to look it up)...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

look at the If..Then statement... only cells IN row 19 or 23 (I had to
guess
at the two rows you wanted this functionality for as you didn't mention
them
in your postings) and only if they are IN or BETWEEN columns D and Y
(again,
I had to guess at the columns to have this functionality) will the typed
in
entry be changed to an "X". Stated another way, If the user types
anything
into D19:Y19 or into D23:Y23, that entry will be changed to an "X"...
anything else the user types in will stay just as they typed it. Simply
change the limits in the If..Then statement to match your actual
conditions.

Rick


Herrie said:
Rick,

I cannot simply convber 'any' value into an 'X', since I don't know
WHAT
the
user wanted to fill in.

This sheet is part of a bigger thing. The next step I'm workin on is
Filling
the 'Master' with the availabilities from the users sheets.
I'm still working on that.

I think of using a reference to the users sheet. VLookup
For this I'm looking for a trick to 'build' the sheetname from a given
cellvalue in the Master.

Better suggestions would be welcome.

(It is a sort of planning for volunteers participating in a cultural
festival www.culturanova.nl)

CXell "A2" has "1002" as value, the user's sheet name to which the
references will point will be '1002.xls' + cell addresses.


:

Instead of warning the user to put an X in the cell within your
ranges,
why
not just convert the entry to an X for them...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Column >= 4 And Target.Column <= 25 And _
(Target.Row = 19 Or Target.Row = 23) Then
Target.Value = Left("X", -CBool(Len(Trim(Target.Value))))
End If
Whoops:
Application.EnableEvents = True
End Sub

Here I assumed the two rows were 19 and 23 and the 21 columns covered
Columns D thru Y.

Rick


Hello,

After roaming through the Search resluts on "Validation" I still
have
not
found a solution to my question.

I have a worksheet, that will be sent to a list of volunteers, who
can
mark
theu availability on this sheet by putting a "x" or "x" (a cross) in
2
rows
of cells.
(These rows are weekdays, morning/afternoon/evening)

I ONLY want an "X" (or "x") in these 2 x 21 cells.

I tried this snipet

[snippet]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19" Then
Debug.Print "oke"
Application.EnableEvents = False
Target.Text = Trim(Target.Value)
If Target.Text <> "X" And Target.Value <> "x" Then
MsgBox "Alleen aankruisen met een 'X' of een 'x'!"
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True

End Sub
[/snippet]
on the first cell (D19) but nothing happens, no msgbox, nothing....

Where do I go wrong?

Any suggestions?

YT

Harry
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change column values depending upon the selection 3
question data validation and delete/clear behavior 0
Data Validation : Allow only "X" or blank 3
Error--"try selecting only entire rows, entire columns, or justgroups of cells" 0
sort 2 or more rows of cells linked to one row of data 1

Top