Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
Hi All I use this code to make all character entered in the range upper case : Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("B1:B26")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub I'd also like to restrict these letters to "W" , "L" or "D". Can someone help with amending the code so that only these letters can be entered? It would be best if no error message were generated , juts a blank cell when any other letter is entered. Grateful for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
Hi Colin,
Am Mon, 14 Oct 2013 22:13:28 +0100 schrieb Colin Hayes: I'd also like to restrict these letters to "W" , "L" or "D". try: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Intersect(Target, Range("B1:B26")) Is _ Nothing Then Exit Sub Select Case Target.Value Case "w", "W", "l", "L", "d", "D" Target = UCase(Target) Case Else Target = "" End Select Application.EnableEvents = True End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
Why not use Data Validation?
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
In article , Claus Busch
writes Hi Colin, Am Mon, 14 Oct 2013 22:13:28 +0100 schrieb Colin Hayes: I'd also like to restrict these letters to "W" , "L" or "D". try: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Intersect(Target, Range("B1:B26")) Is _ Nothing Then Exit Sub Select Case Target.Value Case "w", "W", "l", "L", "d", "D" Target = UCase(Target) Case Else Target = "" End Select Application.EnableEvents = True End Sub Regards Claus B. Hi Claus OK thanks very much for this. It works really well and does the job exactly when opened initially. Curiously though , it seems to lose focus after other activity on the sheet. If I make entries in the range after entries to other cells in the sheet , it stops working. Other letters are then allowed. This is strange. It only works again after shutting the wb and re-opening. It seems to need a refresh. Grateful as always for your time and expertise. Best Wishes Colin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
Hi Colin,
Am Tue, 15 Oct 2013 03:01:46 +0100 schrieb Colin Hayes: Curiously though , it seems to lose focus after other activity on the sheet. it seems you desabled events. Change the code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:B26")) Is Nothing _ Or Target.Count 1 Then Exit Sub Select Case Target.Value Case "w", "W", "l", "L", "d", "D" Target = UCase(Target) Case Else Target.ClearContents End Select End Sub and create a standard module and put in following code and run it: Sub test() With Application .EnableEvents = True .ScreenUpdating = True End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
In article , Claus Busch
writes Hi Colin, Am Tue, 15 Oct 2013 03:01:46 +0100 schrieb Colin Hayes: Curiously though , it seems to lose focus after other activity on the sheet. it seems you desabled events. Hi Claus Yes , thank you. That fixed it. It's working perfectly now. ^_^ Best Wishes Colin Change the code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:B26")) Is Nothing _ Or Target.Count 1 Then Exit Sub Select Case Target.Value Case "w", "W", "l", "L", "d", "D" Target = UCase(Target) Case Else Target.ClearContents End Select End Sub and create a standard module and put in following code and run it: Sub test() With Application .EnableEvents = True .ScreenUpdating = True End With End Sub Regards Claus B. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
Hi Colin,
Am Tue, 15 Oct 2013 14:43:07 +0100 schrieb Colin Hayes: Yes , thank you. That fixed it. It's working perfectly now. it is useless to use ScreenUpdatimg while changing only one cell in time. But when you use it you have to take care the it will set again to true if you run into an error. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Restricting Cell entry to certain letters
In article , Claus Busch
writes Hi Colin, Am Tue, 15 Oct 2013 14:43:07 +0100 schrieb Colin Hayes: Yes , thank you. That fixed it. It's working perfectly now. it is useless to use ScreenUpdatimg while changing only one cell in time. But when you use it you have to take care the it will set again to true if you run into an error. Regards Claus B. HI Claus Yes , I see the issue. As I'm usually making one entry at a time , it may not be necessary to run it. I've actually got a similar question about some other code which I've just posted called 'Event recognition'. Maybe you can help here too. Thanks again. Best Wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restricting entry | Excel Worksheet Functions | |||
restricting entry into a cell based on entry to a previous cell | New Users to Excel | |||
Restricting data entry to A-Z a-z 0-9 | Excel Discussion (Misc queries) | |||
Restricting entry in B1 on the basis of entry in A1 | Excel Worksheet Functions | |||
Restricting entry in B1 on the basis of entry in A1 | Excel Worksheet Functions |