Abrindo uma planilha específica

1

Eu quero que quando a pasta de trabalho for aberta, a folha numerada após o mês atual seja aberta. Alguém pode por favor ajudar? Além disso, como fiz uma folha de licença e fiz uma certa entrada, a última folha na pasta de trabalho se abre. Alguma idéia do que poderia ser o motivo? meu código é:

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 = "[email protected]"
                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 = "[email protected]"
                        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
    
por Shefali 18.07.2012 / 10:48

1 resposta

0

Qual é a última folha? É a folha de controle?

Se alguém adicionar outra planilha à pasta de trabalho, tudo isso falhará. Você deve se certificar de que a pasta de trabalho está protegida.

O código principal está tão confuso que eu não tenho tempo para desvendar isso.

No entanto, a premissa básica de usar Sheets(Month(Now)).Activate em workbook_open certamente funciona bem - embora não seja muito robusta.

Portanto, se outra planilha estiver sendo deixada ativa, é provável que esteja acontecendo no código confuso na função Workbook_SheetChange .

    
por 18.07.2012 / 16:31