Macro with Drop Down Box on a Protected Sheet?

[JumboCactuar]

Hi,
i cant figure why this isnt working, when i change the selection "The cell your trying to change is on a protected sheet"

Sub DataChange()
Activesheet.Unprotect "password"
If Range("e30") = 1 Then


Range("c6").FormulaR1C1 = "=IF(Pivot!RC[17]="""","""",Pivot!RC[17])"


ElseIf Range("e30") = 2 Then


Range("C6").FormulaR1C1 = "=IF(Pivot!RC[41]="""","""",Pivot!RC[41])"


ElseIf Range("e30") = 3 Then


Range("c6").FormulaR1C1 = "=IF(Pivot!RC[62]="""","""",Pivot!RC[62])"


EndIf
Activesheet.Protect "password"

End Sub

e30 is the linked cell to the dropdown box which has 3 options.
It works fine unprotected but i need to protect the sheet. I have tried the protect code as shown above and also inside the IF statements, no luck

any help appreciated

 

[jeffreybrown]

Did you unlock cell E30 to begin with?

Select cell E30 >> right click >> Format Cells >> Protection Tab >> Uncheck Locked

Also, why not put this code in a Worksheet Change Event?

 

[JumboCactuar]

Did you unlock cell E30 to begin with?

Select cell E30 >> right click >> Format Cells >> Protection Tab >> Uncheck Locked
ive tried this and also uncheck locked on the dropdown box, no luck

Also, why not put this code in a Worksheet Change Event?
can you give an example?

thanks

 

[jeffreybrown]

Try...

Right click on the sheet which contains the E30 where you will be choosing 1, 2, or 3

On E30, enter a data validation:

Data >> Data Tools >> Data Validation >> Allow: List >> Source: 1,2,3

Now just select from E30 and the code will fire

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E30")) Is Nothing Then
    ActiveSheet.Unprotect "password"
    If Range("E30") = 1 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[17]="""","""",Pivot!RC[17])"
    ElseIf Range("E30") = 2 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[41]="""","""",Pivot!RC[41])"
    ElseIf Range("E30") = 3 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[62]="""","""",Pivot!RC[62])"
    End If
    ActiveSheet.Protect "password"
    End If
End Sub

 

[JumboCactuar]

ADVERTISEMENT

Try...

Right click on the sheet which contains the E30 where you will be choosing 1, 2, or 3

On E30, enter a data validation:

Data >> Data Tools >> Data Validation >> Allow: List >> Source: 1,2,3

Now just select from E30 and the code will fire

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E30")) Is Nothing Then
    ActiveSheet.Unprotect "password"
    If Range("E30") = 1 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[17]="""","""",Pivot!RC[17])"
    ElseIf Range("E30") = 2 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[41]="""","""",Pivot!RC[41])"
    ElseIf Range("E30") = 3 Then
        Range("C6").FormulaR1C1 = "=IF(Pivot!RC[62]="""","""",Pivot!RC[62])"
    End If
    ActiveSheet.Protect "password"
    End If
End Sub

Tried this but an error pops up "Argument not Optional"

also not sure why i need data validation when E30 is linked to the dropdown

Dropdown Box changes the shift
Default (1) - Shift 1
(2) - Shift 2
(3) - Shift 3

when changed it pulls data from a pivot table into the current sheet depending on shift.

Format control
Input range: $C$30:$C$32
Cell Link: $E$30

 

[jeffreybrown]

What exactly did you try?

I don't want to guess at anything, so what steps did you take?

Is there anything else in the sheet code or just this?

I opened a blank workbook, add the code to the sheet the E30 range and the choices 1,2 and 3.

This only other sheet I have to the workbook is the Pivot sheet.

I select a choice from E30 and the code fires placing a formula in C6 of the same sheet with E30.

 

[jeffreybrown]

ADVERTISEMENT

Tried this but an error pops up "Argument not Optional"

also not sure why i need data validation when E30 is linked to the dropdown

Dropdown Box changes the shift
Default
(1) - Shift 1
(2) - Shift 2
(3) - Shift 3

when changed it pulls data from a pivot table into the current sheet depending on shift.

Format control
Input range: $C$30:$C$32
Cell Link: $E$30

In my opinion, get ride of the control and use the data validation. Much simpler.

 

[JumboCactuar]

What exactly did you try?

I don't want to guess at anything, so what steps did you take?

Is there anything else in the sheet code or just this?

I opened a blank workbook, add the code to the sheet the E30 range and the choices 1,2 and 3.

This only other sheet I have to the workbook is the Pivot sheet.

I select a choice from E30 and the code fires placing a formula in C6 of the same sheet with E30.

i put your code into a module and when changing E30 (1,2 or 3) it just changes the option on the dropdown box but doesnt run any macro

E30 is hidden so its not something that is needed to be changed, the dropdown options effect the value in E30 then run the code depending on 1,2, or 3

appreciate the help

 

[JumboCactuar]

figured this out, i simply had to uncheck locked on the input range cells