I wrote this bit of code to start up a workbook with the
autocomplete disabled and the caps turned on (I think),
but I can't seem to get it to work automatically when I
open the workbook. What am I doing wrong (besides waking
up this morning.


Private Sub WorkBook_Open()
With Application
.AutoCorrect.CorrectCapsLock = True
.EnableAutoComplete = Not .EnableAutoComplete
End With
End Sub

TIA

John Petty

John,

If it's not working I might guess that you didn't place it in the
workbook module.
Double click on "ThisWorkbook" from the VBA editor and paste your
code into the window that appears on the right.

John

John Petty wrote:

I wrote this bit of code to start up a workbook with the
autocomplete disabled and the caps turned on (I think),
but I can't seem to get it to work automatically when I
open the workbook. What am I doing wrong (besides waking
up this morning.

Private Sub WorkBook_Open()
With Application
.AutoCorrect.CorrectCapsLock = True
.EnableAutoComplete = Not .EnableAutoComplete
End With
End Sub

TIA

John Petty

John,

And be sure to delete it from where you had it originally.

Another option by the way would be to leave it where it is
and rename it to
Sub Auto_Open()
instead of
Private Sub WorkBook_Open()

The Auto_Open sub will only work in a regular module.
The other code you were using has to be in the Workbook
module.

John

John Petty wrote:

I wrote this bit of code to start up a workbook with the
autocomplete disabled and the caps turned on (I think),
but I can't seem to get it to work automatically when I
open the workbook. What am I doing wrong (besides waking
up this morning.

Private Sub WorkBook_Open()
With Application
.AutoCorrect.CorrectCapsLock = True
.EnableAutoComplete = Not .EnableAutoComplete
End With
End Sub

TIA

John Petty

John,

Didn't really look critically at the code you were using
For the Caps Lock (and Num Lock), I found this:
(be careful of wordwrap)

Option Explicit

' Code from "VBA Developer's Handbook" (Sybex, 1997):
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As
Long) As Integer
Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As
Byte) As Long
Private Declare Function SetKeyboardState Lib "user32" (lppbKeyState As
Byte) As Long

Function GetCapslock() As Boolean
' Return or set the Capslock toggle
GetCapslock = CBool(GetKeyState(vbKeyCapital) And 1)
End Function

Function GetNumlock() As Boolean
' Return or set the Numlock toggle.
GetNumlock = CBool(GetKeyState(vbKeyNumlock) And 1)
End Function

Sub SetCapslock(Value As Boolean)
' Return or set the Capslock toggle.
Call SetKeyState(vbKeyCapital, Value)
End Sub

Sub SetNumlock(Value As Boolean)
' Return or set the Numlock toggle.
Call SetKeyState(vbKeyNumlock, Value)
End Sub

Private Sub SetKeyState(intKey As Integer, fTurnOn As Boolean)
Dim abytBuffer(0 To 255) As Byte
GetKeyboardState abytBuffer(0)
abytBuffer(intKey) = CByte(Abs(fTurnOn))
SetKeyboardState abytBuffer(0)
End Sub

Sub Caps_on()
If GetCapslock = False Then Call SetKeyState(vbKeyCapital, True)
End Sub

Sub Caps_Off()
If GetCapslock = True Then Call SetKeyState(vbKeyCapital, False)
End Sub

----------------
Once you place the above code in a regular module, you can just call
Caps_on or Caps_Off from your workbook_open code.

John


John Petty wrote:

Okay, Now that I have it in the right place, the
autocomplete works, but I can't seem to get the caps to
lock (all capital Letters). Is there a way that I can do
this?

-----Original Message-----
John,

And be sure to delete it from where you had it originally.

Another option by the way would be to leave it where it is
and rename it to
Sub Auto_Open()
instead of
Private Sub WorkBook_Open()

The Auto_Open sub will only work in a regular module.
The other code you were using has to be in the Workbook
module.

John

John Petty wrote:

I wrote this bit of code to start up a workbook with the
autocomplete disabled and the caps turned on (I think),
but I can't seem to get it to work automatically when I
open the workbook. What am I doing wrong (besides
waking
up this morning.

Private Sub WorkBook_Open()
With Application
.AutoCorrect.CorrectCapsLock = True
.EnableAutoComplete = Not .EnableAutoComplete
End With
End Sub

TIA

John Petty

.

Thanks a Bunch John. Between me and every MVP out there I
will eventually have an awesome application.

-----Original Message-----
John,

Didn't really look critically at the code you were using
For the Caps Lock (and Num Lock), I found this:
(be careful of wordwrap)

Option Explicit

' Code from "VBA Developer's Handbook" (Sybex, 1997):
Private Declare Function GetKeyState Lib "user32" (ByVal
nVirtKey As
Long) As Integer
Private Declare Function GetKeyboardState Lib "user32"
(pbKeyState As
Byte) As Long
Private Declare Function SetKeyboardState Lib "user32"
(lppbKeyState As
Byte) As Long

Function GetCapslock() As Boolean
' Return or set the Capslock toggle
GetCapslock = CBool(GetKeyState(vbKeyCapital) And 1)
End Function

Function GetNumlock() As Boolean
' Return or set the Numlock toggle.
GetNumlock = CBool(GetKeyState(vbKeyNumlock) And 1)
End Function

Sub SetCapslock(Value As Boolean)
' Return or set the Capslock toggle.
Call SetKeyState(vbKeyCapital, Value)
End Sub

Sub SetNumlock(Value As Boolean)
' Return or set the Numlock toggle.
Call SetKeyState(vbKeyNumlock, Value)
End Sub

Private Sub SetKeyState(intKey As Integer, fTurnOn As
Boolean)
Dim abytBuffer(0 To 255) As Byte
GetKeyboardState abytBuffer(0)
abytBuffer(intKey) = CByte(Abs(fTurnOn))
SetKeyboardState abytBuffer(0)
End Sub

Sub Caps_on()
If GetCapslock = False Then Call SetKeyState
(vbKeyCapital, True)
End Sub

Sub Caps_Off()
If GetCapslock = True Then Call SetKeyState
(vbKeyCapital, False)
End Sub

----------------
Once you place the above code in a regular module, you
can just call
Caps_on or Caps_Off from your workbook_open code.

John


John Petty wrote:

Okay, Now that I have it in the right place, the
autocomplete works, but I can't seem to get the caps to
lock (all capital Letters). Is there a way that I can
do
this?

-----Original Message-----
John,

And be sure to delete it from where you had it
originally.

Another option by the way would be to leave it where
it is
and rename it to
Sub Auto_Open()
instead of
Private Sub WorkBook_Open()

The Auto_Open sub will only work in a regular module.
The other code you were using has to be in the Workbook
module.

John

John Petty wrote:

I wrote this bit of code to start up a workbook with
the
autocomplete disabled and the caps turned on (I
think),
but I can't seem to get it to work automatically
when I
open the workbook. What am I doing wrong (besides
waking
up this morning.

Private Sub WorkBook_Open()
With Application
.AutoCorrect.CorrectCapsLock = True
.EnableAutoComplete = Not .EnableAutoComplete
End With
End Sub

TIA

John Petty

.


.