There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash drive driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet java laptop malware memory monitor motherboard music network obp printer problem ram random registry router security slow software sound trojan usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
XL Macro Help Needed

Reply  
Thread Tools
comports's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
05-Sep-2005, 06:48 AM #1
XL Macro Help Needed
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's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
06-Sep-2005, 06:15 AM #2
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
__________________
Member of ASAP Member of UNITE

Defender of the Haggis and all things Scottish.
comports's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
06-Sep-2005, 06:31 AM #3
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's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
06-Sep-2005, 06:51 AM #4
Hi Ash

Try using an Input box like this
Code:
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's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
06-Sep-2005, 01:30 PM #5
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's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
07-Sep-2005, 06:17 AM #6
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
__________________
Member of ASAP Member of UNITE

Defender of the Haggis and all things Scottish.
comports's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
07-Sep-2005, 08:24 AM #7
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's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
07-Sep-2005, 10:08 AM #8
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
__________________
Member of ASAP Member of UNITE

Defender of the Haggis and all things Scottish.
comports's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
07-Sep-2005, 11:46 AM #9
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's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
07-Sep-2005, 12:33 PM #10
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's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
07-Sep-2005, 12:51 PM #11
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's Avatar
Junior Member with 6 posts.
 
Join Date: Sep 2005
Experience: Intermediate
07-Sep-2005, 02:18 PM #12
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
Attached Files
File Type: xls ash-test.xls (21.0 KB, 103 views)
File Type: txt macro.txt (1.5 KB, 111 views)
Glaswegian's Avatar
Computer Specs
Malware Removal Specialist with 3,119 posts.
 
Join Date: Dec 2004
Location: Erm...Glasgow?
Experience: of what?
07-Sep-2005, 06:44 PM #13
XL Charts
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
Attached Files
File Type: xls ash-test v2.xls (41.0 KB, 111 views)
__________________
Member of ASAP Member of UNITE

Defender of the Haggis and all things Scottish.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 08:20 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.