var sibErrMsg = {"invalidMail":"Please fill out valid email address","requiredField":"Please fill out required fields","invalidDateFormat":"Please fill out valid date format","invalidSMSFormat":"Please fill out valid phone number"};
var ajax_sib_front_object = {"ajax_url":"https:\/\/excel.tv\/wp-admin\/admin-ajax.php","ajax_nonce":"b9a3ab3092","flag_url":"https:\/\/excel.tv\/wp-content\/plugins\/mailin\/img\/flags\/"};
Sometimes, you’ll want to programmatically take advantage of Windows outside the capabilities readily available through Visual Basic for Applications (VBA). For example, both eliminating the “Close”-button or creating a more opaque background on a window require the use of the Windows API.
Doing this in Visual Basic isn’t too hard, but there are some distinct differences between what’s readily available to programmers in Microsoft Office’s Visual Basic (VBA) compared to its virtual twin, Visual Basic 6.0, and its smarter, object-oriented younger brother VB.net. Specifically, UserForms in VBA do not contain methods to find their Windows handle, (aka, their hWnd).
hWnd and VBA
And most window modifying APIs require the target window’s handle. Luckily, the Windows API also contains a function to find a given window’s hWnd, called FindWindow():
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
So, to find your window’s handle, you’ll do something like this (assuming you’re coding within the UserForm).
hWnd = FindWindow("ThunderDFrame", me.caption)
Here, FindWindow() takes two arguments. First, the class name of the window for which you’re searching; and second, the window’s caption. ThunderDFrame, huh?
I know, right? The UserForms in Excel are actually of the Windows class ThunderDFrame, which is the class for all UserFroms in Microsoft Office applications after 2002 (it was “ThunderXFrame” before that). I’ve looked online for a good explanation for the name but haven’t really found anything. Either way you’ll need to pass that into the first argument of FindWindow whenever you need a UserForm’s handle.
How To Disable Close Button in Excel Using VBA
Important: Always provide at least one clear and accessible way for the user to close the form safely.
Rather than interrupting the user with unnecessary message boxes after they click the close button, a cleaner and more user-friendly approach is to disable or hide the close button altogether. This ensures a smoother experience and prevents confusion. Here’s how you can achieve this with VBA.
By proactively controlling the form’s behavior, you can streamline functionality while maintaining a professional and intuitive user interface.
'//Find the userform's Window
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
'//Get the current window style
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
'//Set the new window style
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" ( _
ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Const GWL_STYLE = -16
Const WS_SYSMENU = &H80000
Private Sub UserForm_Initialize()
Dim hWnd As Long, lStyle As Long
If Val(Application.Version) >= 9 Then
hWnd = FindWindow("ThunderDFrame", Me.Caption)
Else
hWnd = FindWindow("ThunderXFrame", Me.Caption)
End If
'//Get the current window style and turn off the Close button
lStyle = GetWindowLong(hWnd, GWL_STYLE)
SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
End Sub
Bonus Tip: You can prevent the user from being able to close the window with Alt-F4 using this code as well:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub
Jordan Goldmeier is an accomplished data professional with a wealth of experience across various industries. He currently serves as a consultant at Anarchy Data, where he assists businesses in maximizing the capabilities of Excel for financial planning and analysis. Jordan is also an instructor at Full Stack Modeller and a former Adjunct Instructor in Analytics at Wake Forest University. His extensive career has seen him hold positions as the Chief Operations Officer at Excel.TV, Data Science Manager at DataKind, Data Scientist at Dealer Tire and EY, Analytics & Data Vis Developer at The Perduco Group, and Operations Research Analyst at Booz Allen Hamilton. Jordan's background in data analytics and his passion for Excel make him a valuable resource for businesses seeking to improve their data-driven decision-making processes.
var wpilFrontend = {"ajaxUrl":"\/wp-admin\/admin-ajax.php","postId":"5","postType":"post","openInternalInNewTab":"0","openExternalInNewTab":"0","disableClicks":"0","openLinksWithJS":"0","trackAllElementClicks":"0","clicksI18n":{"imageNoText":"Image in link: No Text","imageText":"Image Title: ","noText":"No Anchor Text Found"}};