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

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

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

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

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

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

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

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

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

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/

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/

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

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

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