I tried to use EnableCancelKey to allow myself to stop the music and still make the appropriate API calls to reset the midi out. It didn't work so well for me. Here's another way.
I created two global variables. One to determine whether the procedure was already running and one to determine if the user wants to stop the procedure.
Public gbUserCancel As Boolean
Public gbAppStarted As Boolean
These are declared in a standard module outside of any procedures so that they're accessible from anywhere in the project.
Next, I modified the click event of the button that's used to start the procedure.
Private Sub CommandButton1_Click()
If gbAppStarted Then
gbUserCancel = True
gbAppStarted = False
Else
gbAppStarted = True
PlayWorksheetNotes
End If
End Sub
If the procedure is running (gbApppStarted is True), then it assumed the user wants to stop the procedure and gbUserCancel is set to True. If the procedure is not running, it's called so that it starts running. Finally, I modified the PlayWorksheetNotes procedure to check the gbUserCancel variable.
Sub PlayWorksheetNotes()
Dim r As Long
On Error GoTo ErrHandler
gbUserCancel = False
For r = 2 To Application.CountA(Range("A:A"))
If gbUserCancel Then Err.Raise 9999
Cells(r, 2).Select
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
DoEvents
Next r
ProcExit:
On Error Resume Next
midiOutReset hMidiOut
'Stop
Exit Sub
ErrHandler:
If Err.Number <> 9999 Then
MsgBox Err.Description
End If
'Stop
Resume ProcExit
End Sub
If the button is clicked while the notes are playing, gbUserCancel is set to True. Inside the loop, that variable is check. Once True, an error is raised and the error handler is called. Any error that's not the one I made up (9999) is shown in a message box. Then the procedure resumes at ProcExit which resets the midi out for the future.
Yesterday I installed OpenOffice on a couple of laptops. In order to accept the license agreement, I had to scroll down to the bottom of the textbox. The "Accept" button was disabled until I'd reached the bottom. I don't know why they care if I read their EULA. I could go on and on about how EULAs are too complicated and probably unenforceable when you really need them, but I won't.
Instead, I was interested in replicating the form in VBA. (Perhaps a violation of the EULA? How delicious.) I started with a Textbox control on a userform. I set Multiline to TRUE, WordWrap to TRUE and ScrollBars to fmScrollBarsVertical. It looked like the OpenOffice form, but I couldn't figure out a way to tell where the scroll bar was in the Textbox. That is, I didn't know when the scrollbar was all the way to the bottom.
Next I went to the Listbox. I knew Listboxes had a TopIndex property that shows the top-most item that's visible. With the size of my Listbox and 100 lines of an EULA, I hardcoded that when TopIndex is 78, the user has scrolled all the way down.
Knowing that TopIndex is 78 and doing something about it are two different things. I discovered, or rediscovered, that clicking on a scroll bar does not fire the Click event of a Listbox. Nor does it fire the MouseUp event. Apparently, the scrollbar of a control is in the domain of the userform rather than the control. I settled on the MouseMove event of the Userform object. It's not perfect, but it's close.
Private Sub UserForm_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Me.ListBox1.TopIndex = 78 Then
Me.CommandButton1.Enabled = True
End If
End Sub
One nice thing is that I don't have to manage the Enabled state of the commandbutton. Once the user hits the bottom, it's enabled and that's that. Scrolling back up doesn't negate the fact the user was at the bottom and presumably read the agreement (yeah, right!). One obvious downside is that Listboxes aren't really meant to hold text. Editing the text would be a huge chore because one wayward line break affects all that come after it.
I would be nice if '78' wasn't hard coded. I'm sure I could figure out the appropriate TopIndex based on the Height and ListCount properties. I also may need to know the Font property. As you can see, my interest dried up before I got to that point.
As usual, your improvement are welcome and appreciated.
The Microsoft Office Insider newsletter had a link for becoming certified in Office 2007. I took the sample test for Excel and got 1000.
I'm not sure how I get 1000 for three questions. I thought I was going to fail. The sample test is administered via Shockwave. It seems that you have to do things in the proper order or it doesn't react. For example, I was not able to select cells on the first sheet because the "first step" was to select the proper sheet. It was strange.
It also appears to only give you one option for completing a task. I don't do much charting, but I can honestly say that I don't recall there ever being a Chart menu item. I have always used either the Charting toolbar or the right-click menu. It's a good thing they gave me five minutes for each question because it took a while to figure out how they wanted me to do things. Has anyone taken the full blown test?
The EnableCancelKey property of the Application object determines what will happen when a user interrupts your code. Users can interrupt code by pressing Esc or Cntl+Break. EnableCancelKey has three possible settings:
- xlDisable - Prevents the user from interrupting. I've never used this and can't think of why I ever would.
- xlInterrupt - Normal operation. The debugger is shown and the code is in debug mode at whichever line it happened to be when it was interrupted.
- xlErrorHandler - Raises error number 18 and reacts just like any other error. If you have error handling set up, it's called.
J-Walk posted about Musical Excel a while back. There was an issue with the midi out API not resetting properly and people could not run the code twice. Mpemba fixed it in the comments with a reset API call, but if the user was to interrupt the code, that API function would never get called.
I downloaded the dueling banjos file from J-Walk, and modified the code. This part was added:
Private Declare Function midiOutReset Lib "winmm.dll" _
(ByVal hMidiOut As Long) As Long
and this part was modified:
Sub PlayWorksheetNotes()
Dim r As Long
On Error GoTo ErrHandler
Application.EnableCancelKey = xlErrorHandler
For r = 2 To Application.CountA(Range("A:A"))
Cells(r, 2).Select
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
DoEvents
Next r
ProcExit:
On Error Resume Next
midiOutReset hMidiOut
Stop
Exit Sub
ErrHandler:
If Err.Number <> 18 Then
MsgBox Err.Description
Else
Debug.Print Err.Description
End If
Stop
Resume ProcExit
End Sub
Setting EnableCancelKey allows the code to exit gracefully and ensures that the midiOutReset call is always made. This resets the midi out and should prevent the problem of not being able to run the code twice. It shouldn't really matter whether you have an On Error statement before or after the EnableCancelKey statement.
Actually, this code would exit gracefully except that I have all those 'Stop' commands in there. Oddly, when I set EnableCancelKey to xlInterrupt, it works every time I press Cntl+Break (that is, the code stops and the debugger opens). When I set EnableCancelKey to xlErrorHandler, pressing Cntl+Break works about one out of every thousand times. That estimate is based on me pressing it about 100 times during code execution and the code executes all the way through nine out of ten times.
If you have the time, download dueling-banjos.xls, modified the code as I have and see if you can interrupt normally.
Most of you will know that Excel 2007 (well, Office 2007) comes with a brand new file format, based on what MSFT calls Open XML.
This suddenly enables us to write code that can easily generate/change Office 2007 files without the need for an Office installation. For instance on a server.
Whilst there is proper documentation on this file format, the document with detailed descriptions of the Open XML format ("part 4" in the aforementioned link) counts an astonishing 4721 pages !!
This is why I decided to write up a couple of basic pages on how to do stuff with these Open XML files.
My first one is about reading and editing cells:
Working With Worksheet Data In An Excel 2007 File
Enjoy!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
The TakeFocusOnClick and Default properties are not behaving like I'd like them to. ActiveX command buttons (the ones you put on userforms) have these two properties. Setting TakeFocusOnClick to False (the default is True) keeps the focus on whatever control had it before the command button was clicked. Setting Default to True causes the Click event of a command button to run when the Enter key is pressed regardless of which control has the focus.
A typical use of the Default property is for an OK button on a form. I use this when I want the user to be able to dismiss the form and apply the changes by pressing Enter. A similar property, Cancel, does the same thing with the ESC key. Typically a Cancel button has its Cancel property set to True so the user can get out of the form by pressing ESC regardless of where he is in the form.
I have a ListView control on a userform. When a user double clicks on a ListItem, a different userform pops up. It seems to me that selecting a ListItem and pressing enter should give the same result as double clicking. I base that on the fact that I can press Enter in a file open dialog box and it has the same effect as double clicking. File open dialogs are just ListViews, after all. As I was pondering the file open metaphor, I thought that maybe the Open button is simply the default button and that's why Enter would work the way it does.
That seemed like a reasonable solution, so I but a command button on the form and set its Default property to True. The user would expect to still be in the ListView when the secondary userform was dismissed, so I also set the TakeFocusOnClick property to False. It works great. I click the button, the userform appears. I dismiss the userform and I'm still in the ListView.
You've probably guessed the problem by now. When I press the Enter button, the useform appears as expected. But when I dismiss the userform, the command button has the focus rather than the ListView. It seems that firing the Click event via an actual mouse click respects the TakeFocusOnClick property, but firing via the Default property doesn't.