Sounds and Title Bar (Userforms)

[Matthew]

Dear All,

I have a userform with some buttons, when a button is clicked I would like a
sound to play - easy with beep command, however I need different sounds with
different keys - bit like a touch-tone phone keypad. How can I play cusom
wav files?

Also, I'm sure this is easy. I would like to disable the tile bar of the
userform or at least the close (x) button.

Any help would be greatly apprciated.

[Tom Ogilvy]

http://j-walk.com/ss/excel/tips/tip87.htm
Playing a Sound Based on a Cell's Value

http://j-walk.com/ss/excel/tips/tip59.htm
Playing Sound From Excel

Here is a post by Stratos Malasiotis on the topic of removing the titlebar:

Hi Charlie,


Of course you can remove the 'titlebar' of a Userform in Excel! <g>


Excel (Office) often makes things more confusing that they already are by
drawing controls by itself without using the 'standard' windows
common controls and other features; however, a userform is still a standard
window and therefore we can apply to it any window style that is
provided in the current versions of Windows.


When you design a Userform in your Excel VBE you are actually
editing/drawing specifications that Excel in run-time uses to create a new
window based on a registered window class and also write all the required
code for it. This class is named "ThunderXFrame" of
"ThunderDFrame" in Office's case (and you can access and modify it using
GetClassLong and SetClassLong API functions). Based on this class
Excel uses the same Windows API and most probably the CreateWindow function
(or CreateWindowEx) to generate the Userform window. That
function takes a long "style" argument where Excel initially assignes the
default window style ('overlapped' I would assume).
Since the createwindow function is completed and the windows is drawn on
your screen (using the ShowWindow function or similar), the control
normally passes to Windows which holds every information about that window
and sends messages to the WinMain and the WinProc functions of
the userform window depending on user-actions (etc.).


In order to access this information you call the GetWindowLong and
SetWindowLong API functions.


Here is an example which demostrates their use for redrawing a userform
window with or without titlebar:


in a userform with three command buttons add:
---------------------------------------------------------------------------Â*----------------------
Option Explicit


Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type


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 DrawMenuBar _
Lib "user32" _
( _
ByVal hWnd As Long _
) _
As Long


Private Sub CommandButton1_Click()
Unload Me
End Sub


Private Sub CommandButton2_Click()
Call fncHasUserformCaption(True)
End Sub


Private Sub CommandButton3_Click()
Call fncHasUserformCaption(False)
End Sub


Private Sub UserForm_Initialize()
Call fncHasUserformCaption(False)
End Sub


Private Function fncHasUserformCaption _
( _
bState As Boolean _
)
'change the style of the Userform window to have a
'caption or not to


'declarations of variables
Dim Userform_hWnd As Long
Dim Userform_Style As Long
Dim Userform_Rect As RECT


'required API consatants
Const GWL_STYLE = (-16)
Const WS_CAPTION = &HC00000


'get a handle to the userform window
Userform_hWnd = FindWindow _
( _
lpClassName:=IIf(Val(Application.Version) > 8, _
"ThunderDFrame", _
"ThunderXFrame"), _
lpWindowName:=Me.Caption _
)


'get the current style fof the window
Userform_Style = GetWindowLong _
( _
hWnd:=Userform_hWnd, _
nIndex:=GWL_STYLE _
)


'deside whether to redraw the form with a caption or without
'based on the bState parameter
If bState = True Then
Userform_Style = Userform_Style Or WS_CAPTION
Else
Userform_Style = Userform_Style And Not WS_CAPTION
End If


'set the new style to the window
Call SetWindowLong _
( _
hWnd:=Userform_hWnd, _
nIndex:=GWL_STYLE, _
dwNewLong:=Userform_Style _
)


'redraw the window using the new style
Call DrawMenuBar _
( _
hWnd:=Userform_hWnd _
)


End Function
---------------------------------------------------------------------------Â*----------------------


The fncHasUserformCaption is reusable and can (hopefully) work in any
userform.
Pretty cool for creating splash screens !!


If you are interested on this kind of programming you may also wish to
visit Stephen Bullen's web-site for a wealth of similar examples. Stephen is
considered one of the grandmasters of the game and a
visit in his website will show you why. He also contributed with three
chapters in John Green's Excel 2000 VBA book and they are also
coauthoring a new book, hopefully available soon.


HTH
Stratos

--
Regards,
Tom Ogilvy


"Matthew" wrote:

> Dear All,
>
> I have a userform with some buttons, when a button is clicked I would like a
> sound to play - easy with beep command, however I need different sounds with
> different keys - bit like a touch-tone phone keypad. How can I play cusom
> wav files?
>
> Also, I'm sure this is easy. I would like to disable the tile bar of the
> userform or at least the close (x) button.
>
> Any help would be greatly apprciated.
>
>

[Matthew]

Thanks Tom!

And I thought it would be easy!!

Matthew