Help....

[John Petty]

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 Wilson]

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 Wilson]

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 Autpen()
instead of
Private Sub WorkBook_Open()

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

John

 

[John Wilson]

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

 

[John Petty]

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