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.

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

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

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

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

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.

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

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