Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to show a chart in a userform?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow, Scotland
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, can you help please? I have a workbook with multiple charts, what I want to do is present chart 1 on a user form press a button NEXT and chart 2 will replace chart 1 I followed an example on how to use a GIF file but cant get the code right to save the chart as a GIF, it's as follows;

    Private Sub UpdateChart()
    Set currentchart = Sheets("Graphs").ChartObjects(ChartNum).Chart

    Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
    currentchart.Export FileName:=Fname, FilterName:="GIF"

    Image1.Picture = LoadPicture(Fname)
    End Sub

    It stops at the first line so if anyone can help I would be most grateful.

  2. #2
    New Member
    Join Date
    Aug 2004
    Location
    Mid-West
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    I would like to know if anyone out there has a solution for the attched message as I have a similar chart need. Thanks

  3. #3
    New Member
    Join Date
    Aug 2004
    Location
    Mid-West
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    I would like to know if anyone out there has a solution for the attched message as I have a similar chart need. Thanks

  4. #4
    Board Regular
    The other chart guy
    Andy Pope's Avatar
    Join Date
    Dec 2003
    Location
    UK
    Posts
    346
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    Hi Alan,

    I would hazard a guess that the contents of the variable ChartNum does not match that of either of your 2 charts.

    What is the value of ChartNum when it fails?
    Also what does it say when it stops at line one?

  5. #5
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,199
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    Hi Guys.
    All new to me being on these forums. Fantastic info though. If I knew how to attach an Excel file, you can have a beautiful workbook that I downloaded a while ago. It is a picture viewer with "next" and "previous" buttons. Let me know how to attach the file please.
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    This site does not provide for an attachment of files, but how about if you post the code in that workbook you downloaded. For my web site (not yet published) I recently wrote an example workbook showing how to do this. In composing my example, it became necessary to solve a number if mini-obstacles along the way. I (and probably others) would be interested to see how your workbook approached and solved the same issues.

  7. #7
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,199
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    This is the language in the Forms sheet. Remember, it was another gentleman who did all the work. I just copied it. If I knew his name, I would mention it here. A gentleman by the name of Andy Pope also has an image viewer.

    Code in Formviewer Forms:
    'Array to save Picture file pathes
    Private folderPath As String
    Private fileNames() As String
    Private position As Integer
    Private nrOfPictures As Integer

    'Variables and Constants needed for Zooming and Displaying the Image
    Const MAX_ZOOM = 8 '800% of the original size
    Const MIN_ZOOM = 0.05 '5% of the original size
    Private m_sngWidth As Single
    Private m_sngHeight As Single
    Private m_sngZoom As Single

    'Load Image
    Private Sub LoadImage(Name As String)
    If Dir(Name) <> "" Then
    With imgMain
    .AutoSize = True
    .Picture = LoadPicture(Name)
    m_sngHeight = .height
    m_sngWidth = .width
    .AutoSize = False
    .PictureSizeMode = fmPictureSizeModeStretch
    m_sngZoom = 1
    .Left = 1
    .Top = 1
    'Reset ZoomButtons
    cmdZoomIn.Enabled = True
    cmdZoomOut.Enabled = True
    End With
    SetZoom m_sngZoom
    Else
    MsgBox ("File " & Name & " could not be loaded.")
    End If
    End Sub

    'Set the Zoom Factor
    Private Sub SetZoom(Zoom As Single)
    With imgMain
    .width = m_sngWidth * Zoom
    .height = m_sngHeight * Zoom
    End With

    lblZoom.Caption = "Zoom: " & Format(Zoom, "0%")

    With frmImage
    .ScrollHeight = imgMain.height + 2
    .ScrollWidth = imgMain.width + 2
    End With
    End Sub

    Private Sub cbbFiles_Change()
    Dim selectedFile As String
    Dim i As Integer
    Dim found As Boolean

    comboBoxClicked = False
    found = False
    selectedFile = folderPath + cbbFiles.Text

    For i = 0 To (nrOfPictures - 1)
    If (fileNames(i) = selectedFile) Then
    LoadImage selectedFile
    lblInfo = fileNames(i)
    cbbFiles.Text = fileNames(i)
    found = True
    Exit For
    End If
    Next i

    If (found = True) Then
    position = i
    End If
    End Sub

    Private Sub CmdButton1_Click()
    Unload Me
    End Sub

    Private Sub CommandButton1_Click()
    Image1.Picture = LoadPicture("")
    End Sub

    Private Sub frmImage_Click()

    End Sub

    'On Initalization get all Picture Files and save them to an array
    Private Sub UserForm_Initialize()
    Dim wbCodeBook As Workbook

    Set wbCodeBook = ActiveWorkbook
    folderPath = wbCodeBook.path + "\"

    GetAllPictureFiles
    initializeUserForm
    End Sub

    'Prepare User Form
    Private Sub initializeUserForm()
    Dim pos As Integer
    Dim i As Integer

    position = 0

    If (nrOfPictures > 0) Then
    LoadImage fileNames(position)
    lblInfo = fileNames(position)

    pos = InStrRev(fileNames(0), "\") + 1

    cbbFiles.Clear

    For i = 0 To (nrOfPictures - 1)
    cbbFiles.AddItem (Mid(fileNames(i), pos))
    Next i

    cbbFiles.Text = fileNames(position)
    Else
    imgMain.Picture = Nothing

    cbbFiles.Clear

    cbbFiles.Text = "Could not find pictures..."
    End If
    End Sub

    'Show next Picture
    Private Sub cmdNext_Click()
    NextPicture
    End Sub

    Private Sub NextPicture()
    If (position < (nrOfPictures - 1)) Then
    position = position + 1
    Else
    position = 0
    End If

    LoadImage fileNames(position)
    lblInfo = fileNames(position)
    cbbFiles.Text = fileNames(position)
    End Sub

    'Show previous picture
    Private Sub cmdPrev_Click()
    previousPicture
    End Sub

    Private Sub previousPicture()
    If (position > 0) Then
    position = position - 1
    Else
    position = nrOfPictures - 1
    End If

    LoadImage fileNames(position)
    lblInfo = fileNames(position)
    cbbFiles.Text = fileNames(position)
    End Sub

    'Save all picture files (their pathes) to an array
    Sub GetAllPictureFiles()

    Dim i As Integer
    ReDim fileNames(0)
    nrOfPictures = 0

    getFilesOfType "*.jpg", folderPath
    getFilesOfType "*.gif", folderPath
    getFilesOfType "*.bmp", folderPath

    BubbleSortUp fileNames()

    End Sub

    'Get Files of Type XXX
    Private Sub getFilesOfType(imageType As String, searchPath As String)
    With Application.FileSearch
    .NewSearch
    .LookIn = searchPath
    .FileType = msoFileTypeAllFiles
    .fileName = imageType

    If .Execute > 0 Then
    ReDim Preserve fileNames(nrOfPictures + .FoundFiles.Count)
    For i = 1 To .FoundFiles.Count
    fileNames(nrOfPictures + i - 1) = .FoundFiles(i)
    Next i
    nrOfPictures = nrOfPictures + i - 1
    End If
    End With
    End Sub

    'Bubble Sort Ascending
    Sub BubbleSortUp(sToSort() As String)
    Dim Lower As Integer, Upper As Integer
    Dim i As Integer, J As Integer, K As Integer
    Dim Temp As String

    Lower = LBound(sToSort)
    Upper = UBound(sToSort) - 1

    For i = Lower To Upper - 1

    K = i
    For J = i + 1 To Upper
    If sToSort(K) > sToSort(J) Then
    K = J
    End If
    Next J

    If i <> K Then
    Temp = sToSort(i)
    sToSort(i) = sToSort(K)
    sToSort(K) = Temp
    End If
    Next i
    End Sub

    'Zoom Buttons Functionality
    Private Sub cmdZoomIn_Click()
    m_sngZoom = m_sngZoom + 0.2

    If (m_sngZoom >= MAX_ZOOM) Then
    m_sngZoom = MAX_ZOOM
    End If

    cmdZoomIn.Enabled = m_sngZoom <> MAX_ZOOM
    cmdZoomOut.Enabled = m_sngZoom <> MIN_ZOOM

    SetZoom m_sngZoom
    End Sub

    Private Sub cmdZoomOut_Click()
    m_sngZoom = m_sngZoom - 0.2

    If (m_sngZoom <= MIN_ZOOM) Then
    m_sngZoom = MIN_ZOOM
    End If

    cmdZoomIn.Enabled = m_sngZoom <> MAX_ZOOM
    cmdZoomOut.Enabled = m_sngZoom <> MIN_ZOOM

    SetZoom m_sngZoom
    End Sub

    'Open Another Directory
    Private Sub cmdOpen_Click()
    Dim dirName As String

    dirName = GetDirectory

    folderPath = dirName + "\"

    GetAllPictureFiles
    initializeUserForm
    End Sub

    Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

    'Root folder = Desktop
    bInfo.pidlRoot = 0&

    'Title in the dialog
    If IsMissing(Msg) Then
    bInfo.lpszTitle = "Select a folder."
    Else
    bInfo.lpszTitle = Msg
    End If

    'Type of directory to return
    bInfo.ulFlags = &H1

    'Display the dialog
    x = SHBrowseForFolder(bInfo)

    'Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
    pos = InStr(path, Chr$(0))
    GetDirectory = Left(path, pos - 1)
    Else
    GetDirectory = ""
    End If
    End Function


    The language in Module1 is as follows:

    code:
    'Needed for selecting a Folder
    Option Explicit
    Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    '32-bit API declarations
    Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
    As Long

    Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

    'Show User Form
    Sub pictureViewer()
    formViewer.Show
    End Sub

    Good Luck and Regards
    John
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  8. #8
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to show a chart in a userform?

    That workbook from jolivanes is superb...really nice.



    Here is what I did recently regarding this topic:

    (1)
    Established a dedicated folder and path named "C:\Your\ChartFile\Path\" (though the code can be modified to delete and re-establish any directory name).

    In the userform's Initialization event...

    (2)
    ...Delete all chart photo files (actually all files to be on the safe side) from your dedicated folder.

    (3)
    ...Repopulate your dedicated folder with updated chart photos. This gives the userform presentation more meaning because the most recent chart data is shown.

    (4)
    ...Embedded chart objects are strange because their name involves the parent sheet name. I parsed that sheet name out of the chart name for a better-looking chart name that will show up in the ComboBox, without the .gif extension.

    (5)
    ...Populate the ComboBox with the chart photo file names, without the extension.

    (6)
    The ComboBox change event populates an info label and loads the selected file name onto the Image control. I used a ComboBox instead of command buttons because oftentimes people want to see the names of what they are selecting, especially if the audience requests a specific file to be seen, instead of flipping through the in-between ones as with a command button. That said, I repeat that the workbook from jolivanes is really well done, just excellent.



    I know some people cannot, or are not allowed to, download attached files, so here is a description of my userform and the code:

    There are 5 controls:

    1) Label header at the top, advising user to select a file name from the ComboBox.
    2) ComboBox to select a file name
    3) Label (optional) below the CoimboBox and above the Image control, displaying the name of the file photo for the viewer's benefit (triggered by cbo Change).
    4) Image control
    5) Exit button.

    The workbook itself has 5 chart examples (all live), one on each worksheet, though you can have as many as you want. I did not involve chart sheets with this; I never use those.

    Here is the code, with notes. If anyone is interested in seeing the actual workbook, PM me and I will be happy to send you a copy. It is one of several I created for my upcoming web site that would be a free download.



    Option Explicit

    'Tip - - Beforehand, give meaningful names to your charts with code like this:
    'Sheet1.ChartObjects(1).Name = "Revenue Sources"
    'Sheet2.ChartObjects(1).Name = "Month Quantity"
    'Sheet3.ChartObjects(1).Name = "Sales Dollars"
    'Sheet4.ChartObjects(1).Name = "Expenses Distribution"
    'Sheet5.ChartObjects(1).Name = "Inventory by year"


    Private Sub UserForm_Initialize()

    'Step 1
    'Open a With structure for the Application object and prepare Excel.
    With Application
    .ScreenUpdating = False
    .EnableEvents = False

    'Step 2
    'Declare and define variables.
    Dim MyPath As String, ASN As String, ASC As String, ACN As String, ACNN As String
    Dim ws As Worksheet, ChObj As Object
    Dim i As Integer, n As Integer, ASCLen As Integer
    Dim fA() As String, dName As String
    MyPath = "C:\Your\ChartFile\Path\"
    ASN = ActiveSheet.Name

    'Step 3
    'Delete all chart photo files (actually all files to be on the safe side) from your folder.
    With .FileSearch
    .NewSearch
    .LookIn = MyPath
    .FileType = 1
    .SearchSubFolders = True
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count
    Kill .FoundFiles(i)
    Next i
    End If
    End With

    'Step 4
    'Repopulate your folder with updated chart photos.
    For Each ws In Worksheets
    'a
    ws.Activate 'Sheet activated so chart can be activated.
    For Each ChObj In ws.ChartObjects
    'b
    ASC = ActiveSheet.Name 'Sheet name length will need to be known in Step 4c.
    'c
    ASCLen = Len(ASC) 'Sheet name length defined, invoked in Step 4f.
    'd
    ChObj.Activate 'Chart must be activated to be exported.
    'e
    ACN = ActiveChart.Name 'Entire chart name includes parent sheet name and chart name.
    'f
    ACNN = Right(ACN, Len(ACN) - ASCLen - 1) 'Parse out the sheet name for a true chart object name.
    'g
    ActiveChart.Export MyPath & ACNN & ".gif" 'Save the chart object name as a .gif to folder defined in Step 2.
    'h
    Range("A1").Activate 'Deactivate the chart object by activating a worksheet cell.
    Next ChObj 'For some versions, this is a necessary step.
    Next ws

    'Step 5
    'End up on the sheet you were on when you called the userform, for the user's convenience.
    Sheets(ASN).Activate

    'Step 6
    'Load the ComboBox with the names of the chart photos
    ChDir MyPath
    dName = Dir("*") 'All files (which will only be the 'gif's from Step 4g).
    Do While dName <> ""
    n = n + 1
    ReDim Preserve fA(1 To n)
    fA(n) = dName
    dName = Dir()
    Loop
    For i = 1 To n
    cboChartNames.AddItem Left(fA(i), .Find(".", fA(i)) - 1) 'True chart object name without .gif extension
    Next 'for cleaner look in ComboBox.

    'Step 7
    'Restore Excel and close the With structure for the Application object.
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub

    Private Sub cboChartNames_Change()
    'Notice the " & ".gif" " code, to properly refer to the ComboBox item
    'that was loaded into the ComboBox in Step 6 of the Inialization event.
    imgShowCharts.Picture = LoadPicture(cboChartNames.Value & ".gif")
    'A label above the Image control is optional, to serve as a header
    'for the Image, identifying each new chart as it is selected and displayed.
    Me.lblChartName.Caption = "This chart's name is ''" & cboChartNames.Value & "''."
    End Sub

    Private Sub cmdExit_Click()
    Unload Me
    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
  •