December 24, 2007

'Twas the day before Christmas

Tomorrow is Christmas again, the 4th since I started blogging. This year has been particularly busy, so I have not been as active as I would have liked.

But I am still around and there is always next year. I'll do my best to make up it for it then...

Until that time, Merry Christmas and a Happy New Year folks. Hope it's a good one ;-)  

Posted by andrewe at 12:41

December 02, 2007

Faking The RefEdit Control

I prefer not to use the RefEdit control for 2 reasons. One reason is that I have had trouble with it in the past (it's a bit tempermental), and the other is that it is not as flexible as I would like, I cannot select workbooks other than the active one.

So I made an alternative. It's not perfect but it does resemble a real RefEdit control (in my opinion :-) ).

2 forms are required. Here is the first one with showing the range plus sheet plus book in a text box.



Push a button, the first form disappears and the second one shows, this time the form is modeless so you can select a range.



Some class module code updates the address according to any selection changes, and copies the range to give it those "authentic" dotted lines. I've also taken the liberty of disabling command bar controls to help prevent user interference, they get enabled again when when you close the first user form.

Push the Set Range button and after a one second delay, the first userform reappears with the selected range. You can use the variables in the code (wbSelectedWorkbook, wsSelectedSheet and rSelectedRange) to do whatever you want the fake RefEdit for in the first place.



Here is a workbook to try out. It should work with Excel versions 2000 - 2003. For 2007 you will have to do without the range copy code as disabling controls won't stop a user from using the Ribbon. Other than that, it will still do the same job.  
Posted by andrewe at 20:44