Force Caps Lock
 

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Use:
data=WorksheetFunction.Upper(data)

John wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:

Use:
data=WorksheetFunction.Upper(data)

John wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Thanks JWolf for your replies, I'm very much a novice on VBA and usually
just record it. How would I construct the code to say place caps lock in
cells A1:B3 in sheet1

Thanks


"JWolf" wrote in message
...
Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:

Use:
data=WorksheetFunction.Upper(data)

John wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

To do it before printing, use this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Range("YourRangeHere").Value = UCase(Range("YourRangeHere").Value)

End Sub

The code must go in the ThisWorkbook code section since it is an even
procedure and will execute before the workbook is printed.

K

P.S. Responding to your last post (missed it the first time), you woul
use:

Sheets("Sheet1").Range("A1:B3").Value
UCase(Sheets("Sheet1").Range("A1:B3").Value

--
Message posted from http://www.ExcelForum.com

Force Caps Lock
 

Rightclick the sheet tab, choose "View Code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
On Error Resume Next
If Intersect(Target, Range("A1:B3")) _
Is Nothing Then Exit Sub
For Each C In Intersect(Target, Range("A1:B3"))
If C.Formula < UCase$(C.Formula) Then _
C.Formula = UCase$(C.Formula)
Next
End Sub

HTH. Best wishes Harald
"John" skrev i melding
...
Thanks JWolf for your replies, I'm very much a novice on VBA and usually
just record it. How would I construct the code to say place caps lock in
cells A1:B3 in sheet1

Thanks


"JWolf" wrote in message
...
Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:

Use:
data=WorksheetFunction.Upper(data)

John wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is
it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Thanks KK works great


"kkknie " wrote in message
...
To do it before printing, use this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Range("YourRangeHere").Value = UCase(Range("YourRangeHere").Value)

End Sub

The code must go in the ThisWorkbook code section since it is an event
procedure and will execute before the workbook is printed.

K

P.S. Responding to your last post (missed it the first time), you would
use:

Sheets("Sheet1").Range("A1:B3").Value =
UCase(Sheets("Sheet1").Range("A1:B3").Value)


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo cleanup
Application.EnableEvents = False
Dim MyRange As Range, Cell As Range
Set MyRange = Range("A1:B3")
For Each Cell In Target
If Union(Target, MyRange).Address = MyRange.Address Then
Target = UCase(Target)
End If
Next Cell
cleanup:
Application.EnableEvents = True
End Sub


This is a Woorksheet Module, right click on the sheet tab, select view
code and paste into the code window.


John wrote:

Thanks JWolf for your replies, I'm very much a novice on VBA and usually
just record it. How would I construct the code to say place caps lock in
cells A1:B3 in sheet1

Thanks


"JWolf" wrote in message
...

Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:


Use:
data=WorksheetFunction.Upper(data)

John wrote:


Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Just tried your code KK on the range (as opposed to a single cell - which it
worked on) and I keep getting a debug error. The following is what I've
placed in This Workbook


Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Template").Range("A9:C43").Value =
UCase(Sheets("Template").Range("A9:C43").Value)

End Sub
"kkknie " wrote in message
...
To do it before printing, use this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Range("YourRangeHere").Value = UCase(Range("YourRangeHere").Value)

End Sub

The code must go in the ThisWorkbook code section since it is an event
procedure and will execute before the workbook is printed.

K

P.S. Responding to your last post (missed it the first time), you would
use:

Sheets("Sheet1").Range("A1:B3").Value =
UCase(Sheets("Sheet1").Range("A1:B3").Value)


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.



--
Message posted from http://www.ExcelForum.com

Force Caps Lock
 

John

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on worksheet tab and "View code". Copy/paste the above.

NOTE: as written it works only only columns 1 through 8(A to H). Alter that
if needed under Target.Column 8 line.

Gord Dibben Excel MVP

On Tue, 25 May 2004 15:56:46 +0100, "John" wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Sorry for being a pain KK but I'm still getting a debug


"kkknie " wrote in message
...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

Can't get that Code to work JWolf



"JWolf" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo cleanup
Application.EnableEvents = False
Dim MyRange As Range, Cell As Range
Set MyRange = Range("A1:B3")
For Each Cell In Target
If Union(Target, MyRange).Address = MyRange.Address Then
Target = UCase(Target)
End If
Next Cell
cleanup:
Application.EnableEvents = True
End Sub


This is a Woorksheet Module, right click on the sheet tab, select view
code and paste into the code window.


John wrote:

Thanks JWolf for your replies, I'm very much a novice on VBA and usually
just record it. How would I construct the code to say place caps lock in
cells A1:B3 in sheet1

Thanks


"JWolf" wrote in message
...

Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:


Use:
data=WorksheetFunction.Upper(data)

John wrote:


Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

Doesn't work for me Harald


"Harald Staff" wrote in message
...
Rightclick the sheet tab, choose "View Code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
On Error Resume Next
If Intersect(Target, Range("A1:B3")) _
Is Nothing Then Exit Sub
For Each C In Intersect(Target, Range("A1:B3"))
If C.Formula < UCase$(C.Formula) Then _
C.Formula = UCase$(C.Formula)
Next
End Sub

HTH. Best wishes Harald
"John" skrev i melding
...
Thanks JWolf for your replies, I'm very much a novice on VBA and usually
just record it. How would I construct the code to say place caps lock in
cells A1:B3 in sheet1

Thanks


"JWolf" wrote in message
...
Sorry, forgot VBA's function:
data=UCase(data)

JWolf wrote:

Use:
data=WorksheetFunction.Upper(data)

John wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is
it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print
icon?


Thanks

Force Caps Lock
 

Gord, thanks for your response, I've tried all variants on the topic but
none have worked - I'm lost



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
John

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on worksheet tab and "View code". Copy/paste the above.

NOTE: as written it works only only columns 1 through 8(A to H). Alter
that
if needed under Target.Column 8 line.

Gord Dibben Excel MVP

On Tue, 25 May 2004 15:56:46 +0100, "John"
wrote:

Is it possible to force Caps Lock on entry via VBA? Failing that is it
possible to 'refresh' data over a certain range to Caps Lock on the
triggering of an event, that event being clicking on the print icon?


Thanks

Force Caps Lock
 

John,

Rather than posting 4 replies simply stating "it doesn't work",
you should describe why the proposed solution doesn't work. Does
nothing happen? Do you get an error? What error?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...
Sorry for being a pain KK but I'm still getting a debug


"kkknie " wrote in
message
...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should
work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

You make a valid point Chip on my posts so I'll expand

on all bar kkknie's code nothing at all happens

On kkknie's post t it hits debug which highlights the whole line. Only a
very novice user on VBA so I am unsure of what further detail this debug
will give me

What I am trying to achieve is that say, when a user clicks the print Icons
all values within a range are forced upper case.

Hope that makes it clear


"Chip Pearson" wrote in message
...
John,

Rather than posting 4 replies simply stating "it doesn't work",
you should describe why the proposed solution doesn't work. Does
nothing happen? Do you get an error? What error?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...
Sorry for being a pain KK but I'm still getting a debug


"kkknie " wrote in
message
...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should
work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

For Each r in Sheets("Template").Range("A9:C43").Value

This should be

For Each r in Sheets("Template").Range("A9:C43").Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"kkknie " wrote in
message ...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

Well, it works pretty well for me.
If you have hit the debugger and cancelled out you may need to do the
following:
In the VBA editor, hit Ctrl+g to get an Immediate window.
Type in the immediate window: Application.EnableEvents=True and hit
return. This will reset and maybe all the solutions you have tried will
work.

John wrote:
You make a valid point Chip on my posts so I'll expand

on all bar kkknie's code nothing at all happens

On kkknie's post t it hits debug which highlights the whole line. Only a
very novice user on VBA so I am unsure of what further detail this debug
will give me

What I am trying to achieve is that say, when a user clicks the print Icons
all values within a range are forced upper case.

Hope that makes it clear


"Chip Pearson" wrote in message
...

John,

Rather than posting 4 replies simply stating "it doesn't work",
you should describe why the proposed solution doesn't work. Does
nothing happen? Do you get an error? What error?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...

Sorry for being a pain KK but I'm still getting a debug


"kkknie " wrote in

message

...

I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should

work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

Thats it Chip, it works, thanks for your assistance, I'm still very much
learning


"Chip Pearson" wrote in message
...
For Each r in Sheets("Template").Range("A9:C43").Value

This should be

For Each r in Sheets("Template").Range("A9:C43").Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"kkknie " wrote in
message ...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

I've run in to a problem with the code. It converts entries such as ^001 to
1. I need it left at 001

Can I do anything?

Thanks


"John" wrote in message
...
Thats it Chip, it works, thanks for your assistance, I'm still very much
learning


"Chip Pearson" wrote in message
...
For Each r in Sheets("Template").Range("A9:C43").Value

This should be

For Each r in Sheets("Template").Range("A9:C43").Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"kkknie " wrote in
message ...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.

K


---
Message posted from http://www.ExcelForum.com/

Force Caps Lock
 

Hi John,
The macro will convert formulas to text. But your problem
with 001 being converted to 1 is because the macro
in effect reenters the data. Format the column as text
beforehand. Just occurred to me that the ^001 was
really meant to be '001 which would be text. And you
are correct, I do see an entry of '001 being converted to 1

All the more reason to format the column as text.
so that reentry will not change text to numbers.
But if you had a number the number would then be
converted to text.

General is just a default that makes assumptions as to
whether an entry is text or a number. If you want the
something specific you will have to give it the correct
format rather than rely on a default.

This has nothing to do with the macro. But the macro
will convert formulas to values.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John" wrote in message ...
I've run in to a problem with the code. It converts entries such as ^001 to
1. I need it left at 001

Can I do anything?

Thanks


"John" wrote in message
...
Thats it Chip, it works, thanks for your assistance, I'm still very much
learning


"Chip Pearson" wrote in message
...
For Each r in Sheets("Template").Range("A9:C43").Value

This should be

For Each r in Sheets("Template").Range("A9:C43").Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"kkknie " wrote in
message ...
I was afraid that wouldn't work. Should have tested (duh).

Modify to this:

Dim r as Range

For Each r in Sheets("Template").Range("A9:C43").Value
r.Value = UCase(r.Value)
Next

This will loop through the cells individually and should work.

K


---
Message posted from http://www.ExcelForum.com/