Need another eye on this Sub()

[Ayo]

when I run this sub, it seem like everything is fine except, nothing whitin
the sub is executed.
What am I missing?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim filename As String, ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Range("B4").Select

If Target.Address() = "$B$4" And Target.Address() <> "" Then
Application.EnableEvents = False
Range("B5").Select
Application.EnableEvents = True

ElseIf Target.Address() = "$B$5" And Target.Address() <> "" Then
Application.EnableEvents = False
Range("B1") = Range("B4") & ": " & Range("B5") & " " & Range("D4")
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
ActiveWorkbook.SaveCopyAs Range("B4") & "_" & Range("B5") & "_" &
filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
Application.Calculation = xlCalculationAutomatic
For Each ws In Workbook
If ws.Name <> "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
Call formulas
Range("A3").Select
Worksheets("Graphes Table").Visible = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Per Jessen]

Hi

I think the statement 'Range("B4").Select' is the problem!

No matter which cell has been changed, the macro select B4, and then the If
statement will always be true, so elseif will never be tested...

Hopes this helps.
....
Per

 

[Rick Rothstein]

What do you mean by "when I run this sub"? The code you posted is an event
procedure, not a macro... you don't run it, the Excel's VB system runs it
for you when you make a change to the worksheet. I'll assume you know that,
but I am mentioning it just so I can point out that your code must be in an
individual worksheet module and not a general module as is done with macros.

It would be kind of hard to trace your code completely since you make a call
to an external sub ("formulas") that you did post with your code; however,
the following may be the source of your error. You have this line in your
ElseIf block of code...

For Each ws In Workbook

There is no such object as Workbook... I think you meant ThisWorkbook

 

[Ryan H]

I shortened this code by deleting some things that I thought were
unneccessary but I could be wrong. The bulk of your code depends on the
Target Range being B4 or B5 otherwise nothing interesting happens? Maybe
that is your issue? Try this code below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strFileName As String
Dim ws As Worksheet

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

Range("B4").Select

If Target.Address = "$B$4" Then
Range("B5").Select
ElseIf Target.Address = "$B$5" Then

Application.EnableEvents = False
Range("B1").Value = Range("B4").Value & ": " & Range("B5").Value & "
" & Range("D4").Value
strFileName = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) -
4)
ActiveWorkbook.SaveCopyAs Range("B4").Value & "_" &
Range("B5").Value & "_" & _
strFileName & "_" & Format(Date,
"mmmdd_yy") & ".xls"
Application.Calculation = xlCalculationAutomatic

For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
Else
ws.Delete
End If
Next ws

Call formulas
Range("A3").Select
Sheets("Graphes Table").Visible = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Rick Rothstein]

Good catch... I completely missed that one in favor of the other error I
found in the posted code.

 

[Ryan H]

Ha ha! I can't believe I didn't see that. I was looking at a few other
things. Rick is right, good catch!

 

[Ayo]

Thanks Rick. My main problem is that when I change $B$5 none of the line of
codes following "ElseIf Target.Address() = "$B$5" And Target.Address() <> ""
Then" are executed. Thats what I mean by nothing happens.
Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim filename As String, ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

If Target.Address() = "$B$4" And Target.Address() <> "" Then
Range("B5").Select

ElseIf Target.Address() = "$B$5" And Target.Address() <> "" Then
Application.EnableEvents = False
Range("B1") = Range("B4") & ": " & Range("B5") & " " & Range("D4")
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
ActiveWorkbook.SaveCopyAs Range("B4") & "_" & Range("B5") & "_" &
filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook
If ws.Name <> "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
Call formulas
Range("A3").Select
Worksheets("Graphes Table").Visible = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Ryan H]

You may want to double check a few things.

1.) Put this in the Immediate Window: Application.EnableEvents = True.
This will ensure that the events weren't turned off which will keep the
Change Event from firing.

2.) Try this code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim filename As String
Dim ws As Worksheet

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

If Target.Address = "$B$4" Then
Range("B5").Select

ElseIf Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("B1") = Range("B4") & ": " & Range("B5") & " " & Range("D4")
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
ActiveWorkbook.SaveCopyAs Range("B4") & "_" & Range("B5") & "_" &
filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
Application.Calculate

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws

Sheets("BO Download").Delete
Call formulas
Range("A3").Select
Sheets("Graphes Table").Visible = False
ActiveWorkbook.Save
End If

With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

End Sub

3.) Why do you have Target.Address() = "$B$5" And Target.Address() <> ""?
I would just put Target.Address = "$B$5", because your Target will always
have an address.

4.) Change >>> For Each ws In ThisWorkbook with >>> For Each ws In
ThisWorkbook.Worksheets

5.) You don't neccessarly have to select Sheets("BO Download") to delete it
either so I would change,

Worksheets("BO Download").Select
Worksheets("BO Download").Delete

to

Sheets("BO Download").Delete

6.) I also see you change the Calculation mode from manual to automatic
back to manual. Why is that? Are you just wanting to calculate your
worksheet in that 2 line of code? Instead of

Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

just use

Application.Calculate

Have I confused you yet? lol Hope this helps, if so, let me know, click
"YES" below.

 

[Ayo]

Thanks for all the pointers Ryan. I will try them now.