Transparent Userform In Excel

Excel Spin Button Examples

Using the transparent userform allows user to see what is in the sheet behind the userform and also can make data entry more easier. We added 3 option button controls on the userform to set the transparency of the userform.

To be able to set Userform transparency in both 32bit and 64bit Excel versions, we added to userform codes SetWindowLong , GetWindowLong, FindWindow, SetLayeredWindowAttributes functions as follows : ⬇

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If

Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function SetLayeredWindowAttributes Lib "USER32" (ByVal hWnd As LongPtr, ByVal crKey As Long, ByVal bAlpha As Byte, ByVal dwFlags As Long) As Long
    
#Else
    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 GetWindowLong Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function SetLayeredWindowAttributes Lib "USER32" (ByVal hWnd As Long, ByVal crKey As Long, ByVal bAlpha As Byte, ByVal dwFlags As Long) As Long
#End If

Private Const GWL_EXSTYLE = (-20)
Private Const WS_EX_LAYERED = &H80000
Private Const LWA_ALPHA = &H2&
#If Win64 Then
    Public hWnd As LongPtr
#Else
     Public hWnd As Long
#End If

Private Sub OptionButton3_Click()
Dim bytOpacity As Byte
bytOpacity = 255 ' variable keeping opacity setting
hWnd = FindWindow("ThunderDFrame", Me.Caption)
 #If VBA7 Then
Call SetWindowLongPtr(Me.hWnd, GWL_EXSTYLE, GetWindowLongPtr(Me.hWnd, GWL_EXSTYLE) Or WS_EX_LAYERED)
Call SetLayeredWindowAttributes(Me.hWnd, 0, bytOpacity, LWA_ALPHA)
#Else
Call SetWindowLong(Me.hWnd, GWL_EXSTYLE, GetWindowLong(Me.hWnd, GWL_EXSTYLE) Or WS_EX_LAYERED)
Call SetLayeredWindowAttributes(Me.hWnd, 0, bytOpacity, LWA_ALPHA)
#End If
End Sub

✔ The userform example was tested in 32bit and 64bit Excel versions .

---

Set Userform Opacity With Slider

In this template we used slider control to set userform transparency.
At same time ,the value of the slider is displayed on the label. We set the Min value of the slider to 25. Because, when the Min value is 0, the userform becomes invisible. ⬇