August 31, 2006

Userform Positioning

Last time I mentioned going through my utilities and rewriting the code. The idea appealed to me more and more, so I made a good start this week. Unfortunately there is so much code to go through, it's going to take quite a while until I am finished. What I am doing now is adding new features to the userforms so they easier to work with. (I've already improved the Help index though, it's available with the latest upload)

For example, you can use the Registry to record settings such as a userform's position. This might help a lot if you want the userform to appear in the same place each time you use it. (The default position is in the center).

Before you start, go to the VBE (push Alt + F11 at the same time). Locate your userform, (I'm assuming a reasonable amount of experience here), if you can't see the Properties Window, try pushing F4. Make sure the tab with the properties in alphabetic order is showing and go to StartUpPosition. Change the setting to 0 - Manual.

Now go to the code in the userform. The userform events I use are UserForm_Initialize and UserForm_QueryClose. If you are not already using these events, you can select Userform from dropdown list at the top left to show the different events available from the dropdown list at the top right.

For the UserForm_QueryClose event use something like this,

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    SaveSetting "My Settings Folder", Me.Name, "Left Position", Me.Left
    SaveSetting "My Settings Folder", Me.Name, "Top Position", Me.Top
End Sub

SaveSetting will record the userform's left and top positions when the form is closed. This is the information needed for when you use the userform the next time. To make it happen, use this GetSetting code with the UserForm_Initialize event.

Private Sub UserForm_Initialize()
    If GetSetting("My Settings Folder", Me.Name, "Left Position") = "" _
        And GetSetting("My Settings Folder", Me.Name, "Top Position") = "" Then
        Me.StartUpPosition = 1 ' CenterOwner
        Me.Left = GetSetting("My Settings Folder", Me.Name, "Left Position")
        Me.Top = GetSetting("My Settings Folder", Me.Name, "Top Position")
    End If
End Sub

Okay, a bit of overkill with the double condition with the settings (both left and top) but it's not a big thing. (If you don't follow me, don't lose too much sleep ;-))

Now the userform will reopen in the same position it was when closed. For more information on SaveSetting and GetSetting, you can refer to the Help files. Unfortunately they won't tell you where the settings are. To see them, use regedit from Accesories, System Tools on the Start Menu (going from memory so bear with me). If you can't find regedit there, try looking for it in the Windows folder (of Window Explorer). Once you have regedit opened, you can find the settings in HKEY_CURRENT_USER, Software, VB and VBA Program Settings. There are quite a few things you can do to alter your settings, but be very careful, doing the wrong thing in the wrong place with the registry can cause serious problems with your computer. Play it safe and make sure you know what you are doing first :-)  

Posted by andrewe at 22:37

August 24, 2006

Cell Watch Toolbar Improved

I was looking at my Cell Watch Toolbar today and had an idea - rather than just looking at cells, why not include a way to overwrite them. So I added a new button to switch between "Read" and "Write" modes.

Copied from the read me file,

Read Mode / Write Mode
The second button on the toolbar allows you to alternate between Read and Write modes. When the clipboard icon shows, the toolbar is Read Mode, when the keyboard icon shows, toolbar is in Write Mode.

Read Mode (Copy to Clipboard): Push the captions that show the Cell Watch values and they will be copied to the clipboard. You can then paste them anywhere you like, even with another program like Word or Outlook.

Write Mode (Write to Cells): Push the captions and an input box will show. This will allow you to change Cell Watch values directly from the toolbar.

So this means you can not just copy to the clipboard, you can change the values of a specified cell that is out of sight, be it off the screen, hidden or in another sheet or workbook. Nice.

Cell Watch Toolbar

If I have any other ideas, I'll try to include them too ;-)  
Posted by andrewe at 20:09

August 20, 2006

Paste Special Plus 2

It's been one of those weeks. The first three days were holidays and I was out of town. Then I came back to work to find all hell had broken loose...why can't I have the fiasco first, then the time off to recuperate? :-)

Anyway, I did find time to add a few options to Paste Special Plus. Well, maybe more than a few...

Here's the download link.

Anyway here are some photos of me and my future family during the holidays at Nunobiki Waterfall just behind Kobe Shinkansen Station.

This is a picture of us wearing our "let's go wading in a waterfall" clothes. Only problem is the sign that said we could not. Yikes.

My family are amazed at my sudden weight loss. What is my secret? :-)

After a bit of climbing we arrived at a lookout. Here is the view.

Kobe is a pretty nice place. A long bus ride from where I live in Nagoya but worth every minute of it ;-)  
Posted by andrewe at 19:00

August 10, 2006

XCEL Racer

Here's a game I made a while back. Only I never got around to completely finishing works okay but all you do is drive around the track and avoid the walls, there is no goal or score :-)

Anyway, here it is if you want to try it. Use the arrow keys to change direction or move forward. Watch out for the sound if you crash, it can be quite loud.

Being the world's least artistic person, I added a sub so that you can change the tracks if you want to. Just use the same colors as already used. Look for SetWallsAndTrack in the standard module at the bottom.

Anyway, safe driving, here's a link to download the game. Have fun.  
Posted by andrewe at 20:11

August 02, 2006

Paste Special Plus

After a very long holiday, I would like to say how happy I am to be back. But I can't. How could I when I just left paradise? :-)

Anyway, I've been working on a few small projects recently. One was to add a few options for what regular Paste Special does not include.

A few notes

- Conditional Formatting only works with Formula Is, Format, Cell Shading (Fill Color).

- Row Height must be selected both when copying and pasting to work.

- The form is modeless for later Excel versions. For Excel 97, you will have to use it twice, once to copy a range and once more to paste.

- The code is a little slow, so please don't use it for very large ranges like entire columns unless you enjoy waiting a long time. You might be better off doing things the "hard" way in this case.

Also, I have noticed it seems problems at times where VBA functions are used. I am not sure of the cause, you can overcome it by not enabling macros while you are working with it. (Anyone else who knows something, please write a comment or send me an email)

Here is the download link. You will find it under Paste Special on the Right Click menu, (Cell, Row and Column)

Installation: Don't open it like regular files. Open Excel, go to Tools, Add-Ins (on the top menu) and use the Browse button to find it if not already shown in the list provided.  
Posted by andrewe at 20:48