newbie Q - Macro wont run with sheet protected.

[etb75]

I have a sheet for data entry. On that sheet a button to run macro to scale the graphs on sheet 2. If I protect sheet 2 though because I am thinking of letting others use this program. It gives me an error 400. Any ideas?????

 

[Datsmart]

Have your code un-protect the sheet, scale the chart, then re-protect the sheet.

 

[etb75]

thus the "newbie"... How would I do that? Here is the code for the scale graphs. Now remember I am no expert obviously. I wrote this code from copying others and trial and error...


Sub ScaleGraphs_Click()
Dim lngBasePremium As Long
Worksheets("print report").Activate
lngBasePremium = Range("H54")
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium
.MaximumScale = lngBasePremium * 2
.MinorUnit = lngBasePremium * 0.1
.MajorUnit = lngBasePremium * 0.25
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium
.MaximumScale = lngBasePremium * 2
.MinorUnit = lngBasePremium * 0.1
.MajorUnit = lngBasePremium * 0.25
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium * 0.6
.MaximumScale = lngBasePremium * 1.5
.MinorUnit = lngBasePremium * 0.02
.MajorUnit = lngBasePremium * 0.1
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium * 0.6
.MaximumScale = lngBasePremium * 1.5
.MinorUnit = lngBasePremium * 0.02
.MajorUnit = lngBasePremium * 0.1
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium * 0.8
.MaximumScale = lngBasePremium * 1.8
.MinorUnit = lngBasePremium * 0.02
.MajorUnit = lngBasePremium * 0.1
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = lngBasePremium * 0.8
.MaximumScale = lngBasePremium * 1.8
.MinorUnit = lngBasePremium * 0.02
.MajorUnit = lngBasePremium * 0.1
.Crosses = xlCustom
.CrossesAt = lngBasePremium
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
Range("A1").Select

End Sub

 

[etb75]

NEVER MIND, i think i got ya. Can just record a macro of unpretecting and past that in front of the current code, then run protecting and paste that at the end.. RIGHT?

 

[Datsmart]

Note the "ActiveSheet.Unprotect" and "Protect" lines I added to your code.
I used jrr for the password.

Sub ScaleGraphs_Click()
Dim lngBasePremium As Long
Worksheets("print report").Activate
    ActiveSheet.Unprotect "jrr" 'Where jrr is the password
lngBasePremium = Range("H54")
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium
    .MaximumScale = lngBasePremium * 2
    .MinorUnit = lngBasePremium * 0.1
    .MajorUnit = lngBasePremium * 0.25
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium
    .MaximumScale = lngBasePremium * 2
    .MinorUnit = lngBasePremium * 0.1
    .MajorUnit = lngBasePremium * 0.25
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium * 0.6
    .MaximumScale = lngBasePremium * 1.5
    .MinorUnit = lngBasePremium * 0.02
    .MajorUnit = lngBasePremium * 0.1
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium * 0.6
    .MaximumScale = lngBasePremium * 1.5
    .MinorUnit = lngBasePremium * 0.02
    .MajorUnit = lngBasePremium * 0.1
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium * 0.8
    .MaximumScale = lngBasePremium * 1.8
    .MinorUnit = lngBasePremium * 0.02
    .MajorUnit = lngBasePremium * 0.1
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = lngBasePremium * 0.8
    .MaximumScale = lngBasePremium * 1.8
    .MinorUnit = lngBasePremium * 0.02
    .MajorUnit = lngBasePremium * 0.1
    .Crosses = xlCustom
    .CrossesAt = lngBasePremium
    .ReversePlotOrder = False
    .ScaleType = xlLinear
End With
Range("A1").Select
    ActiveSheet.Protect "jrr"
End Sub

 

[CW-NEN]

You got it. I use the protect/unprotect all the time. That would be the best way to get the code, if you are not super confident.

 

[etb75]

SWEET...

I tried it but i guess i didnt do my copy and paste of the code that i ran correctly, oh wait, i put activate cell A1 after proctecting... Duh...

Question, where you wrote "where jrr is the password" Is that just for the user, or is that code being used in the macro? Because when I recorded the macros for protect and unprotect it did not have that text.

THANKS this helps so much. It amazing because I am sure you guys could make the illustrations and data entry that we use look some much better... Anyways, thanks...

 

[Datsmart]

The text in the code was for your instruction and it does use jrr as a password.
Did you use a password when you recorded your macro? If so, you should have it listed in the code.

 

[etb75]

I got ya... I took your code and changed to the password i have been using. HOWEVER, problem, and I know most of the users i am concerned with will not know how to do this but - After protecting the sheet and the workbook. All macros working fine etc etc... If i go into the file and right click the sheet, it says view code, well under code it shows me the password. Any ideas???

 

[ADAMC]

Password protect your VBE code.

In the visual basic editor choose Tools> VBA project Properties

choose the protection tab> check the lock project from viewing box and apply a password