Excel Vba Question How Do I Take The Data In Each Row Of A Table And?

Excel Vba Question How Do I Take The Data In Each Row Of A Table And?

While working on a project recently, I thought about hiding the [X] close button which is at the top of the VBA UserForm. I managed to identify three options for achieving this. If you are thinking about the same thing, then this post will give you the answers you’re looking for. To hide, or not to hide, that is the question Before we start looking at the three options, I want to cover the most critical question of all. should we hide a UserForm’s close button? The close button is a recognized part of the Windows environment. Pretty much every application uses a cross at the top right corner to close a window. Therefore, based on their experience of other applications, all users already know what the cross should do. It doesn’t matter which country the user is in or what language t speak, it is a universally understood icon to close a window. Therefore you should be asking yourself why you want to hide the close button at all. With this in mind, I would say that generally the close button should not be hidden or disabled. However, there are some circumstances where we may want to break this general rule. One such example is a progress bar. If the user closes a progress bar, t may not know when the macro has finished running, or how long is left. Therefore, it might be useful to hide the close button in this scenario. By default, the close button will unload a UserForm, but it can beadopted to have custom close procedure. As we can control this functionality, it may be better to control it rather than hide or disable it. About Windows API codes Of the three solutions, two of them rely on Windows API calls. These are where the VBA code makes use of functions which are not part of Excel, but part of the main Windows application. As this is complicated stuff, I will not go through it in detail here. But the key points to note about the Windows API codes are. T will not work on a Mac, only on Windows. So if designing for Windows and Mac, you will need to identify which platform is used and adapt the code accordingly. The code sections marked “Include this code at the top of the module” must be included before any other Subs or Functions are declared, and must be below the Option Explicit statement (if you have one). 3. Options for disabling/hiding a UserForm close button If you are still intent on hiding or disabling the close button, then read on to understand the three options. (1) The unobvious disable with a message box This first option is the worst of the three. It does not disable or hide the close button, but prevents the user from using it to close the window. Even though it is the worst option, it is the easiest to use and understand. The following code can be used within the UserForm’s QueryClose event. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Close button to close the form", vbOKOnly End If End Sub If the user clicks on the close button, the following message box will appear. This is effectively disabling the close button, as the UserForm will not close. But from a user’s perspective, this solution is just frustrating. Why would there be a button which doesn’t do anything apart from telling us it doesn’t do anything? This is why it’s not a great option. (2) Disable the close button (Windows API) The second option will disable the close button; it will be visible, but will be greyed-out and cannot be clicked. The first image below shows the close button when it is enabled; the second image shows the close button when it is disabled. From a user’s perspective, this option is better than the first, but still not great. What is the point of a button which cannot be clicked; it just creates confusion? Basic code The code below should be copied into a new standard module and must be included before any other code, but after the Option Explicit statement (if there is one). 'Include this code at the top of the module Private Const GWL_STYLE = -16 Private Const WS_CAPTION = &HC00000 Private Const WS_SYSMENU = &H80000 Private Const SC_CLOSE = &HF060 #If VBA7 Then Private Declare PtrSafe Function FindWindowA _ Lib "user32" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare PtrSafe Function DeleteMenu _ Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, _ ByVal wFlags As Long) As Long Private Declare PtrSafe Function GetSystemMenu _ Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long #Else Private Declare Function FindWindowA _ Lib "user32" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function DeleteMenu _ Lib "user32" (ByVal hMenu As Long, _ ByVal nPosition As Long, ByVal wFlags As Long) As Long Public Declare Function GetSystemMenu _ Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long #End If 'Include this code in the same module as the API calls above Public Sub CloseButtonSettings(frm As Object, show As Boolean) Dim windowHandle As Long Dim menuHandle As Long windowHandle = FindWindowA(vbNullString, frm.Caption) If show = True Then menuHandle = GetSystemMenu(windowHandle, 1) Else menuHandle = GetSystemMenu(windowHandle, 0) DeleteMenu menuHandle, SC_CLOSE, 0& End If End Sub The code examples above create reusable functionality to enable or disable the close button. The CloseButtonSettings subprocedure can now be called from any other module. There are two arguments required. frm – a reference to the UserForm for which the close button should be enabled/disabled show – a True or False value where True = enable the close button and False = disable the close button Disable the close button on UserForm initialize If inserted into the UserForm’s initialize event it will disable the button when the form is created Private Sub UserForm_Initialize Call CloseButtonSettings(Me, False) End Sub Disable or enable the close button at any point The close button can be enabled or disabled as and when required with the following macros. Disable the close button Sub DisableClose Call CloseButtonSettings(frmMyUserForm, False) End Sub Enable the close button Sub EnableClose Call CloseButtonSettings(frmMyUserForm, True) End Sub (3) Hide system menu (Windows API) The third option is to hide the system menu section of the window. The System menu section includes the maximize, minimize and close buttons. The screenshot below shows the close button is not visible. By hiding the system menu section, we are also removing the ability to see the minimize and maximize buttons. By default a UserForm does not have these buttons visible (separate Windows API calls are required to enable this), therefore this is probably a good option in 99.9% of circumstances. Basic Code The code below should be copied into a new standard module and must be included before any other code, but after the Option Explicit statement (if there is one). 'Include this code at the top of the module Private Const GWL_STYLE = -16 Private Const WS_CAPTION = &HC00000 Private Const WS_SYSMENU = &H80000 #If VBA7 Then Private Declare PtrSafe Function GetWindowLong _ Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, _ ByVal nIndex As Long) As Long Private Declare PtrSafe Function SetWindowLong _ Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, _ ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare PtrSafe Function FindWindowA _ Lib "user32" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare PtrSafe Function DrawMenuBar _ Lib "user32" (ByVal hWnd As Long) As Long #Else Private Declare Function GetWindowLong _ Lib "user32" Alias "GetWindowLongA" ( _ ByVal hWnd As Long, ByVal nIndex As Long) As Long Private Declare Function SetWindowLong _ Lib "user32" Alias "SetWindowLongA" ( _ ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare Function FindWindowA _ Lib "user32" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function DrawMenuBar _ Lib "user32" (ByVal hWnd As Long) As Long #End If 'Include this code in the same module as the API calls above Public Sub SystemButtonSettings(frm As Object, show As Boolean) Dim windowStyle As Long Dim windowHandle As Long windowHandle = FindWindowA(vbNullString, frm.Caption) windowStyle = GetWindowLong(windowHandle, GWL_STYLE) If show = False Then SetWindowLong windowHandle, GWL_STYLE, (windowStyle And Not WS_SYSMENU) Else SetWindowLong windowHandle, GWL_STYLE, (windowStyle + WS_SYSMENU) End If DrawMenuBar(windowHandle) End Sub These codes create reusable functionality to show or hide the buttons. The SystemButtonSettings subprocedure can be called from any other module. There are two arguments required frm – a reference to the UserForm for which the close button should be shown/hidden show – a True or False value where True = show the system menu and False = hide the system menu Hide the system menu buttons on UserForm initialize If inserted into the UserForm’s initialize event it will disable the button when the form is created Private Sub UserForm_Initialize Call SystemButtonSettings(Me, False) End Sub Show or hide the close button at any point The close button can be shown or hidden as and when required using the following macros. Hide the close button Sub HideClose Call SystemButtonSettings(frmMyUserForm, False) End Sub Show the close button Sub ShowClose Call SystemButtonSettings(frmMyUserForm, True) End Sub