How To: Record Your Own Macro

Comment from Postal Bob
Time: Friday, October 17, 2008, 12:10 pm

I need instructions on how to program 1 cell on a worksheet so that when you click on it it deletes the data that has been entered in unprotected cells. Can this be done? I have to present this to my boss the end of next week and he’s a computerphob, the easier it is the better he likes it. Thanks

Comment from Fred
Time: Monday, September 19, 2011, 2:50 pm

Hi,

Why the macro recorder doesn’t record any action on objects? For exemple I would like to add a “Round Single Corner Rectangle” and modify some properties of this shape in VBA. The easiest way should be to record a macro and then use some elements of it to write my own code but I got nothing…

Thanks

Comment from Fred
Time: Monday, September 19, 2011, 4:03 pm

Thanks for the quick answer.

In fact I’am currently using Excel 2010 and I have nothing recorded in my macro when, for exemple, I change dimensions of a rectangle . May be there’s an option to select when we work with shapes? Any ideas?

Comment from Fred
Time: Monday, September 19, 2011, 5:52 pm

You were right. I hadn’t noticed yet that IT service had installed Office 2007 instead of 2010 on my new laptop. Sorry for this mistake. I prefer explicit statements too. The macro recorder can be usefull to obtain the exact VBA shape name…
Thanks again

Comment from Asad
Time: Wednesday, September 21, 2011, 12:14 am

Hi Jon,
I had a recorded macro for chart creation in 2003 and it does not work in 2007.
I had used your dynamic charting by dates procedure to make the chart.
Can you help ? It stops at the Set ChtObj line.

Sub MainGraph(wanum As String)
‘
‘ Macro1 Macro
‘
Dim wsItem As Worksheet
Dim chtObj As ChartObject
Dim chrObj As Integer
For Each wsItem In ThisWorkbook.Worksheets

For Each chtObj In wsItem.ChartObjects

chtObj.Delete

Next

Next

Set chtObj = ActiveSheet.ChartObjects.Add(Left:=250, Width:=700, Top:=170, Height:=400)

With chtObj.Chart
‘ remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
‘
chrObj = 1
‘Charts.Add

With chtObj.Chart
.ChartType = xlLine
.SeriesCollection.NewSeries.XValues = “=APChart.xls!ChartDates”

.SeriesCollection(1).Name = “Estimate”
.SeriesCollection(1).Values = “=APChart.xls!ChartFirmA”

.SeriesCollection.NewSeries.XValues = “=APChart.xls!ChartDates”

.SeriesCollection(2).Name = “Actuals”
.SeriesCollection(2).Values = “=APChart.xls!ChartFirmB”

.Location Where:=xlLocationAsObject, Name:=”NewAP”
.Parent.Name = “CHART” & chrObj
End With

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = “Estimate/Actuals ” & wanumb & Space(5) & “(As of ” & Date & “)”
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “Years/Months”
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Hours”
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = 5
.MarkerForegroundColorIndex = 5
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 6
.Shadow = False
End With

ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With

ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = -1
End With
Selection.Interior.ColorIndex = xlAutomatic
Sheets(“New787″).DrawingObjects(“CHART” & chrObj).RoundedCorners = False
Sheets(“New787″).DrawingObjects(“CHART” & chrObj).Shadow = False
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With

ActiveSheet.ChartObjects(“CHART” & chrObj).Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = “0″
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
‘Windows(“APChart.xls”).Activate
‘Range(“U40″).Select
ActiveSheet.Shapes(“CHART” & chrObj).Select
Selection.Locked = False
ActiveSheet.Protect
ActiveWindow.ScrollRow = 7
With ActiveChart
.Legend.Top = 5
.Legend.Left = 5
End With

‘Call ExportChart
‘ ActiveSheet.Shapes(“CHART” & chrObj).Select
Windows(“APChart.xls”).Activate
Range(“A3″).Select
End Sub