December 25, 2006

Merry Christmas

Merry Christmas folks! Hoping it's a good one!

Here's a little something I made last week.

Christmas Tree

Enable macros and do what Santa says. If you look at the code, you will see there is a way to exit or continue looping using Application.EnableCancelKey (push the Esc key to make it work)

Take care, have fun. See you again soon ;-)


Posted by andrewe at 12:16

December 19, 2006

X marks the spot

There are times when you want to prevent a user from pushing the "X" button on a userform. Here are two ways that you can do it.

Disable the X button
Straight from the Help files - use this variation to make sure the "cancel" button is used instead.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then
        MsgBox "Please use the cancel button.", , Me.Caption
        Cancel = 1
    End If
End Sub

The result,

Not bad, but wouldn't it be easier just to hide the X button in the first place?

Hide the X button
Place this code in a standard module,
Public Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Public Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Const GWL_STYLE = -16
Public Const WS_SYSMENU = &H80000

Then in the userform itself,
Private Sub UserForm_Initialize()
    Dim hWnd As Long, lStyle As Long
    If Val(Application.Version) >= 9 Then
        hWnd = FindWindow("ThunderDFrame", Me.Caption)
        hWnd = FindWindow("ThunderXFrame", Me.Caption)
    End If
    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
End Sub

The result,

Looks much better :-)

Oh, by the way, the professional version of my utilities is READY but we are working on some advertising first. For the time being, I can only offer the latest version of my New Book Navigator, now with Help files ;-) The link is here.  
Posted by andrewe at 19:54