Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sounds and Title Bar (Userforms)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sounds and Title Bar (Userforms)
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sounds and Title Bar (Userforms)
Thanks Tom!
And I thought it would be easy!! Matthew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I PUT SOUNDS IN EXCEL | Excel Discussion (Misc queries) | |||
Sounds | Excel Discussion (Misc queries) | |||
Named range=Column title,comumn title in cellB6 use B6in equation | Excel Discussion (Misc queries) | |||
Pasting Objects into Chart title and Axis title | Charts and Charting in Excel | |||
Sounds in Excel | Excel Programming |