[comports]

Hi All,

I do hope you can help me..! I have to generate a word report from an xl spreadsheet of replies from a recent survey. I have lots of questions with % answers. No problem so far. I then create a graph based on the values. No problem. then I need to create a graph on the next question.and so on. I would like to write a macro that when I select the next selection of answers it creates a graph based on these and not the first set again..?

Does that make sense..?

Any help please.
Ash

[Glaswegian]

Hi and Welcome to TSG.

In the absence of any other replies.........

I'm not the best on charts - have you tried recording a macro while choosing your range and then creating the chart? Tools > Macro > Record New Macro. This will give the basic code which can then be modified to suit your particular requirements.

Regards

[comports]

Hi,

Thanks for this. I can create a Macro that will work on the first one. Its when I select the next range of cells and try the macro that it simply selects the first range and runs again. I want it to automatically use the selected range rather than "hard coded" range as it appears to be in the macro.

Ash

[Glaswegian]

Hi Ash

Try using an Input box like this

Sub test()
Dim myRng As Range

On Error Resume Next
Set myRng = Application.InputBox("Select a range", "Range Selection", Type:=8)
    If myRng Is Nothing Then
        MsgBox "Please select a range of cells", vbOKOnly + vbExclamation
            Else
                ' your chart code here
                ' use this to set the range as
                'the source data for your chart - SetSourceData Source:=myRng
    End If
On Error GoTo 0
End Sub

Does this help?

Regards

[comports]

Hi,

Thanks for this and it worked but I managed to find a way of doing it without having to inout the range manually.

Sub Macro3()

With Selection
Charts.Add
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="CorpAffairs!PMF 1 " ActiveChart.SeriesCollection(1).Select
With ActiveChart.ChartGroups(1)
.GapWidth = 150
.VaryByCategories = True
End With
With ActiveChart
.DepthPercent = 100
.GapDepth = 150
End With
ActiveChart.PlotArea.Select
With ActiveChart
.Elevation = 15
.Perspective = 30
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
End With
End With
End Sub

Now all I need to do is find a way for the chart to be placed on the Active sheet rather than specify it by name so I can use the macro on other sheets/woorkbooks..

Any ideas..?

Ash

[Glaswegian]

Ash

I think you may need to re-think the way you're doing this. Using the code you posted, as soon as the chart is created, it becomes active, therefore you will not have an activesheet (otherwise you would simply have used 'ActiveSheet').

I won't have time to put together some code today but perhaps you could upload a sample workbook with your exact requirements and I'll get back to you.

Regards

[comports]

Hi,

Thanks for all your help but late last night I managed to solve that and a couple of other issues.

Heres my latest code.

Sub Charting()
With Selection
Dim place
Dim Image
Dim Top
place = ActiveSheet.Name
Top = ActiveCell.Offset(-2, 0)
pos = ActiveCell.Offset(0, 4)
lftPos = ActiveCell.Offset(5, 0).Left
topPos = ActiveCell.Offset(5, 0).Top
Charts.Add
Image = ActiveChart.Name
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=place
ActiveChart.SeriesCollection(1).Select
With ActiveChart.ChartGroups(1)
.GapWidth = 150
.VaryByCategories = True
End With
With ActiveChart
.DepthPercent = 100
.GapDepth = 150
End With
With ActiveChart.Parent
.Height = 140 ' resize
.Width = 240 ' resize
.Top = topPos
.Left = lftPos
End With
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
With ActiveChart
.PlotArea.Width = 2.5 * 72
.HasTitle = True
.ChartTitle.Characters.Text = Top
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
End With
ActiveChart.Legend.Select
Selection.Delete
End With
End Sub

Sub addRow()
With Selection
i = 1
Do While i < 12
i = i + 1
Selection.Insert Shift:=xlDown
Loop
End With
End Sub

I would like to merge the "AddRow" into the top macro but it's throwing me athe moment. I would like ot click on one button, The macro would find the last row in the selection, Add 10 empty rows below, then create a graph from my original range and place it in the empty rows I just created. I'm losing it here as when I add the new rows I need to find the next empty row and select it to add the space. Only problem is my original range is no longer active so it can't build the chart..?

Frustrated......
Ash

[Glaswegian]

Without seeing your setup it's difficult to answer. You are using Select and Selection a great deal and that is usually not necessary. Better to define ranges and object variables and then use them in your code. Over use of selecting also slows down your code.

For example, in these lines

ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=place
ActiveChart.SeriesCollection(1).Select

VBA has to resolve the expression 'ActiveChart' for each line. More efficient to use a With...End With

With ActiveChart
.ChartType = xl3DColumnClustered
.Location Where:=xlLocationAsObject, Name:=place
.SeriesCollection(1).Select
End With

You are only making one reference to 'ActiveChart' and VBA knows that all lines after each full stop refer to that chart.

Regards

[comports]

Hi Glaswegian,

Can't find a way to attach my files for info so I'll paste a sample in here.

If you paste this into XL and then run the macro you will see what it now does.

If I select the row below "Total" I also have a small macro that adds 10 rows below that so the graph will fit in the space. Have also included my latest macro.

Handover process and responsibilities

Yes 72%
No 18%
Don't know 10%
Total*

The learning review

Yes 61%
No 16%
Don't know 12%
Total*

I have experienced problems using PMF

Yes 15%
No 76%
Don't know 9%
Total*


My Code:-

Sub Charting()

With Selection
Dim place
Dim Image
Dim Top
place = ActiveSheet.Name
Top = ActiveCell.Offset(-2, 0)
Pos = ActiveCell.Offset(0, 4)
lftPos = ActiveCell.Offset(5, 0).Left
topPos = ActiveCell.Offset(5, 0).Top
Charts.Add
Image = ActiveChart.Name

With ActiveChart
.ChartType = xl3DColumnClustered
.Location Where:=xlLocationAsObject, Name:=place
End With

ActiveChart.SeriesCollection(1).Select

With ActiveChart.ChartGroups(1)
.GapWidth = 150
.VaryByCategories = True
End With

With ActiveChart
.DepthPercent = 100
.GapDepth = 150
End With

ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select

With ActiveChart.Parent
.Height = 140 ' resize
.Width = 240 ' resize
.Top = topPos
.Left = lftPos
End With

With ActiveChart
.PlotArea.Width = 2.5 * 72
.HasTitle = True
.ChartTitle.Characters.Text = Top
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
End With

ActiveChart.Legend.Select
Selection.Delete
MsgBox Top + " graph complete"

End With
End Sub
Sub addRow()
With Selection
i = 1
Do While i < 10
i = i + 1
Selection.Insert Shift:=xlDown
Loop
End With
End Sub

Does this help at all and sorry to take up your time.

Ash

[cristobal03]

To attach files, if you go into an Advanced Reply (by clicking the Post Reply button), under the Reply to Thread frame there is an Additional Options frame. You can use the Manage Attachments button to upload files to the thread. However, if there is any proprietary information or sensitive data in your workbook you might want to make a copy of your workbook, dummy the data (i.e., make it up), and upload the copy. Something I also do just to be safe is clear all the fields under the Summary tab of the file properties (from the application toolbar, File-->Properties). There is also a size limit for what can be uploaded so you'll probably want to pare your copy a bit--deleting most of the charts should do it.

Just a random tip, I realize I haven't said anything important to this thread. Anyway.

chris.

[Glaswegian]

Ash

Thanks for that - if possible can you still follows Chris's instructions and post a sample workbook? I'll see if I can do something tonight - hope you're not in too much of a hurry!!

Chris - thanks for the help.

Regards

[comports]

Hi All,

Thanks for the help in uploading files. It makes it a whole lot easier. I guess I should use my eyes more..!!

I have included 2 files. ash-test xl and my macro code. If you select the row below the words Total on the xl sheet and run the AddRow macro it all works. Then select the range for each chart and run the other macro that all works. I would like to be able to include the AddRow part to my main macro and only run one. As the "total" row is not used I would like to find that row, replace it's content with nothing, Add 10 rows and then create and place the graph in that space. Perhaps it needs to be "create graph - find and delete "total" - add rows then position the graph there. The only problem is sometimes the tables have more than just 3 rows otherwise I would use ActiveCell.Offset from the inititial selection..!

Does that make sense.?

Ash

[Glaswegian]

Hi Ash

See if the attached now does what you want.

I've added two buttons to make it easier. Select the blank row under "Total" and then hit the 'Add Ten Rows' button. Then highlight your data and hit the 'Add a Chart' button. I took the liberty of amending your chart code to shorten it a bit.

Hope this works for you.

Regards