Calling Excel 2007 VBA Experts

foozball3000

foozball3000

Executive Member
Joined
Oct 28, 2008
Messages
5,929
Reaction score
1,738
Location
Kyalami
We present our analytic results in Excel.
We have a Standard Template that contains a lot of Macro's, and we then 'inject' our results into this template. These Macro's were written for Office 2003, but it worked fine in office 2007 until this weekend. :confused:

The problem is that the Macro that clears the spreadsheet before a different test's results are displayed, is not doing its job. I need a new VBA Script that Clears everything off the sheet, including charts and other objects.

And then I need to find a way, that that script can be updated into Results that we've already given to clients.

Any help?
 
Paste the code here, so we can look.

U may have to make the macro delete the sheet and then add a new one with the same name.
That works.

Haven't used 2007 yet, getting it in a few months.
 
This is the method that is supposed to delete all the charts...
Step by step, everything works, it selects the charts, but the delete statement doesn't delete them...

Sub delChart()
Dim chartObj As ChartObject

On Error Resume Next
For Each chartObj In ActiveSheet.ChartObjects
chartObj.Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
Next chartObj
End Sub
 
Thanks, it works. But how do I update previous releases?
Hmmm.... I'll email someone on the .Net forums tomorrow.
 
I'm abusing google to find a solution for this.. perhaps someone on myBB knows how to do it:
Can you create a function similar to Try & Catch? Apparently its not standard with VBA. But it is needed in this case...
Some versions of Excel uses Selection.Delete and others use Selection.Clear... the best way to cater for both versions is with a Try and Catch statement...
 
Found the Code:

Selection.Delete
On Error GoTo errFoo
errFoo:
Selection.Clear
Resume Next
 
New Objective:
Seeing as Excel is VBA, I want to change the standard white background to a gradient one... I have no idea how to create a Gradient Brush in VBA. I can do it in C# and VB.Net.
Any pointers?
 
foozball3000 said:
New Objective:
Seeing as Excel is VBA, I want to change the standard white background to a gradient one... I have no idea how to create a Gradient Brush in VBA. I can do it in C# and VB.Net.
Any pointers?
Click to expand...

Use the macro recorder, and change it.
Then check the code from the macro recorder.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X