How To: Record Your Own Macro

Comments

1. Postal Bob says

   Friday, October 17, 2008 at 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

2. Jon Peltier says

   Friday, October 17, 2008 at 12:57 pm

   Bob –

   Here’s a procedure that will clear unprotected cells in the active sheet. Place it in a regular module in the workbook.

   Sub ClearUnlockedCells()
     Dim r As Range
     Dim iCalc As Long
     
     Application.ScreenUpdating = False
     iCalc = Application.Calculation
     Application.Calculation = xlCalculationManual
     For Each r In ActiveSheet.UsedRange.Cells
       If Not r.Locked Then
         r.ClearContents
       End If
     Next
     Application.ScreenUpdating = True
     Application.Calculation = iCalc
   End Sub

    
   Don’t use a cell as the trigger, open the Forms toolbar, and stick a button on the sheet. When the Assign Macro dialog pops up, select the above macro. Then change the caption on the button to something like “Clear Inputs”. Then protect the sheet.

3. Fred says

   Monday, September 19, 2011 at 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

4. Jon Peltier says

   Monday, September 19, 2011 at 3:40 pm

   Fred –

   Microsoft had to rush at the end of the Office 2007 development cycle to prepare it for release, that they did not have resources to update the macro recorder for the brand new Office shapes. This affects charts too, since the pieces that comprise the charts are also new Office shapes.

   Excel 2010 has a lot of the macro recorder goodies that Excel 2007 lacks.

5. Fred says

   Monday, September 19, 2011 at 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?

6. Jon Peltier says

   Monday, September 19, 2011 at 4:09 pm

   Fred –

   I inserted, moved, and resized a rectangle in Excel 2010 with the macro recorder on, and got this code:

   Sub Macro2()
       ActiveSheet.Shapes.AddShape(msoShapeRectangle, 153.75, 63, 176.25, 63).Select
       Selection.ShapeRange.IncrementLeft 202.5
       Selection.ShapeRange.IncrementTop 89.25
       Selection.ShapeRange.ScaleWidth 0.4127659574, msoFalse, msoScaleFromBottomRight
   End Sub

   In general I prefer using explicit size and position statements:

   With ActiveSheet.Shapes(1)
       .Left = 100
       .Top = 75
       .Width = 125
       .Height = 75
   End With

7. Fred says

   Monday, September 19, 2011 at 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

8. Asad says

   Wednesday, September 21, 2011 at 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

9. Jon Peltier says

   Wednesday, September 21, 2011 at 6:33 am

   Asad –

   If there’s an unprotected active sheet, that line of code ought to work.

10. steve says

    Monday, September 3, 2012 at 3:41 am

    how to make makro to put the current date and current time in specifed cell

11. Jon Peltier says

    Monday, September 3, 2012 at 11:01 am

    Steve –

    Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.