Display a worksheet on a VBA form

[Colo]

Hi All.
The following method which I wrote is a provisional one.
Before execution, you need to prepare UserForm1 with Image Control(Image1).

Sub Test()

  Dim objTemp As Object, MyChart As Chart, rngImg As Range

  Selection.Copy

  Set rngImg = Application.InputBox("Pls select range", Type:=8)

  rngImg.Copy

  Set objTemp = ActiveSheet.Shapes.AddShape(1, 1, 1, 1, 1)

  objTemp.Select

  ActiveSheet.Paste

  objTemp.Delete

  With Selection

      .CopyPicture 1, 2

      Set MyChart = ActiveSheet.ChartObjects.Add _

                              (1, 1, .Width, .Height).Chart

  With MyChart

      .Paste

      .Export "Temp.jpg"

      .Parent.Delete

  End With

  .Delete

  End With

  With UserForm1.Controls("Image1")

    .Picture = LoadPicture("Temp.jpg")

    .PictureSizeMode = fmPictureSizeModeZoom

  End With

  Kill "Temp.jpg"

  UserForm1.Show

End Sub

[Colo]

[Posted: 2002-01-14 18:46 ?]
Something strange. I checked date of my pc, but it was correct.
I had better stop any post from now on?

[Suresh_36]

Is it possible to display a worksheet or a part of it on a userform/ text box (in Excel 97 and above)

[Tom Schreiner]

Hi
I know you can in VB, not sure in VBA.
Will check now...

Yes, as a matter of fact you can...
In VBA environment choose tools, additional controls, Microsoft Office Spreadsheet...
An Excel icon will be added to your control toolbox...
Simply choose it and draw it onto your form
Tom

[Suresh_36]

Hi Tom,

Yes indeed, thank you very much for the prompt reply.

[Tom Schreiner]

Your Welcome

[Yogi Anand]

Hi Tom and Suresh:
I am using Excel 97 and I don't see it in additional controls. Could it be becauase you are using a later version of Excel?

[Tom Schreiner]

Hi Yogi.
Search for this file in your system folder.
I am not sure if this is standard with office or Visual Studio?
If you need it and it is legal, I'll email it to you.
Actually, it's in the office folder.

MSOWC.DLL

Tom

[ This Message was edited by: TsTom on 2002-04-14 08:24 ]

[Sbirro Cornuto]

This works great for my purposes, except that the image is kind of blurry, you know it´s not too sharp. I guess that´s the way with .jpg. I would think that bitmap (.bmp) format would look better, but don´t know how to make it use .bmp file. Just changing the extension doesn´t seem to work . . .

Can anyone help me?

Thx in advance

On 2002-01-14 18:46, Colo wrote:
Hi All.
The following method which I wrote is a provisional one.
Before execution, you need to prepare UserForm1 with Image Control(Image1).

Sub Test()

  Dim objTemp As Object, MyChart As Chart, rngImg As Range

  Selection.Copy

  Set rngImg = Application.InputBox("Pls select range", Type:=8)

  rngImg.Copy

  Set objTemp = ActiveSheet.Shapes.AddShape(1, 1, 1, 1, 1)

  objTemp.Select

  ActiveSheet.Paste

  objTemp.Delete

  With Selection

      .CopyPicture 1, 2

      Set MyChart = ActiveSheet.ChartObjects.Add _

                              (1, 1, .Width, .Height).Chart

  With MyChart

      .Paste

      .Export "Temp.jpg"

      .Parent.Delete

  End With

  .Delete

  End With

  With UserForm1.Controls("Image1")

    .Picture = LoadPicture("Temp.jpg")

    .PictureSizeMode = fmPictureSizeModeZoom

  End With

  Kill "Temp.jpg"

  UserForm1.Show

End Sub