How To Position A User Form In Excel With VBA

The addition of a user form in an Excel worksheet can add a lot of functionality into your application. For example, you can use a form for data entry where you can programmatically check for errors and validation.

But sometimes the standard positioning is inconvenient and hides data in the sheet and you need to move the form before using it.

This article will explain how you can position the form using a few lines of VBA code.

Setting The User Form Start Up Position

First, you'll need to set the start up position to "manual" which you can do in the properties window of the form.

Before you activate the form, some calculations need to be performed to determine where you want the form to be displayed on the worksheet.

Our code will work out the width of a current region of data and position the form to the right of the table and just below the top of the screen.

First, we'll select the region of data:

set r=activeCell.currentRegion

We can get the width of the data table but need to take into account the frame of the worksheet.

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

myForm.left = fromLeft +20

One issue is that the calculation positions the form exactly on the edge of the table and you might need to move it slightly, depending on your preference. We've added 20 to the left position for some comfort.

We'll position the form slightly below the top of the screen so that it's away from any of the toolbars you might need to use.

fromTop = Application.Height - ActiveWindow.UsableHeight

myForm.top = fromTop

One issue might be that as you add data to the worksheet, the form might get in the way again. To solve this problem, you could add a refresh button which would reposition the form based on the code above whenever you required it.

Determining the position of a form in an Excel worksheet can be a challenging problem. A complete solution involves taking into account the existence of toobars which affect the height calculation but sometimes it can be just as effective to work out a practical solution for your situation.

Summary
With just a few lines of VBA code, we've been able to reposition a form to make it easier to use. As you increase your library of code snippets, you'll discover there's many ways you can use VBA to make your use of Excel more efficient and productive.