The macro is about locking columns containing all dates except column with today's date. I have coded it in each sheet and is exactly similar in all the sheets. Macro runs when the data in any cell is changed. But I want macro to run when the workbook is opened. I tried to code it in 'this workbook' but I can not figure out how to do it. I also tried to do it in 'module' but could not.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'vps Dim x As Long x = 7 ThisWorkbook.ActiveSheet.Unprotect Password:="123456" ThisWorkbook.ActiveSheet.Cells.Locked = False Do Until IsEmpty(Cells(5, x)) If Cells(5, x) <> Date Then Columns(x).Locked = True End If x = x + 1 Loop ThisWorkbook.ActiveSheet.Protect Password:="123456" End Sub
I want the macro to run when the workbook is opened and not only when the data in cell is changed.
Macro to run code when the workbook is opened
-
anshulgarg -
January 24, 2019 at 10:48 PM -
Thread is marked as Resolved.
-
-
-
In ThisWorkbook, you will need to add your code to the On_Open event.
-
I tired to code it in ThisWorkbook but i am confused how to modify code - "Private Sub Worksheet_Change(ByVal Target As Excel.Range)"
-
please post your code using code tags as described in our forum rules. It is not easy to read and decipher in the manner you have presented.
-
I am really very sorry about that.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'vps
Dim x As Long
x = 7
ThisWorkbook.ActiveSheet.Unprotect Password:="123456"
hisWorkbook.ActiveSheet.Cells.Locked = False
Do Until IsEmpty(Cells(5, x))
If Cells(5, x) <> Date Then
Columns(x).Locked = True
End If
x = x + 1
Loop
ThisWorkbook.ActiveSheet.Protect Password:="123456"
End SubThere you go.
-
-
Still need to add code tags in accordance with Forum rules.
-
Code
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'vps Dim x As Long x = 7 ThisWorkbook.ActiveSheet.Unprotect Password:="123456" ThisWorkbook.ActiveSheet.Cells.Locked = False Do Until IsEmpty(Cells(5, x)) If Cells(5, x) <> Date Then Columns(x).Locked = True End If x = x + 1 Loop ThisWorkbook.ActiveSheet.Protect Password:="123456" End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!