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

 

[Michael M]

A little confusing, you say August is Sheet1....then you say October is Sheet1 !!
But you need something like

Sub MonthSheetSelect()
Dim shtname As String, thismonth As Long
thismonth = Month(Now())
    If thismonth = 11 Or thismonth = 12 Then
        thismonth = thismonth - 6
        Else
        thismonth = thismonth + 6
   End If
Worksheets(thismonth - 1).Protect 'protects previous month sheet
Worksheets(thismonth).Select
End Sub

 

[Pinaceous]

Hi Michael M,

I wrote "August of Sheet11", not sure where your getting Sheet1 from.

I also wrote "October (Sheet1)".

Sorry if this is confusing.

pinaceous

 

[Michael M]

Ok, my apologies......see what happens when you get old !!..
So how about

Sub MonthSheetSelect()
Dim shtname As String, thismonth As Long
thismonth = Month(Now())
    If thismonth = 11 Or thismonth = 12 Then
        thismonth = thismonth - 6
        Else
        thismonth = thismonth + 6
   End If
If thismonth -1 <> 1 then "change for first month
Worksheets(thismonth - 1).Protect 'protects previous month sheet
end if
Worksheets(thismonth).Select
End Sub

 

[Pinaceous]

Hi Michael!

Ok, thank you for the code.

I am getting an error in the code with respect to:

If thismonth -1 <> 1 then "change for first month

I'm hoping this is something that you can correct?

Please let me know.

Thank you,
pinaceous

 

[Michael M]

Just remove the text at the end

Sub MonthSheetSelect()
Dim shtname As String, thismonth As Long
thismonth = Month(Now())
    If thismonth = 11 Or thismonth = 12 Then
        thismonth = thismonth - 6
        Else
        thismonth = thismonth + 6
   End If
If thismonth -1 <> 1 then 
Worksheets(thismonth - 1).Protect 'protects previous month sheet
end if
Worksheets(thismonth).Select
End Sub

 

[t0ny84]

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

 

[Pinaceous]

Hey Michael,

The code does not kick back any errors but I'm concerned with "protect" in the code.

But I'm concerned with how the sheet behaves with the "protect" in the code.

Is there a way to substitute "lock" in post #6 in lieu of protecting the sheet?

I apologize that I'm asking for something different midstream, please let me know if you suggest posting another thread.

Thank you,
pinaceous

 

[Michael M]

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

 

[Peter_SSs]

Yes it is a duplicate thread because I had a naming issue.

I tried to change the title.

Can you combine them for me?

Never-the-less you should not just start a new thread with the same question otherwise we can end up with two groups of people trying to solve the same question. This often ends up with people in one of the groups wasting their time with things that have already been resolved in the other thread. If you think a thread needs a different title or needs removing, use the 'Report' button that appears at the bottom left of each post and put your request/reasoning there. In this case I will combine the threads, change the title & remove some irrelevant posts.