Embedding spreadsheet in a user form

[GeorgeJ]

I am writing a complex VBA program. I know how to protect a worksheet from
data and format and structure changes, but is there any way to make the user
unable to change the size and screen location of a currently displayed
worksheet? Is there any way to embed a worksheet onto a user form so that it
occupies a certain position on the user form. I desire a situation in which
a "window" on the user form contains a worksheet. Within this "window" the
user can move the cursor in the worksheet and the worksheet's scroll-bars are
displayed and active and the PageUP and PageDown controls work, but the user
can not change any of the data or formatting or structure af the worksheet,
and he cannot change its size or location within the worksheet's "window" on
the user form. In other words, I want something like a list box but I want
to have the formatting and display options (such as text-wrap, color,
co-lwidth, font, etc.) which, as far as I know, are only available on a
worksheet.

Any suggestions as to how to get this would be appreciated.

 

[Jim Cone]

There is a "spreadsheet" control you can add to a userform.
It looks like, um, a spreadsheet and may do what you want (you will have to experiment).
You can add it to the tool box using "additional controls".
Find that by right-clicking the tool box.
The one you want is "Microsoft Office Spreadsheet xx.o"
A nice feature, is that it comes with its own help.
Click the "?" at the top right after it is added to the userform.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"GeorgeJ" <[email protected]>
wrote in message
I am writing a complex VBA program. I know how to protect a worksheet from
data and format and structure changes, but is there any way to make the user
unable to change the size and screen location of a currently displayed
worksheet? Is there any way to embed a worksheet onto a user form so that it
occupies a certain position on the user form. I desire a situation in which
a "window" on the user form contains a worksheet. Within this "window" the
user can move the cursor in the worksheet and the worksheet's scroll-bars are
displayed and active and the PageUP and PageDown controls work, but the user
can not change any of the data or formatting or structure af the worksheet,
and he cannot change its size or location within the worksheet's "window" on
the user form. In other words, I want something like a list box but I want
to have the formatting and display options (such as text-wrap, color,
co-lwidth, font, etc.) which, as far as I know, are only available on a
worksheet.

Any suggestions as to how to get this would be appreciated.

 

[GeorgeJ]

Thank you Jim. I was unaware that one could add more types of controls by
right clicking the control box. However, I was *not* able to find the one
you referred to, specifically "Microsoft Office Spreadsheet xx.o". When I
right clicked in the control box there appeared a drop down list of
additional possible controls. They were arranged alphabetically and the
only ones starting with "Microsoft Office" were of the form "Microsoft Office
Outlook ...". I didn't see any additional controls that contained the word
"spreadsheet". I am using Excel 2007.

I would like to resubmitt my original question. Is it possible to lock a
worksheet so that

a) the user cannot move the cursor outside of a certain row range and a
certain column range ?

b) the use cannot change the location on the screen of the window displaying
the worksheet, and he cannot change the size of the window displaying the
worksheet?

Thanks

 

[Jim Cone]

You may want to start over with a new post.
I would not have attempted to answer your question if you had mentioned XL2007.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"GeorgeJ" <[email protected]>
wrote in message
Thank you Jim. I was unaware that one could add more types of controls by
right clicking the control box. However, I was *not* able to find the one
you referred to, specifically "Microsoft Office Spreadsheet xx.o". When I
right clicked in the control box there appeared a drop down list of
additional possible controls. They were arranged alphabetically and the
only ones starting with "Microsoft Office" were of the form "Microsoft Office
Outlook ...". I didn't see any additional controls that contained the word
"spreadsheet". I am using Excel 2007.

I would like to resubmitt my original question. Is it possible to lock a
worksheet so that

a) the user cannot move the cursor outside of a certain row range and a
certain column range ?

b) the use cannot change the location on the screen of the window displaying
the worksheet, and he cannot change the size of the window displaying the
worksheet?

Thanks