Я хочу, чтобы при открытии рабочей книги открывался лист, пронумерованный после текущего месяца. Может кто-нибудь, пожалуйста, помогите? Кроме того, так как я сделал лист отпуска и сделал определенную запись, последний лист в рабочей тетради откроется сам. Любая идея, что может быть причиной? мой код:
Dim l, m, leaves
Private Sub Workbook_Open()
ThisWorkbook.Sheets("Control").Visible = xlSheetVisible
ThisWorkbook.Sheets("Control").Range("A1").Value = ""
ThisWorkbook.Sheets("Control").Visible = xlSheetVeryHidden
Sheets(Month(Now)).Activate
Application.StatusBar = "Designed by:- Shefali Oberoi"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim tdy, later, a, val, pre, col, rw, mmcc, aname, m
m = 0
Do While (m = 0)
If Sh.Name = "Control" Then
Sh.Visible = xlSheetVeryHidden
ThisWorkbook.Sheets(Month(Now)).Activate
End
Else
End If
col = Target.Column
rw = Target.Row
val = Target.Value
If val = "" Or val = "//" Then
Exit Sub
Else
aname = ActiveSheet.Name
later = Format(Now + 15, "dd/mm/yyyy")
tdy = Format(ActiveSheet.Cells(7, col).Value & "/" & ActiveSheet.Range("B2").Value & "/" & ActiveSheet.Range("B1").Value, "dd/mm/yyyy")
pre = Format(Now, "dd/mm/yyyy")
a = ActiveSheet.Cells(6, col)
fifth = Format(Now + 5, "dd/mm/yyyy")
Annual = Format(ActiveSheet.Cells(7, col).Value & "/" & ActiveSheet.Range("B2").Value & "/" & ActiveSheet.Range("B1").Value, "dd/mm/yyyy")
third = Format(ActiveSheet.Cells(7, col).Value & "/" & ActiveSheet.Range("B2").Value & "/" & ActiveSheet.Range("B1").Value, "dd/mm/yyyy")
If tdy = "//" Then
End
End If
If DateValue(tdy) >= DateValue(later) Then
MsgBox "OOPS! Cannot apply beyond next 15 days!", vbCritical
ActiveSheet.Cells(rw, col).Value = ""
End
Else
If ActiveSheet.Cells(rw, col).Value = "Annual Leave" And DateValue(tdy) < DateValue(fifth) Then
MsgBox "OOPS! Annual Leave can only be applied 5 days prior. Email has been sent to Saurabh", vbCritical
Set myolapp = CreateObject("Outlook.Application")
Set myitem = myolapp.CreateItem(OlMailItem)
myitem.to = "xyz@abc.com"
myitem.Subject = "Annual Leave Request"
myitem.htmlbody = "<html><font face='arial' size=2>Hi Saurabh,<br><br>"
myitem.htmlbody = myitem.htmlbody & "I am trying to apply for an annual leave for " & Annual
myitem.htmlbody = myitem.htmlbody & " which is within next five days."
myitem.htmlbody = myitem.htmlbody & "</font></html>"
myitem.send
ThisWorkbook.Save
ActiveSheet.Cells(rw, col).Value = ""
End
Else
If DateValue(tdy) <= DateValue(pre) Then
MsgBox "OOPS! Date has already passed. Cannot process this request.", vbCritical
ActiveSheet.Cells(rw, col).Value = ""
End
Else
If a > 2 Then
MsgBox "OOPS! Two employees are already on leave." & vbCrLf & "This request cannot be processed.", vbCritical
Set myolapp = CreateObject("Outlook.Application")
Set myitem = myolapp.CreateItem(OlMailItem)
myitem.to = "xyz@abc.com"
myitem.Subject = "Leave Request"
myitem.htmlbody = "<html><font face='arial' size=2>Hi Saurabh,<br><br>"
myitem.htmlbody = myitem.htmlbody & "I am trying to apply leave for " & third
myitem.htmlbody = myitem.htmlbody & " when two other employees would be absent as well."
myitem.htmlbody = myitem.htmlbody & "</font></html>"
myitem.send
ThisWorkbook.Save
ActiveSheet.Cells(rw, col).Value = ""
End
Else
End If
If leaves = "" Then
leaves = Format(ActiveSheet.Cells(7, Target.Column).Value & "/" & ActiveSheet.Range("b2").Value & "/" & ActiveSheet.Range("b1").Value, "dd/mm/yyyy")
Else
leaves = leaves & ", " & Format(ActiveSheet.Cells(7, Target.Column).Value & "/" & ActiveSheet.Range("b2").Value & "/" & ActiveSheet.Range("b1").Value, "dd/mm/yyyy")
End If
ThisWorkbook.Sheets("Control").Visible = xlSheetVisible
If ThisWorkbook.Sheets("Control").Range("A1").Value = "" Then
ThisWorkbook.Sheets("Control").Range("A1").Value = leaves
Else
ThisWorkbook.Sheets("Control").Range("A1").Value = ThisWorkbook.Sheets("Control").Range("A1").Value & "," & leaves
End
End If
End If
End If
End If
End If
Loop
m = 1
End Sub