keepITcool has developed a method to position a userform over a specific range. Finding a range’s screen coordinates is tricky. I usually resort to “close enough”.
He says:
First I found a bug. Excel 97 thru 2007.
The VisibleRange of Pane 2 and 3 (in a 4 pane window) are inconsistent.
depending on the sequence in which the vertical and horizontal split bars are set it will sometimes
return the range of the upper-right pane, sometimes the lower-left… and in both panes it may return activepane.index = 2
I found the cause and a workaround.
I soon got desperate using PointsToScreenPixels with non-standard zoom. Then I found that using XLM macro’s is the only reliable way to consistently get the “crosshair” on the screen under a variety of splits, zooms and display options. Some fiddling to offset from that point in case of frozen panes.. et voila!
You can download RangePos Beta1.zip.
Update: Download RangePos Beta3.zip
Mike Alexander has updated his table driven Ribbon customizer. You can find it here:
http://www.datapigtechnologies.com/Custom_UI_Builder.zip
He says:
The utility now creates and adds a module with all the call-back functions to the newly created buttons, allowing the buttons to work right away.
As always, the source code is open for anyone who wants to build on this and create a better table-driven Ribbon Customizer.
If you try it out, post your comments here.
When I was creating sample data for Returning a Limited Collection..., I had to fill a bunch of collections with custom classes. Instead of creating the class and adding it to the collection, I created a function that returned the class and use the function call in the Add method. Here's an example:
Set clsGroup = New CGroup
clsGroup.Name = "Group1"
Set colContacts = New Collection
colContacts.Add CreateContact("Dick Kusleika", _
"Company1", "NE"), "Dick Kusleika"
colContacts.Add CreateContact("John Doe", _
"Company2", "NE"), "John Doe"
colContacts.Add CreateContact("Jane Doe", _
"Company2", "MA"), "Jane Doe"
Set clsGroup.Contacts = colContacts
colGroups.Add clsGroup, clsGroup.Name
The CreateContact function looks like this:
Function CreateContact(sName As String, sCompany As String, _
sState As String) As CContact
Dim clsCon As CContact
Set clsCon = New CContact
clsCon.Name = sName
clsCon.Company = sCompany
clsCon.State = sState
Set CreateContact = clsCon
End Function
I never thought of calling a function directly from the Add method of a Collection, but I can't think of any reason not to do it.
What's the most impressive Excel application that you've seen?
Today I had an opportunity to revisit Ivan F. Moala's ImageToXcel, and I spent some time looking at the VBA code. It gets my vote. Yowsers!
Other nominations?
Are you still using Excel 2003? Me too. Microsoft has some lessons that you can listen to, or read, I guess. Unfortunately, I can't put them on my iPod and I don't see me sitting in front of my computer listening to a 50 minute lesson on macro security. One of you guys do it and tell me if they're any good. 