Clear Worksheet Protection with VBA - but it doesn't work

[Simon Schäfer]

Hello altogether,

I hope you can help me with my problem:
I have created an Excel sheet, where most of the cells are protectet. The
User has the opportunity to fill the cells by using different comboboxes.
I know that there are two options of getting the protection disabled to
write in the protectet cells.
First: Using VBA to Disable the Protection, Write into the Cell and Protect
again:

Private Sub cbo1_Change()
ActiveSheet.Unprotect ("password")
Range("d16").ClearContents
ActiveSheet.Calculate
Range("K10").Select
Selection.Copy
Range("D16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Protect ("password")
End Sub

The second option is, to use the method, that all elements like comboboxes,
commandbuttons e.g. has the allowance to change the cell value:

,UserInterfaceOnly:=true

But now my problem:
I have tried both options, and when I use the command button everything's
working fine. But the combobox doesn't work with these options. The
protection won't get disabled!!!

Has anyone an idea how to solve this? I tried it on different versions of
Excel and different computers....nothing takes a better effect.

Also I tried this one:

Private Sub cbo1_DropButtonClick()
ActiveSheet.Unprotect ("passwort")
Range("d16").ClearContents
End Sub

Private Sub cbo1_Change()
ActiveSheet.Calculate
Range("K10").Select
Selection.Copy
Range("D16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Protect ("passwort")
End Sub


So that the protection is disabled when the user clicks on the combobutton.
But when the user then clicks out of the field range without choosing an
entry he has an unprotected sheet. That shouldn't be like this!

Well now I ask you if there is a chance to solve this problem. Either tu use
a single macro or to use the two different ones and make sure, that the user
has to choose an entry once clicked on the combo field button.

Hope you can help. Many many thanks in advance and have a nice day!

Simon





Das soll er nicht haben. Wie kann man per Code zur Auswahl eines Wertes der
ComboBox "zwingen"???

Habt Dank für Eure Hilfe....