I work a 4 week rota which entails 4 different duties, any 5 days from seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of 'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of 'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

An alternative approach is to use the CAPS LOCK key. This is usually located
just above the shift key.
--
Gary's Student


"Sandy Mann" wrote:

Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of 'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

Oh geez. I have two shift keys... Now what? <g

(Sorry I just couldn't help myself)
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

An alternative approach is to use the CAPS LOCK key. This is usually located
just above the shift key.
--
Gary's Student


"Sandy Mann" wrote:

Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of 'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code. If you
dont' turn off Events, the _Change code will make a change, which will call
_Change, which will make a change, which will call _Change, and on and on
and on until VBA just quits (earlier versions of Excel would blow up with an
"out of stack space" error). Also, you'll want to test whether Target has a
Formula in it. If it does, you don't want to overwrite the formula with the
value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.HasFormula = False Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbUpperCase)
Application.EnableEvents = True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Sandy Mann" wrote in message
...
Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of
'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

I have no CAPS LOCK key. For years, I have been removing CAPS LOCK, NUM
LOCK, SCRL LOCK, and F1 from my keyboards. Utterly useless keys that just
get in the way of real work.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Thomlinson" wrote in message
...
Oh geez. I have two shift keys... Now what? <g

(Sorry I just couldn't help myself)
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

An alternative approach is to use the CAPS LOCK key. This is usually
located
just above the shift key.
--
Gary's Student


"Sandy Mann" wrote:

Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which
duty
numbers were worked on what days. The duty number is in the form of
'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it
will be
automatically be entered in upper case format.
Many thanx
--
Al

"Chip Pearson" wrote in message
...
"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code.

I bow to your greater knowledge of Excel Chip. I initially included the
EnableEvents lines in my code but found that when I commented them out, ( in
XL97), the code ran just fine without them. I therefore assumed that Excel
did not see * ab1 * changing to * AB1 * as being a change just as it treats
="Sandy" and ="sandy" as the same text.

Perhaps it is different in other vesions of XL?

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Thanx everyone for the help. All is working great. I should have mentioned
that about about 40 people will be using this form so shift key etc. is not
really an option as some users know even less than I do.
Once again thanx for all your help
--
Al


"Chip Pearson" wrote:

"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code. If you
dont' turn off Events, the _Change code will make a change, which will call
_Change, which will make a change, which will call _Change, and on and on
and on until VBA just quits (earlier versions of Excel would blow up with an
"out of stack space" error). Also, you'll want to test whether Target has a
Formula in it. If it does, you don't want to overwrite the formula with the
value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.HasFormula = False Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbUpperCase)
Application.EnableEvents = True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Sandy Mann" wrote in message
...
Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of
'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al

Perhaps I should explain further because the code that I posted is useless
in any normal spreadsheet on its own as written. After pressing the *Send*
button I realised that I should have included an Intersect Target/Range to
exit the Sub if it the entry was not in the specified range. However, when
I saw Gary's Student's suggestion of simply using the Caps Lock <smack on
head I felt it was like the elephant in the room that no one was talking
about!


--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
"Chip Pearson" wrote in message
...
"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code.

I bow to your greater knowledge of Excel Chip. I initially included the
EnableEvents lines in my code but found that when I commented them out,
( in XL97), the code ran just fine without them. I therefore assumed
that Excel did not see * ab1 * changing to * AB1 * as being a change just
as it treats ="Sandy" and ="sandy" as the same text.

Perhaps it is different in other vesions of XL?

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

XL97), the code ran just fine without them.

It didn't run just fine. It ran in a loop until VBA decided to kill it. Use
code like the following to see how many times it loops until VBA kills the
processing. In my Excel 2003 SP2, it runs 232 times before VBA terminates
it. It may appeared to have run just fine, but only because your computer is
fast enough that you didn't see a slowdown due to the loop. The code below
will illustrate what's really happening.

Private Sub Worksheet_Change(ByVal Target As Range)
Static N As Long
If Target.Address = "$A$1" Then
' use A1 to reset N
N = 0
End If
N = N + 1
Debug.Print N
Target.Value = UCase(Target.Value)
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Sandy Mann" wrote in message
...
"Chip Pearson" wrote in message
...
"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code.

I bow to your greater knowledge of Excel Chip. I initially included the
EnableEvents lines in my code but found that when I commented them out,
( in XL97), the code ran just fine without them. I therefore assumed
that Excel did not see * ab1 * changing to * AB1 * as being a change just
as it treats ="Sandy" and ="sandy" as the same text.

Perhaps it is different in other vesions of XL?

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

I bow once again Chip.

When I run your code as written, ( in XL97), I get 199 1's. If I comment
out the IF statements then I get 146 to 344 - 198 numbers, ( I assume that
is why you were resetting N in the IF statement). If I run it again I get
490 to 688 - again a gap of 145 followed by 198 numbers. I have spent the
the time since I read your post trying to figure out why the gap but it is
beyond me.

I also found out by experimenting that even changing the line
Target.Value = UCase(Target.Value)
to
Target.Value = Target.Value

causes the event macro to fire again so it seems that it is not so much a
Worksheet_Change as a Worksheet_Entry macro.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Chip Pearson" wrote in message
...

XL97), the code ran just fine without them.

It didn't run just fine. It ran in a loop until VBA decided to kill it.
Use code like the following to see how many times it loops until VBA kills
the processing. In my Excel 2003 SP2, it runs 232 times before VBA
terminates it. It may appeared to have run just fine, but only because
your computer is fast enough that you didn't see a slowdown due to the
loop. The code below will illustrate what's really happening.

Private Sub Worksheet_Change(ByVal Target As Range)
Static N As Long
If Target.Address = "$A$1" Then
' use A1 to reset N
N = 0
End If
N = N + 1
Debug.Print N
Target.Value = UCase(Target.Value)
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Sandy Mann" wrote in message
...
"Chip Pearson" wrote in message
...
"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code.

I bow to your greater knowledge of Excel Chip. I initially included the
EnableEvents lines in my code but found that when I commented them out,
( in XL97), the code ran just fine without them. I therefore assumed
that Excel did not see * ab1 * changing to * AB1 * as being a change just
as it treats ="Sandy" and ="sandy" as the same text.

Perhaps it is different in other vesions of XL?

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub


"gramps" wrote:

I work a 4 week rota which entails 4 different duties, any 5 days from seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of 'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al