If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Clear Worksheet Protection with VBA - but it doesn't work
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.... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Worksheet protection with exceptions | KG | General Discussion | 4 | February 21st, 2010 03:38 AM |
Need function to insert worksheet name into a cell of that worksheet. | August Meduna | General Discussion | 2 | October 9th, 2004 12:57 AM |
Worksheet Protection | Steve Arndt | General Discussion | 1 | July 24th, 2004 09:44 AM |
Sheet Names | Joseph M. Yonek | Worksheet Functions | 6 | January 3rd, 2004 02:15 AM |
excel worksheet password protection | Tommy | Setting up and Configuration | 3 | December 4th, 2003 05:24 PM |