Sheet and userform
 

Hi,

is it possible to view and edit a part of a sheet in a userform (as a view
on the sheet)?

Thanks
Jos Vens

Sheet and userform
 

Hi Jos

Somewhere between "no" and "it's tricky". See if you're able to tame the
"Microsoft Office Spreadsheet Control" found in the "more controls" menu.

HTH. best wishes Harald

"Jos Vens" skrev i melding
...
Hi,

is it possible to view and edit a part of a sheet in a userform (as a view
on the sheet)?

Thanks
Jos Vens

Sheet and userform
 

Hi Harald,

it is just what I'm looking for, but two questions (or is there a help
function for?)

-how can I get data from my sheet (=Excel) into the form (=sheet embedded in
the form) and vice versa or are there more adequate methods to let the user
fill in a table within a form?
-does it work in every excel version (it should be from version 9 (= excel
2000) and above?

thanks
Jos


"Harald Staff" schreef in bericht
...
Hi Jos

Somewhere between "no" and "it's tricky". See if you're able to tame the
"Microsoft Office Spreadsheet Control" found in the "more controls" menu.

HTH. best wishes Harald

"Jos Vens" skrev i melding
...
Hi,

is it possible to view and edit a part of a sheet in a userform (as a
view
on the sheet)?

Thanks
Jos Vens

Sheet and userform
 

Hi Harald,

I must say that I save a lot of time, thanks to your kind answer! It makes
my program much better too.

the declaration was not necessary but it seems that the .value default
property of cells (and .formula works too!) in VBA is not working for a
embedded sheet in a form, so that's why it didn't work.

Thanks again for spending your time and helping me!!!
Jos Vens



"Harald Staff" schreef in bericht
...
Hi again Jos

Glad you can use that solution.
You haven't declared i and j, and ypou don't spesify WHAT you want to read
from the cells. Yoju should go for the Formula properties. This works
he

Private Sub UserForm_Initialize()
Dim i As Long, J As Long
For i = 1 To 10
For J = 1 To 10
sSheet.Cells(i, J).Formula = ActiveSheet.Cells(i, J).Formula
Next
Next
End Sub

You may also be a little safer if you spesify which sheet instead of
depending on wether one is Active or not. But this depends on what you are
going to use this for:

sSheet.Cells(i, J).Formula = ThisWorkbook.Sheets("Sheet1").Cells(i,
J).Formula

HTH. best wishes Harald

"Jos Vens" skrev i melding
...
Hi Harald,

I figured out how that the embedded spreadsheets can be filled with the
cells-command but I get a strange error (invalid argument).

Here's how I fill the sheet in the form (which is called sSheet)

Private Sub UserForm_Initialize()

For i = 1 To 10
For j = 1 To 10
sSheet.Cells(i, j) = ActiveSheet.Cells(i, j)
Next
Next

End Sub
e
If I replace the "ActiveSheet.Cells(i, j)" with a literal string e.g.
"A",
it works!

Refilling the Sheet from the embedded sheet works fine.

Can you help me?

Jos


"Harald Staff" schreef in bericht
...
Hi Jos

Somewhere between "no" and "it's tricky". See if you're able to tame
the
"Microsoft Office Spreadsheet Control" found in the "more controls"
menu.

HTH. best wishes Harald

"Jos Vens" skrev i melding
...
Hi,

is it possible to view and edit a part of a sheet in a userform (as a
view
on the sheet)?

Thanks
Jos Vens

Sheet and userform
 

"Jos Vens" skrev i melding
...
the declaration was not necessary

Maybe not. <old teacher It makes code and use of memory more efficient
though. </old teacher

but it seems that the .value default
property of cells (and .formula works too!) in VBA is not working for a
embedded sheet in a form, so that's why it didn't work.

I suspected something like that. Now we know.
Thanks for the feedback, glad it worked.

Best wishes Harald