Vba in excel to locked entire colums

[vbacoder12]

Hi ,

I have been unsuccessfully writing a vba to locked the cells of multiple columns based on a date. Any suggestions or code that can be provided would be great

 

[Joe4]

Welcome to the Board!

Add code to the beginning of your procedure that first unlocks the cells, then makes the updates you require, then re-locks them again at the end.
You should be able to get the VBA code for the unlocking and locking simply by turning on your Macro Recorder and recording yourself performing those steps manually.

 

[vbacoder12]

Welcome to the Board!

Add code to the beginning of your procedure that first unlocks the cells, then makes the updates you require, then re-locks them again at the end.
You should be able to get the VBA code for the unlocking and locking simply by turning on your Macro Recorder and recording yourself performing those steps manually.

This is what I'm doing and it's locking the entire workbook.
Private Sub workbook_open()
ActiveSheet.Unprotect
PASSWORD =""
If Date>#date# then
RANGE("A:G").LOCKED=TRUE
ACTIVESHEET.PROTECT
PASSWORD=""
THISWORKBOOK.SAVE
END IF
END SUB

 

[vbacoder12]

This is what I'm doing and it's locking the entire workbook.
Private Sub workbook_open()
ActiveSheet.Unprotect
PASSWORD =""
If Date>#date# then
RANGE("A:G").LOCKED=TRUE
ACTIVESHEET.PROTECT
PASSWORD=""
THISWORKBOOK.SAVE
END IF
END SUB

I just need columns a-g on one sheet

 

[Joe4]

That is not valid VBA code.
See this for how to structure the lines of code to unprotect/protect your sheets: VBA Protect / Unprotect Worksheets

A few questions:

1. How many sheets are in your workbook?

2. Do you really have #date# in your VBA code?
If not, what is in this place? Where is that date coming from?

 

[vbacoder12]

I only one sheet that needs protected.
I changed around some of the info i. The vba and it seems to be working.

1. I am using range().select then selection.locked=TRUE or false depending on what column it is.
2. The date . I just entered date().
3. Should i be calling out active sheet1 in the formula?

I also have a question about how to have queries run off of vba on a certain day and time. Should i create a new question off that?

That is not valid VBA code.
See this for how to structure the lines of code to unprotect/protect your sheets: VBA Protect / Unprotect Worksheets

A few questions:

1. How many sheets are in your workbook?

2. Do you really have #date# in your VBA code?
If not, what is in this place? Where is that date coming from?

 

[vbacoder12]

I only one sheet that needs protected.
I changed around some of the info i. The vba and it seems to be working.

1. I am using range().select then selection.locked=TRUE or false depending on what column it is.
2. The date . I just entered date().
3. Should i be calling out active sheet1 in the formula?

I also have a question about how to have queries run off of vba on a certain day and time. Should i create a new question off that?

I was putting date >#9/3/2024

 

[Joe4]

It sounds like you still may have some inefficiencies in your code (it is seldom necessary to select the ranges in VBA code, you can usually work with them directly).
For example, whenever you have one line ending in "Select" and the next beginning with "Selection", they can usually be combined into one, i.e.

Range(...).Select
Selection.Locked=TRUE

can be simplified to just:

Range(...).Locked=TRUE

Not only does it make your code shorter, but selecting ranges can actually cause screen flickering while the code is running, and make your code run slower.

If you get your VBA code by using the Macro Recorder, it is very literal, and records every range selection. So often times, while the recorded code will work fine, it can often be cleaned up a little to make it run more efficiently.

I am glad you seem to have gotten everything working the way you want now. If you want us to help you "clean up" the code a little, you can post what you currently have here, and we can help you with that.

 

[vbacoder12]

It sounds like you still may have some inefficiencies in your code (it is seldom necessary to select the ranges in VBA code, you can usually work with them directly).
For example, whenever you have one line ending in "Select" and the next beginning with "Selection", they can usually be combined into one, i.e.

Range(...).Select
Selection.Locked=TRUE

can be simplified to just:

Range(...).Locked=TRUE

Not only does it make your code shorter, but selecting ranges can actually cause screen flickering while the code is running, and make your code run slower.

If you get your VBA code by using the Macro Recorder, it is very literal, and records every range selection. So often times, while the recorded code will work fine, it can often be cleaned up a little to make it run more efficiently.

I am glad you seem to have gotten everything working the way you want now. If you want us to help you "clean up" the code a little, you can post what you currently have here, and we can help you with that.

Private Sub workbook_open()
ActiveSheet.Unprotect
Password=""
If date>#9/6/2024 then
Range("a:d").select
Selection.locked=false
Range("e:g").select
Selection.locked=TRUE
Activesheet.protect
Password=""
Thisworkbook.save
End if
End sub

 

[Joe4]

That IF statement is not valid VBA code and will return an error.

I would also recommend moving the unprotect step under your IF statement.
Otherwise, if the IF statement is NOT met, you are unlocking the worksheet and leaving it unlocked, which I don't think is what you want.

I think this should do what you want in an efficient manner:

Private Sub Workbook_Open()

    If Date > DateSerial(2024, 9, 6) Then
        ActiveSheet.Unprotect Password = ""
        Range("a:d").Locked = False
        Range("e:g").Locked = True
        ActiveSheet.Protect Password = ""
        ThisWorkbook.Save
    End If

End Sub