Methods In Excel

A collection of tips for working with user forms this week.

1. Custom Colours. 

You can define a set of custom colours to use with a forms controls – better still these colours will persist across sessions of the IDE, so you can use them in different projects once you have set them up.

To define a custom colour all you have to do is open the colour picker dialog  from a controls colour pallet and set the colour. To do this select a control, go to one of its properties that exposes a colour (backcolor, forecolor etc.). Next click the drop down and pick the “Palette” tab, at the bottom of that there are two rows of white boxes, right click in any of these and you’re a way – happy colouring!

Select the colour drop down, and click the “Palette” tab. Right click any of the white boxes in the bottom two rows.

2. Tab Order, Tab Order.

One for the OCD’s amongst us. I’m guess most people know this already, but just in case. You can change the order in which the cursor jumps from control to control on a user form, in response to the user pressing the tab key. Under the view menu, click the “Tab Order” menu item, and away you go… makes sense really!

3. Fix up, Look Sharp. 

[What, you're not a Dizzee fan?]. Another little micro tip really. Under the Format menu are a host of tools to get your controls arranged superb nice on the form. They are fairly straight forward to understand, but here’s a pro tip; customise your toolbars and add them as buttons to save time!!!

Use the tools under the Format menu to arrange and line up the controls on your form. Add the controls as toolbar buttons to save time (red box).

Well that’s your lot, if you have any other tpis, please do let us know! Thanks Ross

A quick time saver this week.

Paste picture.

When you want to add a picture to a control on a form, you can just copy the image to the windows clipboard, and Ctrl+V when in a controls picture property. If the file is on disc already then it not a time saver, but if you pull the image for the internet, or off the worksheet it’s a great little tip. This was a tip from J-walk, back in the day.

Copying a picture to the windows clip board allows you to paste it directly into the controls picture property – nice

Lets get to it.

Tip 3. Debug>>Compile

A lot of the time F5  and running the project will flag errors in your code, but a more complete way to test the code via the complier is (obviously) to compile it, This is an option from the Debug menu, its  a lot faster to keep hitting this until the all the bugs are fixed. If you don’t do this give it a go, especially after you’ve done quite a bit a work.

Before and after, the Compile option is greyed out, alls well!

.

Pro tip – why not add a button to the tool bar. View > Toolbars > Customize > Commands > Debug .

So carrying on from my last post, here’s the next trick in the series.

My Definition is this…

In the IDE, under the View menu there’s an option called “Definition”, the short cut for this is Shift+F2, when the cursors is over a variable name, a function or sub call, this short cut will jump you to where the element is declared – great for navigation around large projects. I think it was Uncle Bob that put me on to this one.  As a bonus Ctrl+Shift+F2 will jump you right back to were you was, although I never uses this as I’m always having to make fixes once I get there!

Shift + F2, Ctrl + Shift + F2, nice!

As you can see this work across modules, a hand little tip, saves a sit of scrolling and so forth.

If you’ve got any cool tips, please let us all know!

With Christmas and New Year out of the way, I thought I’d put together this years blog post [:-)].

I was planning one big post with a few tips all at once, but I think I’ll do one at a time, because that way I might actually get some posts done!

You might well know these tricks, but I guess there will be some people who don’t,and maybe some you’ve forgotten, so its good to share them again every now and then!

By the way, if you have any other good ones, especially if there not well known , please add a comment and let usall  know.

So the first trick…

The Drag Back.

When you’re debug that little yellow arrow that pops up in the left hand margin bit, can be dragged forward and backwards through the code. Simply hover the mouse over the over the yellow arrow, left click and hold then drag.

The Drag Back

As you can see from the image above, you can drag that sucker back, forward, anywhere there not a “no entery” symbol, and you can get the values of variables form the tool tips as normal. Cool ah!

Good stuff, hopefully more to come soon