embed a part of a worksheet in a user form?

[euroluce]

Hi - I'd like to be able to display a range of cells in a user form, and allow the users to select cells (individually or a range as per usual with excel) and then copy and paste between them.

Is this possible? I had found a way to embed a worksheet object in a form earlier on, but I could not work out how to put data into it from my existing worksheets, or otherwise use the embedded worksheet for anythign exctiting!

Thanks in advance for any ideas!

- dan

 

[Jaafar Tribak]

Hi,

Like you said you can insert a worksheet control in your userform and establish various links with a real worksheet via Form/Worksheet Events.

Below is an example of what you can do.
Basically what this code does is link all the data in the worksheet with the worksheet control when the userform is loaded.

Private Sub UserForm_Initialize()
    Dim addr As String
    
    With Sheets(1)
        addr = .UsedRange.Address
        Spreadsheet1.Range(addr).Value = .UsedRange.Value
    End With

End Sub

You can do so many things following the same technic.


Hope this helps.

 

[euroluce]

that sounds great, and is exactly what I want to do - saving me loads of other coding...

problem is that when I try the code you supplied, excel throws me a "Property let procedure not defined and property get procedure did not return an object (Error 451)" error when I try to show the form...

I set up a brand new workbook in excel 2000, added a user form and put your code in the code section for UserForm1

should the code you posted work or was it just an indication of what the "real" code might look like? :wink:

 

[Jaafar Tribak]

Hi,

The code should work provided that you first Embbed a Worksheet control from the Control toolbox in the same way as when you add a commandbutton or any other standard control.

You do this by right clicking on the control toolbox and addind a "Microsoft Worksheet 10.0" From the list.

Also make sure this new Spreadsheet control's name is Spreadsheet1 and the real worksheet's name is Sheet1 so it is consistant with my code.

Jaafar.

 

[euroluce]

hmm... may be the problem has to do with the fact that I'm using excel 2000 and the sheet I'm inserting in the form is a "Microsoft Office Spreadsheet 9" instead of a "Microsoft Worksheet 10.0"

 

[Ivan F Moala]

Range is NOT a Property of this control

Use The Activesheet

eg

Spreadsheet1.ActiveSheet.Range(addr).Value = .UsedRange.Value

 

[euroluce]

thanks! that worked a treat... got my values in the embedded sheet and looks like this will be a way simpler way to give my users a small "window" into only the data they are supposed to be playing with...

 

[manmoncang]

hmm... may be the problem has to do with the fact that I'm using excel 2000 and the sheet I'm inserting in the form is a "Microsoft Office Spreadsheet 9" instead of a "Microsoft Worksheet 10.0"

I dont have both. It's by default install or need to add in or whatever? tq

 

[Ruddles]

I've been lurking. Thanks, Jaafar!