Here are instructions for that which I found somwhere on the Web a couple of
years ago (my apologies to the actual author). I have added a few notes of my
own.

This code makes an userform semitransparent. Works equally well for Word or
Excel. Place the following controls on an Userform.
• CommandButton1
• ScrollBar1
• Label1

Copy & paste all the code below into the form's code page.

Please note, the userform will be invisible if you move the scroll bar to
100%. That means you cannot control the userform any more. I suggest setting the
scrollbar limits to Min value = 2 and Max value = 98. Double-clicking the
userform or the label resets the scrollbar value to 50.

Private Declare Function GetActiveWindow Lib "USER32" () As Long

Private Declare Function SetWindowLong Lib "USER32" _
Alias "SetWindowLongA" ( _
ByVal hWnd As Long, ByVal lngWinIdx As Long, _
ByVal dwNewLong As Long) As Long

Private Declare Function GetWindowLong Lib "USER32" _
Alias "GetWindowLongA" ( _
ByVal hWnd As Long, ByVal lngWinIdx As Long) As Long

Private Declare Function SetLayeredWindowAttributes Lib "USER32" ( _
ByVal hWnd As Long, ByVal crKey As Integer, _
ByVal bAlpha As Integer, ByVal dwFlags As Long) As Long

Private Const WS_EX_LAYERED = &H80000
Private Const LWA_COLORKEY = &H1
Private Const LWA_ALPHA = &H2
Private Const GWL_EXSTYLE = &HFFEC
Dim hWnd As Long

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ScrollBar1_Change()
Call Semitransparent(Me.ScrollBar1.Value)
End Sub

Private Sub UserForm_Activate()
Me.ScrollBar1.Value = 50
End Sub

Private Sub Label1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.ScrollBar1.Value = 50
End Sub

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.ScrollBar1.Value = 50
End Sub

Private Sub Semitransparent(ByVal intLevel As Integer)
Dim lngWinIdx As Long
hWnd = GetActiveWindow
lngWinIdx = GetWindowLong(hWnd, GWL_EXSTYLE)
SetWindowLong hWnd, GWL_EXSTYLE, lngWinIdx Or WS_EX_LAYERED
SetLayeredWindowAttributes hWnd, 0, (255 * intLevel) / 100, LWA_ALPHA
Label1.Caption = "Semitransparent level is ..." & (100 - intLevel) & "%"
End Sub

Thanks

This is very interesting.

I've tried the below code and awesome it worked perfectly
well without even a single error. I directly applied this to my program which I
had done already and it worked great. I thank Hutch for providing me the code.

Although I've this excellent code, I want to do something more in this. Actually
in Visual Basic we can make the form  transparent but the controls like label,
command buttons and others can be visible as such with full opacity. But here in
Excel VBA along with userform all other controls placed in the form is also
getting faded and transparent. I don't want those to be transparent. Only the
form can be semi transparent and I want this to effect only on the userform and
not on the other controls palced in the form.

Kindly advice me how to acheive this.