Transparent UserForm

Make a UserForm transparent in Excel; this allows you to see what is in the worksheet behind the form and can make things like data entry a bit easier.

Sections:

Make UserForm Transparent

Set Form Opacity with a Button

Notes

Make UserForm Transparent

To do this, we need to use VBA code. This code will go into a regular module and also within the Initialize event of the UserForm.

Module Code

This goes into a regular module within the VBA window. (Alt + F11 to get to the VBA window and then Insert > Module.)

Private Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
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 SetLayeredWindowAttributes _
    Lib "user32" _
    (ByVal hWnd As Long, ByVal crey As Byte, ByVal bAlpha As Byte, ByVal dwFlags As Long) As Long

Private Const GWL_EXSTYLE = (-20)
Private Const WS_EX_LAYERED = &H80000
Private Const LWA_ALPHA = &H2&

Public hWnd As Long

Note: the above code must go at the very top of the module.

This next section of code can be placed anywhere within a module.

Sub MakeTransparent(frm As Object, TransparentValue As Integer)

Dim bytOpacity As Byte

'Control the opacity setting.
bytOpacity = TransparentValue

hWnd = FindWindow("ThunderDFrame", frm.Caption)
Call SetWindowLong(hWnd, GWL_EXSTYLE, GetWindowLong(hWnd, GWL_EXSTYLE) Or WS_EX_LAYERED)
Call SetLayeredWindowAttributes(hWnd, 0, bytOpacity, LWA_ALPHA)

End Sub

UserForm Code

This line of code must be placed within the UserForm itself in order for it to work.

Place this within the Initialize event.

MakeTransparent Me, 200

200 is the only thing you should change as it determines the level of opactiy. Any value between 165 and 200 generally works well.

Opacity Values:

• Max number: 255 (opaque)
• Min value: 0 (transparent)

The code within the Initialize event, would look like this:

Private Sub UserForm_Initialize()

MakeTransparent Me, 200

End Sub

If you already have code in the Initialize event, just place MakeTransparent Me, 200 at the top of this section.

To learn more about events and how to use them, view our tutoral on UserForm Events.

Set Form Opacity with a Button

You can make it so that the opacity of a form is changed depending on what is happening within the form. This could be set with the click of a button, or any other trigger within the form.

For this example, I added two buttons on the form, one will make the form transparent and the other will make it opaque.

Transparent code:

Private Sub CommandButton4_Click()

'Make the form transparent.
MakeTransparent Me, 200

End Sub

CommandButton4 is the name of the Transparent button.

Opaque code:

Private Sub CommandButton5_Click()

'Make the form opaque.
MakeTransparent Me, 255

End Sub

CommandButton5 is the name of the Opaque button.

Note: this code goes into the code window for the UserForm. To get to each section of code for the buttons, simply double-click the button after you have put it onto the form.

Once you click the Transparent button, it looks like this:

Click the Opaque button to get back to the default, non-transparent form.

Notes

Make sure not to make the form completely invisible or it might be difficult to actually close it. I suggest keeping your opacity levels set to just enough so that the worksheet behind the form can be read.

Download the sample file for this tutorial to see the above examples in Excel.