Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Display a worksheet on a VBA form

  1. #1
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    [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?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom,

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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your Welcome

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •