|
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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 -- Regards Roger Govier "Herrie" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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 "Herrie" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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)" wrote: 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" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
Rick,
All clear! but I have to 'read out' te values... so only 'x' = valid there "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
Validation of 2 rows of cells, only 'X' or 'x' allowed.
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" wrote in message ... Rick, All clear! but I have to 'read out' te values... so only 'x' = valid there "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com