How To Position A User Form In Excel With VBA

How To Position A User Form In Excel With VBA

The addition of a consumer kind in an Excel worksheet can add a number of performance into your utility. For instance, you should use a kind for information entry the place you may programmatically test for errors and validation.

However generally the usual positioning is inconvenient and hides information within the sheet and it’s essential transfer the shape earlier than utilizing it.

This text will clarify how one can place the shape utilizing a number of traces of VBA code.

Setting The Person Kind Begin Up Place

First, you may must set the beginning up place to “guide” which you are able to do within the properties window of the shape.

Earlier than you activate the shape, some calculations have to be carried out to find out the place you need the shape to be displayed on the worksheet.

Our code will work out the width of a present area of information and place the shape to the precise of the desk and just under the highest of the display screen.

First, we’ll choose the area of information:

set r=activeCell.currentRegion

We are able to get the width of the info desk however must take into consideration the body of the worksheet.

fromLeft = r.Width + Utility.Width - ActiveWindow.UsableWidth

myForm.left = fromLeft +20

One situation is that the calculation positions the shape precisely on the sting of the desk and also you would possibly want to maneuver it barely, relying in your choice. We have added 20 to the left place for some consolation.

We’ll place the shape barely under the highest of the display screen in order that it is away from any of the toolbars you would possibly want to make use of.

fromTop = Utility.Top - ActiveWindow.UsableHeight

myForm.high = fromTop

One situation could be that as you add information to the worksheet, the shape would possibly get in the best way once more. To resolve this drawback, you might add a refresh button which might reposition the shape primarily based on the code above everytime you required it.

Figuring out the place of a kind in an Excel worksheet generally is a difficult drawback. An entire answer includes bearing in mind the existence of toobars which have an effect on the peak calculation however generally it may be simply as efficient to work out a sensible answer to your scenario.

Abstract

With only a few traces of VBA code, we have been capable of reposition a kind to make it simpler to make use of. As you enhance your library of code snippets, you may uncover there’s some ways you should use VBA to make your use of Excel extra environment friendly and productive.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.