Excel VBA Set Userform Opacity
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 .
Transparency of userform can be set easily with slider control and VBA codes.
VBA codes to set userform opacity:
VBA codes to set userform opacity:
Private Sub Sliderk_Scroll()
Call SetOneValueSlider
End Sub
Private Sub SetOneValueSlider()
Dim bytOpacity As Byte
bytOpacity = Sliderk.Value
...
Call SetOneValueSlider
End Sub
Private Sub SetOneValueSlider()
Dim bytOpacity As Byte
bytOpacity = Sliderk.Value
...
...
End Sub
End Sub
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.
No comments:
Post a Comment