Displaying a "Chart" within Userform

[BrianExcel]

Each row in my spreadsheet contains a different product (Apples, Oranges, Bananas, etc).

The second, third and 4th columns are types of locations (Small, Medium, Large, etc).

What I would LOVE to do is place checkmarks on my userform1. The first will be apples, the second oranges, etc.

When I select a product, I want a grid on my userform showing which locations carry that product.

So when the form opens, it's blank except for three static columns, each of which is a store type.

When I click Apples, a first row is created on the form with "Apples" in the first cell. Then an "X" appears under Medium and Large because those stores carry them, but the small stores doesn't.

Any thoughts on how I could accomplish this?

 

[chuckchuckit]

Here is some info for "Charts inside UserForm's":

http://spreadsheetpage.com/index.php/site/tip/displaying_a_chart_in_a_userform/

 

[BrianExcel]

Thanks Chuck. I think my description was a bit off. I actually want to show a custom grid, not a chart.

I don't know if it's possible, but I wanted to update the case in case anyone else reads.

 

[chuckchuckit]

You might be able to make a custom grid and turn it into a .JPG and then import it into your UserForm, might be an other thought.

Private Sub UserForm_Initialize()
   Image1.Picture = LoadPicture("C:\Users\Office\Documents\Untitled-1.jpg")
End Sub

</PRE>

 

[chuckchuckit]

Looks like you cannot show a chart in a UserForm. It appears from my previous post that an other poster has said the same thing in post #2 on his point 4) that:

4) You cannot show a chart in an userform. The best one can do is create the chart in Excel, export it as an image, and import that image into the userform.

http://www.mrexcel.com/forum/showthread.php?t=561672&highlight=chart+userform

I did a search on "chart in UserForm" and a bunch of discussion popped up, but looks like they conclude the above.

Chuck

 

[chuckchuckit]

I am just starting to research a bit to do somethig similar. I want to show maybe 20 or more charts on a screen through Excel. And am wonder the same myself on best way.

1. Such as do 20 UserForms with .JPG copies in each one?
2. Or do 20 charts/graphs on an Excel spread sheet?

I am just starting to ponder such as I have all the data fields/ranges for the 20 charts, but do not know the best way to start to approach such...

I would appreciate your comments here as you progress, and so shall I.

Thanks.

Chuck

 

[hayden]

Per Chuck's request...

I had a series of charts that I wanted to show and decided to put them all on an excel sheet. What I did was create code that would produce two charts side by side, then loop so that I could fit a total of 4 charts on 1 8.5x11. If it would help to have the code, let me know and I can post it. Overall it worked very well for me.

Hayden

 

[chuckchuckit]

hayden,

For sure! Would appreciate the code for that if possible! And as I look through it I may want to ask questions too if that would be ok.

I will be gone for a few hours in a bit, but will be back.

Thanks much!

Chuck

 

[hayden]

First define sum public variables for the length, thickness, height and width of the chart and the spacing of the chart. The values get defined and used later.

Public l As Integer
Public t As Integer
Public h As Integer
Public w As Integer
Public sheetloc As String
Public multi As Integer

Here is the code that I used to create the page of charts. I've tried to comment most of it.

Sub updatecharts()
Dim origval As Integer
'24 rows, 12 pages, 4 charts per page.
'get chart number on modelinfo page so that we can set it back to that value at the end of sim.  (This part isn't needed and was used to display only one chart on another page)
origval = Sheets("ModelInfo").Range("a43").Value
'disable the on cell event for only one chart.
Application.EnableEvents = False
'build fist line of charts outside of loop You want to start here.
'define the thickness width height and length.
t = 26
w = 425
h = 300
l = 10
multi = 0 ' a variable to indicate 1 or multiple charts...
Dim chtcount As Integer ' chartcount
chtcount = 1
sheetloc = "HeadHydrographs"
Sheets("ModelInfo").Range("A43").Value = chtcount ' write the chart count
Call addchart ' see the next set of code
'Move over for the 2nd chart (i.e. the right chart)
t = 26
l = l + 20 + w
chtcount = 2
multi = 1
Sheets("ModelInfo").Range("A43").Value = chtcount
Call addchart
'Finished with the 1st 2 charts (right and left), now start loop for next row of charts
For i = 2 To 24 ' loop for chart row.
'Left chart
chtcount = chtcount + 1
t = t + h + 15
l = 10
Sheets("ModelInfo").Range("A43").Value = chtcount
Call addchart
'right chart
chtcount = chtcount + 1
l = l + 20 + w
Sheets("ModelInfo").Range("A43").Value = chtcount
Call addchart
Next
Sheets("ModelInfo").Range("a43").Value = origval
Application.EnableEvents = True
End Sub

Here is the code to add a chart. Alot of this is formatting stuff.

Sub addchart()
Dim startrow As String
Dim chartname As String
Dim endrow As String
Dim timerange As Range
Dim comprange As Range
Dim obsrange As Range
Dim MyChtObj As ChartObject
Dim charttitle As String
chartname = Sheets("ModelInfo").Range("b43").Value
charttitle = Sheets("ModelInfo").Range("b44").Value & " - " & Sheets("ModelInfo").Range("b2").Value
i = 5
startrow = Sheets("ModelInfo").Cells(i, 13).Value
'search through my list of data to find the chart I want. In my case I had another setup that only plotted one chart...
Do Until startrow = chartname
    If startrow <> charrtname Then
    i = i + 1
    startrow = Sheets("ModelInfo").Cells(i, 13).Value
    Else
    End If
Loop
' i is now the starting row
j = i
endrow = Sheets("ModelInfo").Cells(j, 13).Value
Do Until endrow <> chartname ' search for the end of the data for my chart
    If endrow = chartname Then
    j = j + 1
    endrow = Sheets("ModelInfo").Cells(j, 13).Value
    Else
    End If
Loop
'define ranges for chart
Set timerange = Sheets("ModelInfo").Range("x" & i & ":x" & j - 1)
Set obsrange = Sheets("ModelInfo").Range("y" & i & ":y" & j - 1)
Set comprange = Sheets("ModelInfo").Range("z" & i & ":z" & j - 1)
' Delete previous charts
Sheets(sheetloc).Activate
If multi = 0 Then
Do Until ActiveSheet.ChartObjects.Count = 0
ActiveSheet.ChartObjects(1).Delete
Loop
Else
End If
'add chart
Set MyChtObj = ActiveSheet.ChartObjects.Add(left:=l, width:=w, top:=t, Height:=h)

With MyChtObj.Chart
    .ChartType = xlXYScatterLines
    
'remvoe extra series
    Do Until .SeriesCollection.Count = 0
    .SeriesCollection(1).Delete
    Loop
    
    
End With
With MyChtObj.Chart.SeriesCollection.NewSeries
    .Name = "Observed"
    .Values = obsrange
    .XValues = timerange
End With

With MyChtObj.Chart.SeriesCollection.NewSeries
    .Name = "Computed"
    .Values = comprange
    .XValues = timerange
End With

With MyChtObj.Chart
    .PlotArea.Interior.ColorIndex = xlNone
    .Legend.Position = xlBottom
    .HasTitle = True
    .charttitle.Characters.Text = charttitle
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (days)"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Head (ft)"
End With
With MyChtObj.Chart.Axes(xlValue)
    .MajorUnitIsAuto = False
    .MajorUnit = 5
End With

End Sub

 

[chuckchuckit]

Hi hayden,

Thanks for the code!

Am trying to step through it with F8 and am wondering if there are some pieces missing, perhaps the worksheet "ModelInfo" needs some data in it? As the loops are not picking up any data.

Thanks.

Chuck