Force Caps Lock

[John]

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

 

[JWolf]

Use:
data=WorksheetFunction.Upper(data)

 

[JWolf]

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

Use:
data=WorksheetFunction.Upper(data)

 

[John]

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

 

[kkknie]

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

 

[Harald Staff]

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]

Thanks KK works great

 

[JWolf]

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]

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]

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.

 

[Gord Dibben]

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

 

[John]

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

 

[John]

Can't get that Code to work JWolf

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]

Doesn't work for me Harald

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]

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

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

 

[Chip Pearson]

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

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

 

[John]

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

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

 

[Chip Pearson]

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

 

[JWolf]

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]

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

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