help with combobox and vba with password

[ticc888]

hi

i've got a combbox with two options for dropdown and a macro that executes dependeing ont he two options..

the vba for the combbox on the worksheet is:

Private Sub combo_pl1_Change()
ActiveSheet.Unprotect ("pass")
Select Case combo_pl1.Value

Case "Plus or Minus 0%"
Call non2020pl_rev0percent

Case "Plus or Minus 1000%"
Call non2020pl_rev1000percent

Case Else
MsgBox "Something Else"
End Select
ActiveSheet.Protect ("pass")

End Sub


-----------------

While the macro for the first macro referred to above in the combobbox is below:

---------------

' Revenue

Sub non2020pl_rev0percent()
ActiveSheet.Unprotect ("escondida")
Range("EF6").Select
ActiveCell.FormulaR1C1 = "0"

Range("EK6").Select
ActiveCell.FormulaR1C1 = "0"
ActiveSheet.Protect ("escondida")
End Sub

-----------------------



I have two issues:

Everytime i use the dropdown box, it works, but than after i go and save a different version of the file (different file name), i get error message:

Unprotect method in worksheet failed.... (this error occurs when i use these dropdown boxes before i save), when i do anythign else to my spreadsheet, and save a differnt name file, it doesn't throw this error.

...

it will still save, after you ok it,

but when you go back to use it, with the different name, the error that there is a RC notation ...

so 2nd question is: why does it change my formulae (the 2nd vba code above) into RC notation?

 

[xenou]

Hi,

1)
Why do you have two passwords for the same sheet? ("pass" and "escondida") Does your code really have these passwords?

2)
No need for .FormulaR1C1
Just:

Range("EF6").Select
ActiveCell.Value = "0"

3) No need to select at all actually:
Just:

Range("EF6").Value = "0"

4) unprotect has some funny behavior. If it was not protected when you first run the code, then protected by the code, then tried to be unprotected with the wrong password, it would fail. The stuff about R1C1 doesn't make sense but don't use .FormulaR1C1 unless you really have an R1C1 style formula.

Hope this helps.

 

[ticc888]

hi

thanks for the pointers..

yes the password are all the same, i got confused, while constructing it, while working away...

but the other pointers, i didn't know about, i'll go ahead and see if it makes a difference, apreciate very much the syntax

cheers

ticc888

 

[GTO]

...yes the password are all the same...

Just to tack onto Alexander's observations, if you are using the same password for the worksheets that are protected, you could use a public Constant at the top of a standard module, like:

Option Explicit
Public Const PWD As String = "escondida"

This way you don't have to type the password string in throughout your code.

Mark

 

[xenou]

Looking at this again, also:

Range("EF6").Value = [COLOR="Blue"]"0"[/COLOR]

You might really prefer:

Range("EF6").Value = [COLOR="blue"]0[/COLOR]

Assuming you want the number 0 in these cells...

 

[ticc888]

Thanks Alex and GTO..

Alex, its definitely worked, also part of the problem i've noticed was it was contributing to becoming very buggy and throwing errors when i used the dropdown boxes with macroes written in that way (opening and closing evertime a macro is run)..

anyway, its very stable now (which is a relief), and i can play around with it and save it in a differen tname, no problems doesn't throw any errors.

I noticed that the method I used didn't go down well for combobox.. that i was using.

Its obbvious that that code i was using with the open and close of the password was causing significant issues, but yes I've heard it does create unusuaul behaviour when you have so many macros done in that way.

I'll also run with GTO's idea and see how that goes...

thanks a million guys