Macro codes automatically opens when opening a excel file

[Vaibhav Bhawsar]

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.

 

[Nigel]

Is the VBE opening because of an error?

Post your code, it is impossible to diagnose the problem without it. --

Regards,
Nigel
(e-mail address removed)

 

[Vaibhav Bhawsar]

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

 

[SteveM]

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

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

 

[Vaibhav Bhawsar]

Hi Steve,

My problem resolved.

many thnaks to u

Regards,

Vaibhav