How to enable the VBA Coding for Locked Cells

[azad092]

Hi
I want to use the given VBA code to convert the formulas into values
but I want that if a sheet or cell is locked the code should be applied without unlocking the sheet or cell
and I also want that this code should work when the date value entered in sheet2.range ("B2") is < then the current date
for example if in B2 date is 12/05/2020 and when 13/5/2020 comes the macro code should automatically run

Sub FormulaToValue()
Dim N As Long, rng As Range

N = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(N, Columns.Count))

rng.Value2 = rng.Value2
End Sub

 

[Joe4]

but I want that if a sheet or cell is locked the code should be applied without unlocking the sheet or cell

If the cell is locked, you MUST unlock it before doing the conversion.
However, that is not hard. You can unlock and re-lock the sheet right in the VBA code. So it will still remained locked for the users.
It is only unlocked for brief moment in time while the VBA code is running.
Here is a link to code on how to do that: VBA to protect and unprotect Sheets - Excel Off The Grid

I also want that this code should work when the date value entered in sheet2.range ("B2") is < then the current date
for example if in B2 date is 12/05/2020 and when 13/5/2020 comes the macro code should automatically run

Are you saying that you want the code to run automatically?
If so, is someone opening the file every day (the file needs to be opened for the code to run).
You can add your code to the Workbook_Open event, while is VBA code that runs automatically when the file is first opened (as long as VBA/Macros are enabled).

 

[azad092]

If the cell is locked, you MUST unlock it before doing the conversion.
However, that is not hard. You can unlock and re-lock the sheet right in the VBA code. So it will still remained locked for the users.
It is only unlocked for brief moment in time while the VBA code is running.
Here is a link to code on how to do that: VBA to protect and unprotect Sheets - Excel Off The Grid


Are you saying that you want the code to run automatically?
If so, is someone opening the file every day (the file needs to be opened for the code to run).
You can add your code to the Workbook_Open event, while is VBA code that runs automatically when the file is first opened (as long as VBA/Macros are enabled).

hi
thanks for attention
I try and will inform you about the result

 

[azad092]

hi
thanks for attention
I try and will inform you about the result

hi
thanks for attention
I said that I want to use the date to run the macro for converting the formulas into values
for example in sheet1 B2 we enter date manually and in cell BX2 we set the current date using the current date formula means that when the file will be opened the BX2 cell will show the current date
we just compare the date in B2 and in BX2
so if BX2-B2=0 then do nothing
and if BX2-b2=2 then we will call the macros of lock,convert into values and unlock
so please just guide how we can use the date-date formula
I try and will inform you about the result

 

[Joe4]

we just compare the date in B2 and in BX2
so if BX2-B2=0 then do nothing
and if BX2-b2=2 then we will call the macros of lock,convert into values and unlock
so please just guide how we can use the date-date formula

You can just structure it like:

If Range("BX2")>Range("B2") Then
'   enter your code here
End If