Restricting Cell entry to certain letters

[Colin Hayes]

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.

 

[Claus Busch]

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.

 

[GS]

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

 

[Colin Hayes]

Hi Colin,

Am Mon, 14 Oct 2013 22:13:28 +0100 schrieb Colin Hayes:


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

 

[Claus Busch]

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.

 

[Colin Hayes]

Hi Colin,

Am Tue, 15 Oct 2013 03:01:46 +0100 schrieb Colin Hayes:


it seems you desabled events.

Hi Claus

Yes , thank you. That fixed it. It's working perfectly now.

^_^


Best Wishes


Colin

 

[Claus Busch]

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.

 

[Colin Hayes]

Hi Colin,

Am Tue, 15 Oct 2013 14:43:07 +0100 schrieb Colin Hayes:


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