Is it possible to display worksheets cells in a userform

[Guest]

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.

 

[Ardus Petus]

You will have to define one TextBox per cell to display

HTH

 

[Guest]

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.

 

[Guest]

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

 

[Jim May]

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

 

[Gary Keramidas]

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

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

Tom,

As always thanks, I will check this out.

 

[Jim May]

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...

 

[MilkmanDan]

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

 

[Tim Williams]

The Range object also has a CopyPicure method.