Force Caps Lock

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
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
 
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
 
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.
 
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
 
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.
 
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
 
Can't get that Code to work JWolf



JWolf said:
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.
Click to expand...
 
Doesn't work for me Harald


Harald Staff said:
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
Click to expand...
 
Gord, thanks for your response, I've tried all variants on the topic but
none have worked - I'm lost



Gord Dibben said:
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
Click to expand...
 
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 said:
Sorry for being a pain KK but I'm still getting a debug
Click to expand...
 
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 said:
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
Click to expand...
 
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
 
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.
 
Thats it Chip, it works, thanks for your assistance, I'm still very much
learning


Chip Pearson said:
For Each r in Sheets("Template").Range("A9:C43").Value
Click to expand...

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
Click to expand...
 
Back
Top