I have so far made this macro and it works when i manually change a value in cell A43. The thing i want to know is if how i can modificate this code
in a way that this code runs even if A43 changes value due to a fomula and not manually.
Private Sub Worksheet_change(ByVal Target As Range)
If [A43] = 1 Then
ActiveSheet.Unprotect
[B30:C30].Locked = False
ActiveSheet.Protect
End If
If [A43] = 0 Then
ActiveSheet.Unprotect
[B30:C30].Locked = True
ActiveSheet.Protect
End If
End Sub
Have the macro trigger off of the changes in the cell(s) that the formula in A43 uses, but still use the result value in A43 to decide what to do.
So if A43 has a formula that uses cell A1 that the user changes, then do something like this.
Please Login or Register to view this content.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
The problem is i have the macro in sheet2 and the cell that manually changes A43 is in another sheet any further help will be much appreciated
Put this in the sheet that has the manually changed cell so the code is triggered when you change that cell.
It will Lock\Unlock cells on "Sheet2".
Please Login or Register to view this content.
Thnx for feedback . Now i have the problem that it indeed unlocks the cell but not locking them again
Whats the formula in A43? If you have Quotes around the "0" in the formula, remove them because 0 <> "0"
=IF(OR(MOD(YEAR('Συνολικές Αποδοχές,Δώρα,Αδειες'!B13);400)=0;AND(MOD(YEAR('Συνολικές Αποδοχές,Δώρα,Αδειες'!B13);4)=0;MOD(YEAR('Συνολικές Αποδοχές,Δώρα,Αδειες'!B13);100)<>0));1;0)
is the formula in A43... Sorry im kind of newbie in VBA things...but i wanted to ask you the first code i showed in the start of the thread should i keep it?It perfectly works for manually inputs. I've read sth about calculate instead of change at private sub section is it possible to work like this?
Remove your original Worksheet_Calculate code.Originally Posted by tyeinse
Are you trying to determine if B13 is a leap year?
If you're using VBA to hide cells, the code can also calculate if it's a leap year.
I may have the cell locking backwards. If I do, change the equal sign to not equal <>.Please Login or Register to view this content.
it still doesnt work but you understood that the code is for leap year that's incredible....in another language... the B13:C13 cells range work entry B13 and work finish hours to be more precise (they are in sheet 2 and they are inputed manually).The B13 cell in sheet 13 is also inputed manually and indicates the working year its format is yy/MM/DD.
I didn't understand this.
Did you remove the old code?
Does this code still unlock the cells but not lock them?
Is the sheet name "Sheet2" correct in the code?
Yes i removed the old code. The code now do not lock or unlock nothing. sheet2 is the sheet i wanna lock the cells B30:C30 and sheet13 has the year entry B13. Can i hold their default names in code? because in excel sheets sheet2 and sheet13 have months names in greek.
This uses the worksheet code name Sheet2.
I thought B13 was a complete date. Apparently it is just the Year.
Please Login or Register to view this content.
My friend i want to thank you for helping me but i found a way to solve it. Here are my efforts.
Private Sub Worksheet_Calculate()
myMacro
End Sub
Public Sub myMacro()
If Range("A42").Value = 1 Then
Sheet2.Unprotect
Sheet2.Range("B30:C30").Locked = False
Sheet2.Protect
End If
If Range("A42").Value = 0 Then
Sheet2.Unprotect
Sheet2.Range("B30:C30").Locked = True
Sheet2.Protect
End If
I call this macro from a simple If cell and it is working perfectly thnx for helping though and btw its a very very interesting forum !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks