I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value = Workbooks(€œmyWorkbook€�).Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error €œRun-time error €˜-2147352571 (80020005)
Could not set the Value property. Type mismatch.€�

I also tried:

myuserform.mytextbox.Text = Workbooks(€œmyWorkbook€�).Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with plain
text in the Userform.

--
Trefor

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

Ardus,

Many thanks for the reply, not quite what I was hoping for, I have a table 2
tables each 2 x 20 cells, not sure that I fancy coding up 80 text boxes and
then presumably fixing up the colours afterwards. Thanks anyway, I will try
and think of a plan B.

--
Trefor


"Ardus Petus" wrote:

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

you could use copyaspicture to copy the range and display it in the userform.
Stephen Bullen has documented the procedure to do it with a chart, but is
should be adaptable to using a picture of the range:

http://www.oaltd.co.uk/Excel/Default.htm

--
Regards,
Tom Ogilvy


"Trefor" wrote:

Ardus,

Many thanks for the reply, not quite what I was hoping for, I have a table 2
tables each 2 x 20 cells, not sure that I fancy coding up 80 text boxes and
then presumably fixing up the colours afterwards. Thanks anyway, I will try
and think of a plan B.

--
Trefor


"Ardus Petus" wrote:

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

In VBE mode With your Userform selected and your Toolbox visible,
right-click on a blank area of the toolbox, select more options... Scroll
down to Microsoft Excel Spreadsheet, Your version? and select it.
The icon should now be a part of your toolbox; Click on it and drag it to
your Userform.
HTH
Jim May

"Tom Ogilvy" wrote in message
...
you could use copyaspicture to copy the range and display it in the
userform.
Stephen Bullen has documented the procedure to do it with a chart, but is
should be adaptable to using a picture of the range:

http://www.oaltd.co.uk/Excel/Default.htm

--
Regards,
Tom Ogilvy


"Trefor" wrote:

Ardus,

Many thanks for the reply, not quite what I was hoping for, I have a
table 2
tables each 2 x 20 cells, not sure that I fancy coding up 80 text boxes
and
then presumably fixing up the colours afterwards. Thanks anyway, I will
try
and think of a plan B.

--
Trefor


"Ardus Petus" wrote:

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have
also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur
formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange
covers
more than one cell I get an error "Run-time error '-2147352571
(80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange").
Text

and this does not error, but it only display the first cell in a
column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

Jim,

Thanks for the reply, not having seen this before (saw some other great
stuff as well in the selection list), what do you do with the spreadsheet one
you have on the form? How do I get my sheet or range into it?

--
Trefor


"Jim May" wrote:

In VBE mode With your Userform selected and your Toolbox visible,
right-click on a blank area of the toolbox, select more options... Scroll
down to Microsoft Excel Spreadsheet, Your version? and select it.
The icon should now be a part of your toolbox; Click on it and drag it to
your Userform.
HTH
Jim May

"Tom Ogilvy" wrote in message
...
you could use copyaspicture to copy the range and display it in the
userform.
Stephen Bullen has documented the procedure to do it with a chart, but is
should be adaptable to using a picture of the range:

http://www.oaltd.co.uk/Excel/Default.htm

--
Regards,
Tom Ogilvy


"Trefor" wrote:

Ardus,

Many thanks for the reply, not quite what I was hoping for, I have a
table 2
tables each 2 x 20 cells, not sure that I fancy coding up 80 text boxes
and
then presumably fixing up the colours afterwards. Thanks anyway, I will
try
and think of a plan B.

--
Trefor


"Ardus Petus" wrote:

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have
also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur
formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange
covers
more than one cell I get an error "Run-time error '-2147352571
(80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange").
Text

and this does not error, but it only display the first cell in a
column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

Tom,

As always thanks, I will check this out.

--
Trefor


"Tom Ogilvy" wrote:

you could use copyaspicture to copy the range and display it in the userform.
Stephen Bullen has documented the procedure to do it with a chart, but is
should be adaptable to using a picture of the range:

http://www.oaltd.co.uk/Excel/Default.htm

--
Regards,
Tom Ogilvy


"Trefor" wrote:

Ardus,

Many thanks for the reply, not quite what I was hoping for, I have a table 2
tables each 2 x 20 cells, not sure that I fancy coding up 80 text boxes and
then presumably fixing up the colours afterwards. Thanks anyway, I will try
and think of a plan B.

--
Trefor


"Ardus Petus" wrote:

You will have to define one TextBox per cell to display

HTH
--
AP

"Trefor" a écrit dans le message de
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting
and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value =
Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with
plain
text in the Userform.

--
Trefor

this may or may not help, because it does not do everything you requested. i set
up a 4 x 4 table and a userform with 12 text boxes( textbox1 thru textbox 12).

this code fills in the data, but no formatting.

Private Sub UserForm_Activate()
Dim myArray As Variant

Dim i As Long, j As Long, z As Long
j = 1
z = 1
myArray = Worksheets("sheet1").Range("Data2").Value
For i = LBound(myArray) To UBound(myArray)
For j = LBound(myArray) To UBound(myArray)
Me.Controls("Textbox" & z).Value = myArray(i, j)
z = z + 1
Next
Next
End Sub

--


Gary


"Trefor" wrote in message
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value = Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with plain
text in the Userform.

--
Trefor

Gary,

Thanks for the reply, but really looking for the formating as well. I have
several different sheets, so this could be really time consuming in code.

--
Trefor


"Gary Keramidas" wrote:

this may or may not help, because it does not do everything you requested. i set
up a 4 x 4 table and a userform with 12 text boxes( textbox1 thru textbox 12).

this code fills in the data, but no formatting.

Private Sub UserForm_Activate()
Dim myArray As Variant

Dim i As Long, j As Long, z As Long
j = 1
z = 1
myArray = Worksheets("sheet1").Range("Data2").Value
For i = LBound(myArray) To UBound(myArray)
For j = LBound(myArray) To UBound(myArray)
Me.Controls("Textbox" & z).Value = myArray(i, j)
z = z + 1
Next
Next
End Sub

--


Gary


"Trefor" wrote in message
...

I have a worksheet that has some text and formula in a table. I have also
used conditional formatting in the table to give be different colour
depending on the results in some of the cells.

I would like to take the entire table including all the colur formatting and
display if in a User form, is this possible?

I have tried:

myuserform.mytextbox.Value = Workbooks("myWorkbook").Range("MyRange").Value

If MyRange equals a single cell then it works fine, but if MyRange covers
more than one cell I get an error "Run-time error '-2147352571 (80020005)'
Could not set the Value property. Type mismatch."

I also tried:

myuserform.mytextbox.Text = Workbooks("myWorkbook").Range("MyRange"). Text

and this does not error, but it only display the first cell in a column of
three values.

In either case all formatting appeared lost as I simply ended up with plain
text in the Userform.

--
Trefor