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

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

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

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

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

I just selected a range of cells in another workbook's sheet and copied them
and then made my Userform spreadsheet active and pasted in the copied info;
seemed to work, in that case...

Experiment, and see what else is available...


"Trefor" wrote in message
...
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

I'm in pretty much the exact same situation....

I have several tabs of tables with summary data that indicates state
with conditional formatting, and in building a VB front end I want to
display those tables in userforms with the conditional formatting.

Looking through the suggestions I've seen here and elsewhere, it really
seems that Bullen's approach seems like it's the right direction. As
for modifying it to work on a spreadsheet range instead of a
chart.....can anyone help me figure out how to do that? (been searching
and not feeling like I'm getting closer"). Here's the code from
Bullen's example......


Code:
--------------------
Private Sub UpdateChart()

Dim oCht As Chart, lPicType As Long

'Find the chart object on the sheet
Set oCht = Sheet1.ChartObjects(1).Chart

'Recalculate the sheet to give us a new set of random points
Sheet1.Calculate

'Do we want a metafile or a bitmap?
'If doing a 1 to 1 copy, xlBitmap will give a 'truer' rendition.
'If scaling the image, xlPicture will give better results
lPicType = IIf(obMetafile, xlPicture, xlBitmap)

'Update the chart type and copy it to the clipboard, as seen on screen
With oCht
.ChartType = iChartType
.CopyPicture xlScreen, lPicType, xlScreen
End With

'Paste the picture from the clipboard into our image control
Set imgChtPic.Picture = PastePicture(lPicType)

End Sub
--------------------


--
MilkmanDan
------------------------------------------------------------------------
MilkmanDan's Profile: http://www.excelforum.com/member.php...o&userid=34538
View this thread: http://www.excelforum.com/showthread...hreadid=533369

The Range object also has a CopyPicure method.

--
Tim Williams
Palo Alto, CA


"MilkmanDan" wrote in message
...

I'm in pretty much the exact same situation....

I have several tabs of tables with summary data that indicates state
with conditional formatting, and in building a VB front end I want to
display those tables in userforms with the conditional formatting.

Looking through the suggestions I've seen here and elsewhere, it really
seems that Bullen's approach seems like it's the right direction. As
for modifying it to work on a spreadsheet range instead of a
chart.....can anyone help me figure out how to do that? (been searching
and not feeling like I'm getting closer"). Here's the code from
Bullen's example......


Code:
--------------------
Private Sub UpdateChart()

Dim oCht As Chart, lPicType As Long

'Find the chart object on the sheet
Set oCht = Sheet1.ChartObjects(1).Chart

'Recalculate the sheet to give us a new set of random points
Sheet1.Calculate

'Do we want a metafile or a bitmap?
'If doing a 1 to 1 copy, xlBitmap will give a 'truer' rendition.
'If scaling the image, xlPicture will give better results
lPicType = IIf(obMetafile, xlPicture, xlBitmap)

'Update the chart type and copy it to the clipboard, as seen on screen
With oCht
.ChartType = iChartType
.CopyPicture xlScreen, lPicType, xlScreen
End With

'Paste the picture from the clipboard into our image control
Set imgChtPic.Picture = PastePicture(lPicType)

End Sub
--------------------


--
MilkmanDan
------------------------------------------------------------------------
MilkmanDan's Profile: http://www.excelforum.com/member.php...o&userid=34538
View this thread: http://www.excelforum.com/showthread...hreadid=533369