Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I want that when the workbook opens, the sheet numbered after current month opens up. Can anyone please help? Also, as i have made a leave sheet and make a certain entry, the last sheet in the workbook opens up itself. any idea what could be the reason? my code is: 

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
share|improve this question
Is there an error or does it just not work? – Julian Knight Jul 18 '12 at 14:12
You are mixing ActiveSheet with Sh in the worksheet change function. So it is not clear what is happening. You should either add comments or change ActiveSheet (preferred) so that the meaning is clear. With what you have a the moment it might be possible for somthing else to trigger the Workbook_SheetChange for a sheet that isn't the active worksheet. – Julian Knight Jul 18 '12 at 14:19
Also, your example code is messed up. There are a number of typo's in it. Try copying and pasting to Excel VBE. I recommend that you go to your original code, select all, press tab to indent everything, then paste it into the edit box - that should retain all of the original indentation. – Julian Knight Jul 18 '12 at 14:23

1 Answer

up vote 0 down vote

What is the last sheet? Is it the Control sheet?

If someone adds another sheet to the workbook, this will all fail. You should make sure the workbook is protected.

The main code is so messed up I'm afraid I haven't the time to untangle it.

However, the basic premise of using Sheets(Month(Now)).Activate in workbook_open certainly works fine - though it is not very robust.

So if another sheet is being left active, it is likely to be happening in the messed up code in the Workbook_SheetChange function.

share|improve this answer
Hi Julian. the last sheet is control sheet but it is hidden. hence the last sheet is for december numbered as 12. my workbook is protected. i tried to wriite the code here as simply and cleary as possible but i will try to write it again. the only problem i am facing is that last sheet i.e. numbered "12" pops open when i open the workbook and also, when i make any entry in the first sheet. – Shefali Jul 19 '12 at 5:38
my problem is solved. but i just have one more problem now. it shows run time error 9 : subscript out of range. i have changed : ThisWorkbook.Sheets(month(now)).activate with b = MonthName(Month(Date)) ThisWorkbook.Sheets(b).Activate – Shefali Jul 19 '12 at 8:17
I suspect a typo in naming the sheets since it works fine for me. Also, with that code, watch out for users with a different default language! If that might be an issue, you'd be better off with your own list of (short) month names as an array. Also, can you let us know what the issue was and how you resolved it - it might be useful to people in the future which is what Stack is all about. – Julian Knight Jul 19 '12 at 9:09
check spelling of sheets - Monthname(1) returns January, but Monthname(1,True) returns Jan. If you need a quick list of names to make sure you are spelling them correctly, For i=1 to 12:?monthname(i):next in the immediate window will give you a list to copy and paste – Sean Cheshire Jul 20 '12 at 15:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.