VBA If thismonth then lock sheet# criteria

[Pinaceous]

Hi All,

I'm working with a workbook where it opens up to a specific sheet# based upon the current month.

For example, if it is:

October it opens up to Sheet1
.
.
.
September it opens up to Sheet 12

in using this code:

Sub MonthSheetSelect()

Dim shtname As String
Dim thismonth As Long


thismonth = Month(Now())


If thismonth = 11 Or thismonth = 12 Then
thismonth = thismonth - 6
Else
thismonth = thismonth + 6
End If


Worksheets(thismonth).Select


End Sub

Now my question is, I'd like to create a new sub to lock the prior month's sheet from editing.

So, for example, now being September of my workbook's Sheet12, I'd like it to lock the prior month's sheet being August of Sheet11.

The new sub will have to exclude October (Sheet1) because it is my starting month.

Can you help me?

Thank you,
pinaceous

 

[Pinaceous]

Sorry about that! I could not find a link to the moderator. Next time I'll just leave it alone. Thank you!

 

[Pinaceous]

To lock ANY cells, you still have to protect the worksheet...that's the way Excel is I'm afraid !!

Could you show me how to substitute protect in your code to lock?

This would really help me.

Thank you,
pinaceous

 

[Peter_SSs]

I could not find a link to the moderator.

You don't need a link to a moderator, you just need that Report button.

 

[Michael M]

To lock cells you will need

Range("A1:A4").Cells.Locked=True 'change ranges to suit

But, as mentioned you will still need to protect the sheet to make cell locking work !!

 

[Pinaceous]

Thank you Michael!

 

[Pinaceous]

Hi Michael,

In my workbook, I'm running a code upon Range("B7:B66") upon my sheets that effects the date entry.

If I run your code upon my individual sheet in adding;

        Range("B7:M66").Select

        Selection.Locked = True

I'm getting a mixmatch error.

I'm not sure how to fix it except to maybe add to my workbook code with a case.

Could you continue to help me on this with in possibility combining your code with my workbook code, or do you suggest I create a new thread?

Thank you,
pinaceous

 

[Michael M]

As mentioned you will need to use

Range("B7:M66").Cells.Locked=True

you don't need to use .Select

 

[Pinaceous]

Humm… let me see thank you

 

[Pinaceous]

Hi pinaceous,

You could use something like the following, it will check if the worksheet matches the current month and if it doesn't then it will hide the sheet and lock it with a password for added security. Currently the date check is based on the sheet being named as the month number (e.g. September = 9 so the sheet would be 9).
This can be changed by adding Format to the Month(Now()).

e.g.
Format(Month(Now()), "MMMM") will show September instead of 9.
Format(Month(Now()), "MMMM-YYYY") would show September-2020 instead of 9

Private Sub Workbook_Open() 'this can be changed depending on how you wish
    Dim xWs As Worksheet
    Dim thismonth As Long
   
    thismonth = Month(Now())
   
        For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> thismonth Then 
        xWs.Protect Password:="PasswordHere" 'protects the sheet
        xWs.Visible = xlSheetHidden 'hides the sheet
        End If
    Next
End Sub

t0ny84

Hey,

If I want to exclude sheets here, such as DllBytes & TOTALS from this code, how would I do that?

 

[t0ny84]

Hey,

If I want to exclude sheets here, such as DllBytes & TOTALS from this code, how would I do that?

By updating the following line it should work;

If xWs.Name <> thismonth Then

To

if xWs.Name <> thismonth or "DllBytes" OR "TOTALS" Then


This should then only hide\protect all sheets except for DllBytes, Totals and ThisMonth.

t0ny84