Hi guys,
Hopefully a simple question, but It's driving me crazy ...
I have a book, with one sheet and one form. Inside the form a embbed a
Spreadsheet 9.0 object.
Sheet1 has 2 values, let's say "value 1" and "value 2", on cells A1 and
A2 (very simple so far)
I want to: upload the values whe the form appears, and unload changes
when the form disappears (hopefully still simple). Loading no problem.
Unloading d$%"$·!.
So here's the code inside UserForm1:

Private Sub UserForm_Initialize()
Sheet1.Range("A1").CurrentRegion.Copy
Spreadsheet1.Cells.Range("A1").Paste
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Dim myRange As Range
Set myRange = Spreadsheet1.Cells.Range("A1").CurrentRegion '<<<
TYPE MISMATCH ERROR
myRange.Copy
Sheet1.Paste
End Sub

As far as I can see, myRange is Range but
Spreadsheet1.Cells.Range("A1").CurrentRegion is a VALUE!!!
Look the Inmidiate:
print Spreadsheet1.Cells.Range("A1").CurrentRegion
value 1

print Spreadsheet1.Cells.Range("A1").CurrentRegion.Count
2
Why CurrentRegion is a VALUE!!!! why why why why why why why why!!!!

try this

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

Dim MyRange as OWC.Range
set MyRange = me.SpreadSheet1.Cells.Range("A1").CurrentRegion
rng.copy
Sheet1.Paste
End Sub

In the debug window you are getting the default property (value) of the
OWC.Range object.

You should use

? Typename(me.SpreadSheet1.Cells.Range("A1").Current Region)

But I don't think the result will be helpful since it will just report
Range.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Hi guys,
Hopefully a simple question, but It's driving me crazy ...
I have a book, with one sheet and one form. Inside the form a embbed a
Spreadsheet 9.0 object.
Sheet1 has 2 values, let's say "value 1" and "value 2", on cells A1 and
A2 (very simple so far)
I want to: upload the values whe the form appears, and unload changes
when the form disappears (hopefully still simple). Loading no problem.
Unloading d$%"$·!.
So here's the code inside UserForm1:

Private Sub UserForm_Initialize()
Sheet1.Range("A1").CurrentRegion.Copy
Spreadsheet1.Cells.Range("A1").Paste
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Dim myRange As Range
Set myRange = Spreadsheet1.Cells.Range("A1").CurrentRegion '<<<
TYPE MISMATCH ERROR
myRange.Copy
Sheet1.Paste
End Sub

As far as I can see, myRange is Range but
Spreadsheet1.Cells.Range("A1").CurrentRegion is a VALUE!!!
Look the Inmidiate:
print Spreadsheet1.Cells.Range("A1").CurrentRegion
value 1

print Spreadsheet1.Cells.Range("A1").CurrentRegion.Count
2
Why CurrentRegion is a VALUE!!!! why why why why why why why why!!!!

I knew it!!!!!! OWC!!!! thanks Tom