Forum Discussion
Excel Workbook Help!
Alright, so I’ve tried to find Something for my exact situation on the internet, but I cannot. So I have an excel workbook that contains approximately 31 tabs (worksheets)—1for each day of the month. I want to either find a way to lock out each worksheet at 0001 the following day and also keep the following says locked until 0001. For example:
I would like the 1st of January to be open to edit until 0001 on the 2nd. I would like the 2nd of January to unlock itself at 0001 on the 2nd of January, and so on throughout the month. I am wanting to do this to make sure that data is not entered in the wrong worksheet.
If that won’t work, then I need something that will bring you to the sheet for the day you are accessing it. So if I am opening to workbook on 1 Jan, I want it to automatically bring them to the 1 Jan tab.
There was also an original way I wanted to do this that is a lot more complicated, but would be even better, if someone wants to know more I can tell them! Thank you in advance!
I would like the 1st of January to be open to edit until 0001 on the 2nd. I would like the 2nd of January to unlock itself at 0001 on the 2nd of January, and so on throughout the month. I am wanting to do this to make sure that data is not entered in the wrong worksheet.
If that won’t work, then I need something that will bring you to the sheet for the day you are accessing it. So if I am opening to workbook on 1 Jan, I want it to automatically bring them to the 1 Jan tab.
There was also an original way I wanted to do this that is a lot more complicated, but would be even better, if someone wants to know more I can tell them! Thank you in advance!
2 Replies
- Olá Alanna1280,
Creio que só é possível a partir de uma macro.
Coloquei a instancia "Open" o seguinte código:
Private Sub Workbook_Open()
data = Day(Date)
If data < 10 Then
data = "00" & data
Else
data = "0" & data
End If
BloqueiaDemaisPlanilhas (data)
AbrePlanilhaPorDia (data)
End Sub
E dentro de um módulo o código abaixo:
Sub AbrePlanilhaPorDia(data)
data = Day(Date)
On Error GoTo err
If data < 10 Then
data = "00" & data
Else
data = "0" & data
End If
Sheets(data).Select
ActiveSheet.Unprotect "1234"
Exit Sub
err:
Sheets("outra").Select
ActiveSheet.Unprotect "1234"
End Sub
Sub BloqueiaDemaisPlanilhas(data)
Dim NomePlan As String
Set i = Sheets
For Each i In Sheets
NomePlan = i.Name
If i.Name <> data Then
i.Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"
ActiveSheet.EnableSelection = xlNoSelection
End If
Next
End Sub
Segue em anexo o arquivo para você testar. espero ter ajudado!
Raul NunziatoFaltou o arquivo.
