Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro codes automatically opens when opening a excel file
I have coded a macro for MS excel 2007. The macro has been placed under
Workbook_open event. It works fine as expected but it also opens the Visual Basic editor auomatically displaying the macro codealong with excel sheet. I tested the same by saving in Excel 2003 format. The same problem exists. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro codes automatically opens when opening a excel file
Is the VBE opening because of an error?
Post your code, it is impossible to diagnose the problem without it. -- Regards, Nigel "Vaibhav Bhawsar" <Vaibhav wrote in message ... I have coded a macro for MS excel 2007. The macro has been placed under Workbook_open event. It works fine as expected but it also opens the Visual Basic editor auomatically displaying the macro codealong with excel sheet. I tested the same by saving in Excel 2003 format. The same problem exists. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro codes automatically opens when opening a excel file
Hi Nigel,
Thank you for responding. Following is teh code which iterates cells of 3rd column starting from 4 to 49. Column 3 contains end dates. When end date is nearing current date by 2 days or so, it highlights those cells with color. Code works fine and no errors or warnings. But along with excel sheet it opens the code also (VBE). Regards, Vaibhav Sub Alerts() ' ' Alerts Macro ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("A2").Select Application.Goto Reference:="Alerts" Dim msg, cn, td As Integer Dim dt, k, final_msg As Variant cn = 4 final_msg = "Following Items are critical :" Do While cn < 49 k = Worksheets("Sheet1").Cells(cn, 3).Value If k < Empty Then td = DateDiff("d", CDate(k), Now) If td <= 2 And td 0 Then msg = msg + Worksheets("Sheet1").Cells(cn, 2).Value Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 15 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 15 End If If td = 0 Then Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 33 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 33 End If End If cn = cn + 1 Loop final_msg = final_msg + msg Rem msg = DateDiff("d", "13-JAN-2008", Now) Rem MsgBox final_msg Application.Visible = True End Sub "Nigel" wrote: Is the VBE opening because of an error? Post your code, it is impossible to diagnose the problem without it. -- Regards, Nigel "Vaibhav Bhawsar" <Vaibhav wrote in message ... I have coded a macro for MS excel 2007. The macro has been placed under Workbook_open event. It works fine as expected but it also opens the Visual Basic editor auomatically displaying the macro codealong with excel sheet. I tested the same by saving in Excel 2003 format. The same problem exists. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro codes automatically opens when opening a excel file
On Jan 17, 3:46 am, Vaibhav Bhawsar
wrote: Hi Nigel, Thank you for responding. Following is teh code which iterates cells of 3rd column starting from 4 to 49. Column 3 contains end dates. When end date is nearing current date by 2 days or so, it highlights those cells with color. Code works fine and no errors or warnings. But along with excel sheet it opens the code also (VBE). Regards, Vaibhav Sub Alerts() ' ' Alerts Macro ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("A2").Select Application.Goto Reference:="Alerts" Dim msg, cn, td As Integer Dim dt, k, final_msg As Variant cn = 4 final_msg = "Following Items are critical :" Do While cn < 49 k = Worksheets("Sheet1").Cells(cn, 3).Value If k < Empty Then td = DateDiff("d", CDate(k), Now) If td <= 2 And td 0 Then msg = msg + Worksheets("Sheet1").Cells(cn, 2).Value Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 15 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 15 End If If td = 0 Then Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 33 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 33 End If End If cn = cn + 1 Loop final_msg = final_msg + msg Rem msg = DateDiff("d", "13-JAN-2008", Now) Rem MsgBox final_msg Application.Visible = True End Sub "Nigel" wrote: Is the VBE opening because of an error? Post your code, it is impossible to diagnose the problem without it. -- Regards, Nigel "Vaibhav Bhawsar" <Vaibhav wrote in ... I have coded a macro for MS excel 2007. The macro has been placed under Workbook_open event. It works fine as expected but it also opens the Visual Basic editor auomatically displaying the macro codealong with excel sheet. I tested the same by saving in Excel 2003 format. The same problem exists. It's because the Sub Alerts() is the same name as the Range "Alerts" in the Application.Goto Reference:="Alerts" line Change one and the effect will cease. SteveM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro codes automatically opens when opening a excel file
Hi Steve,
My problem resolved. many thnaks to u Regards, Vaibhav "SteveM" wrote: On Jan 17, 3:46 am, Vaibhav Bhawsar wrote: Hi Nigel, Thank you for responding. Following is teh code which iterates cells of 3rd column starting from 4 to 49. Column 3 contains end dates. When end date is nearing current date by 2 days or so, it highlights those cells with color. Code works fine and no errors or warnings. But along with excel sheet it opens the code also (VBE). Regards, Vaibhav Sub Alerts() ' ' Alerts Macro ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("A2").Select Application.Goto Reference:="Alerts" Dim msg, cn, td As Integer Dim dt, k, final_msg As Variant cn = 4 final_msg = "Following Items are critical :" Do While cn < 49 k = Worksheets("Sheet1").Cells(cn, 3).Value If k < Empty Then td = DateDiff("d", CDate(k), Now) If td <= 2 And td 0 Then msg = msg + Worksheets("Sheet1").Cells(cn, 2).Value Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 15 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 15 End If If td = 0 Then Worksheets("Sheet1").Cells(cn, 2).Interior.ColorIndex = 33 Worksheets("Sheet1").Cells(cn, 3).Interior.ColorIndex = 33 End If End If cn = cn + 1 Loop final_msg = final_msg + msg Rem msg = DateDiff("d", "13-JAN-2008", Now) Rem MsgBox final_msg Application.Visible = True End Sub "Nigel" wrote: Is the VBE opening because of an error? Post your code, it is impossible to diagnose the problem without it. -- Regards, Nigel "Vaibhav Bhawsar" <Vaibhav wrote in ... I have coded a macro for MS excel 2007. The macro has been placed under Workbook_open event. It works fine as expected but it also opens the Visual Basic editor auomatically displaying the macro codealong with excel sheet. I tested the same by saving in Excel 2003 format. The same problem exists. It's because the Sub Alerts() is the same name as the Range "Alerts" in the Application.Goto Reference:="Alerts" line Change one and the effect will cease. SteveM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing a macro that automatically opens another file | Excel Discussion (Misc queries) | |||
Opening file in Excel 2003 opens multipule instances of same file | Excel Discussion (Misc queries) | |||
Excel opens a new Bk., which I don't want, before opening my file | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
Automatically run a macro when opening an Excel file | Excel Programming |