Where to place this control?

[Lou_Reed]

As can be seen from the attached pdf file I have a button that is located in the middle of the Excel spreadsheet. It works to do several thing to prepare


a document for exporting to MS Access as a table.

It works, but the button is located in an awkward place. It is located where the data will be located. It is okay that it is there now, but it clearly will be a problem
when data is put in those cells.

My question is where can I put it so it will be functional, but still available to the user who wants to prepare the Excel spreadsheet
for exportingl?

Any Help appreciated. Thank in advance.

Respectfully,

Lou Reed

[ArviLaanemets]

At top of sheet (above your table) add some empty rows;
Use Freeze panes to set table header and rows above it always visible for user;
Place your button above your table (at top of worksheet).

The rows at top of page are also perfect place for SUM's, COUNT's etc. They will be always visible, and you can add data to your table without inserting rows or dragging cells with formulas to new location every time.

[ssanfu]

Ack!! I didn't want to do this, but it hurts to watch you try to import a spreadsheet. Now you want to have the users modify the spreadsheet......???

Try the attached dB with the sample workbook you provided...

Column "A" of the worksheet MUST have values to have that row imported. And the code ends when the cell in the worksheet is empty.
If there are values in column A from row 6 to row 30, then there are values from row 40 to 45, only rows 6 to 30 will be imported.

The code could be modified to find the last row with data, but I am tired and heading home.

Look at the code to see where you could change settings...

[Lou_Reed]

I am not sure what Freeze panes are. Do you have a reference?

When you say place the button at the top of the table, do you mean completely out of the cell
region?

I just want to keep this basic.

Any help appreciated. Thanks in advance.

Lou Reed

[ArviLaanemets]

Freeze Panes:
Activate a cell on worksheet;
From Menu, open View. There is a menu item Freeze Panes (approximately in center of menu) - click on it;
Now, when you scroll the worksheet down or right, rows above the cell you activated before and columns left at same cell remain always visible.
Selecting Freeze panes, when it was activated previously, again unfreezes previous activation, i.e. all rows and columns can be hidden when scrolling the sheet.

At top of page means in rows above table header.

P.e. you create a table with column headers in A5:F5, select cell D6 and activate Freeze Panes. The area A1:C3 you use to place a button (so it will be always wisible). Into cell E1 you enter "Total:" , and into cell F1 you enter SUBTOTAL formula to calculate sum of filtered entries in F column of your table (NB! In my example I left out row 4 - because when you define a table, it will be better to have an empty row immediately above table header. You can hide this row, or set it's height p.e. to 2.).