Excel property needed

Hi all,

I hope someone here is able to help me.

First a little background on the problem. I am doing an applikation in Access forms, with VBA and in one of my forms I embedded an Excel document. Now this Excel (2000) document is causing me some problems, since I cant get it to show me a dynamic amount of cells in the Access form.
But, I almost think I solved it. This means if I can find this last property to manipulate in VBA. All I know about it is, that its called "Name Box" if you hover over it in Excel.

It gets activated when you select a bunch of cells and says something like 4R x 3C (4 rows times 3 collums).
What I would like to is programmicly set this property to give me YR x 20C (Y is dynamic) and selected from A1 cell.

See the attached picture, to see where to find the "Name Box" - I hope someone is able to help me or know of something that can 'select' the rows and collums.

Thank you, John

Hi MartinShort,

I tryed something like that earlyer, but got an error so thought I did something wrong. I just tryed your code and it gives me the following error

Run-time error '1004':
Method 'Range' of object'_Global' failed

at

Please Login or Register  to view this content.

I tryed with diffrent Y's cause it might be outofbound.

What I tryed earlyer was something like worksheet.range("a1:f4").resize or so

Thank you, John

Hi Martin,

I just open'd a new Excel sheet and tryed out your code - Yes it works!!

But sadly its not working in the application im using it in. I am doing an application in Access forms, where I put an Excel document in a Unbound OLE object. And if I put the code there, and let Access control the properties of Excel it created the above error - I control everything else from Access and thereby prevent the 'macro warning' to pop up if I put code in Excel documents.

See the attached picture for more explaination - I am trying to resize the size of the Excel view, I can do it manualy with the mouse but havent found code for it. I thought it would be linked to the "Name Box" cause thats where it shows the RxC size.

Cheers for the helping, John

Hi Martin,

Np at all - im just glad for the help, and finding a solution to this.

I tryed your code, and yes again it works - I diddent doubt it.

The diffrence now between our two "applications" is that I want this to happen in a form, inside a '(U)Bound Object Frame' - so that the excel sheet is actualy showing inside that Object Frame. Keeping the interface of my form the same, without having an excel window pop up.

So, create a form, inside that form create an Unbound Object Frame and select Excel Worksheet. It is here the problem is, everything else you've show works like a charm.

Not sure how else to explain it, sorry.

Cheers, John

Ohh btw,

I would like to find the solution to this problem, but ive made a work around:

Workaround: Create a default sized Excel dokument 10R x 20C - what I now do is insted of resizing the internal Excel dokument, I desided to create a 'tab' order. So if more items then 10R reside in my Excel dokument, I tab over to a "new" page, which is actualy just the same 10R x 20C, just with rewritten data in it from my vector.

- insted of tabs you could also write a scrollbar that follows a sertain index.

But just to know how excel works inside a form object frame would be nice, since it lessens the amount of code to be written.

John