Lock out macros and charts

[supercrewed]

I have a few questions, the first is; can I lock out the macros in a workbook, so they cannot be edited?
Next; Can I add the year only to a chart title, so it will change at years end to the New Year? I have the date created, but I’m lost as to the correct syntax, for the year only.
Lastly; I have created a sub to generate a chart, on an existing sheet, but I’m confused as to formatting it, in the following code what am I doing wrong. I'm using some code from the macro recorder, I edited / modified, and in the code below, how do I change the gridlines, back color, and so on?
Could someone point me in the right direction, Thanks…?

Sub AddChartObject()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
END_SELECT = 1<o></o>
<o> </o>
Dim myChtObj As ChartObject<o></o>
'************************************* SETS CHART, SHEET, RANGE, CHART TYPE ***********<o></o>
<o> </o>
Sheets("SUMMARY").Select<o></o>
ActiveSheet.Cells(2, 2).Select<o></o>
ActiveSheet.ChartObjects.Delete<o></o>
'*************************************SELECTS CELLS THAT ARE FILLED ********************<o></o>
Do<o></o>
If IsEmpty(ActiveCell) = False Then<o></o>
ActiveCell.Offset(1, 0).Select<o></o>
End If<o></o>
END_SELECT = END_SELECT + 1<o></o>
Loop Until IsEmpty(ActiveCell) = True<o></o>
<o> </o>
With NON_CONF_REPORT_FORM<o></o>
If .TOP_FIVE = True Then<o></o>
END_SELECT = 6<o></o>
ElseIf .TOP_TEN = True Then<o></o>
END_SELECT = 11<o></o>
Else<o></o>
END_SELECT = END_SELECT<o></o>
End If<o></o>
End With<o></o>
<o> </o>
<o> </o>
'************************************* SETS CHART, SHEET, RANGE, CHART TYPE ***********<o></o>
Set myChtObj = ActiveSheet.ChartObjects.Add(Left:=330, Width:=650, Top:=10, Height:=385)<o></o>
With myChtObj.Chart<o></o>
<o></o>
.SetSourceData Source:=Sheets("SUMMARY").Range("B1:C" & CStr(END_SELECT)), PlotBy _<o></o>
:=xlColumns<o></o>
'.ChartType = xlBar 'xlLineMarkers<o></o>
.HasLegend = False<o></o>
.HasDataTable = False<o></o>
<o> </o>
'************************************* FORMATS CHART TITLE****************************<o></o>
<o> </o>
.ChartTitle.Caption = "REWORK / REMAKE TOTALS AS OF - " & FormatDateTime(Date, 2)<o></o>
.ChartTitle.Font.Size = 16<o></o>
.ChartTitle.Font.Name = "Times New Roman"<o></o>
.ChartTitle.Font.Bold = True<o></o>
<o> </o>
End With<o></o>
'**************************************** FORMATS Y AXES *****************************<o></o>
<o> </o>
With myChtObj.Chart.Axes(xlValue)<o></o>
.MinimumScaleIsAuto = True<o></o>
.MaximumScaleIsAuto = True<o></o>
.MinorUnitIsAuto = True<o></o>
.MajorUnitIsAuto = True<o></o>
.Crosses = xlAutomatic<o></o>
.ReversePlotOrder = False<o></o>
.ScaleType = xlLinear<o></o>
.DisplayUnit = xlNone<o></o>
.TickLabels.Orientation = 0<o></o>
<o></o>
End With<o></o>
<o> </o>
'**************************************** Y AXES *****************************<o></o>
With myChtObj.Chart.Axes(Type:=xlValue, AxisGroup:=xlPrimary)<o></o>
.HasTitle = True<o></o>
.AxisTitle.Characters.Text = "Dollars Lost"<o></o>
.AxisTitle.Font.Name = "Times New Roman"<o></o>
.AxisTitle.Font.Size = 14<o></o>
End With<o></o>
<o> </o>
'**************************************** X AXES *****************************<o></o>
<o></o>
With myChtObj.Chart.Axes(Type:=xlCategory, AxisGroup:=xlPrimary)<o></o>
.HasTitle = True<o></o>
.AxisTitle.Text = "Emplyees"<o></o>
.AxisTitle.Font.Name = "Times New Roman"<o></o>
.AxisTitle.Font.Size = 14<o></o>
.HasMajorGridlines = True<o></o>
.HasMinorGridlines = False<o></o>
.TickLabels.Orientation = 90<o></o>
<o></o>
End With<o></o>
<o></o>
End Sub

 

[tusharm]

1) In the VB Editor use Tools | VBAProject... to add a password.

2) To get the year from a date use the YEAR function.

3) Use the macro recorder. Best way to get the necessary code. If you are using 2007 and do not get the code, chances are better (though no guarantee) that 2003 will give you the necessary code.

 

[supercrewed]

Thanks as for the formatting, I've all but got it, just messing around, I'm beginning to understand it. I'll try the year function, can't be too difficult, Thanks for your help...

 

[supercrewed]

Thanks, Year(Now()), works perfect...

 

[tusharm]