☰

打开特定工作表

tag-icon • tag-icon windows-xp microsoft-excel vba
打开特定工作表

我希望当工作簿打开时，当前月份之后的编号的工作表会打开。有人能帮忙吗？另外，当我制作了休假表并进行了特定输入时，工作簿中的最后一张工作表会自动打开。知道可能是什么原因吗？我的代码是：

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

答案1

最后一张表是什么？它是控制表吗？

如果有人向工作簿中添加另一张工作表，这一切都将失败。您应该确保工作簿受到保护。

主要代码太乱了，恐怕我没有时间去理清它。

然而，在 workbook_open 中使用的基本前提Sheets(Month(Now)).Activate肯定可以正常工作 - 尽管它不是很强大。

因此，如果另一张工作表保持活动状态，则很可能发生在Workbook_SheetChange函数中的混乱代码中。

相关内容