Need macro to protect columns for the current month and the prior months

[carlag12]

Hello - I am new to writing macros. I know how to write a macro to protect ranges but I need one where I can protect certain columns based on the current month. For example i have an excel file with a column for each month of the year. For Jan I need to protect just the one column for Jan but when I get to Feb I need to protect Jan and Feb. I think it can be done if the macro uses the eomonth function and looks at a certain cell to know the date is 2/28/2019 but then how do I tell it to hide the column for Jan and Feb based on the date? Thanks!!!

 

[Rijnsent]

Hi Carlag,
okay, I'm assuming you have a worksheet with columns representing your months, identifiable by a header row with a date, the last day of the month?

What you than want in pseudo code:
unprotect sheet
loop through the months/columns
if the month of the date in that header row is before the month of today (or a cell with a date setting), hide that column
if the month of the date in that header row is the month of today (or a cell with a date setting), show the column and unprotect it
if the month of the date in that header row is after the month of today (or a cell with a date setting), show the column but protect it
protect the sheet

Is that what you more or less want? If so, do start by recording a macro going manually through some steps (like unprotecting, hiding a column). If you can post that code here (see my signature on how to), someone here can help you adding a loop to it. And two easy free VBA courses: http://www.homeandlearn.org/ & https://www.excel-easy.com/vba.html

Cheers,

Koen

 

[carlag12]

Hi Carlag,
okay, I'm assuming you have a worksheet with columns representing your months, identifiable by a header row with a date, the last day of the month?

What you than want in pseudo code:
unprotect sheet
loop through the months/columns
if the month of the date in that header row is before the month of today (or a cell with a date setting), hide that column
if the month of the date in that header row is the month of today (or a cell with a date setting), show the column and unprotect it
if the month of the date in that header row is after the month of today (or a cell with a date setting), show the column but protect it
protect the sheet

Is that what you more or less want? If so, do start by recording a macro going manually through some steps (like unprotecting, hiding a column). If you can post that code here (see my signature on how to), someone here can help you adding a loop to it. And two easy free VBA courses: http://www.homeandlearn.org/ & https://www.excel-easy.com/vba.html

Cheers,

Koen

Hi Koen thank

Here is my code - I am trying to protect any columns in range D2:J2 where the month which is a numerical value is <= to a value in a cell B2

Sub test()




Dim x As Range


Dim strPassword As String


For Each x In Range("D2:J2").Cells


If x.Value <= Range("B2") Then


x.EntireColumn.Locked = True


ActiveSheet.Protect Password:="hello"


End If


Next x


End Sub

 

[Rijnsent]

Hi carlag,
that is close, try this:

Sub test()

Dim x As Range
Dim strPassword As String

ActiveSheet.Unprotect Password:="hello"

For Each x In Range("D2:J2").Cells
    If x.Value <= Range("B2") Then
        x.EntireColumn.Locked = True
    End If
Next x

ActiveSheet.Protect Password:="hello"


End Sub

Cheers,
Koen

 

[carlag12]

Thank you so much this worked!