Solved: Call Sub Help

[gimli]

Hey all,

Im confused why this code doesnt work. It seems simple..just trying to call on cell change. The subs are located in the module 1...is that right? Should I also combine the case F9 and F13 on one line?

thanks


[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address

Case "$F$9"
Call Cortneycoxishot
Case "$F$13"
Call Cortneycoxishot

End Select
Exits:
Application.EnableEvents = True
End Sub
Private Sub TOLCHOICE_Change()
Call Cortneycoxishot
End Sub
[/VBA]

[mdmackillop]

The Worksheet Change code must be in the appropriate Sheet module.

[Bob Phillips]

The subs are located in the module 1...is that right? Should I also combine the case F9 and F13 on one line?

Yes, and yes.

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Exits

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("F9, F13")) Is Nothing Then

Call Cortneycoxishot

End If

Exits:
Application.EnableEvents = True
End Sub
[/vba]

[gimli]

Ok..here is what works so far. This code does what I want it to do. When the target cell value changes it runs the sub properly.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address
Case "$F$9"
Call Cortneycoxishot
Case "$F$13"
Call Cortneycoxishot

Case "$F$8"
Call Cortneycoxishot
End Select
Exits:
Application.EnableEvents = True
End Sub
[/VBA]


I also want the sub to run when a drop down box value is changed. But when I add this code..it doenst work and craps out.

[VBA]
Private Sub TOLCHOICE_Change()

Call Cortneycoxishot

End Sub
[/VBA]

It craps out here..see red. Any ideas why?

[VBA]
Sub Cortneycoxishot()

Application.ScreenUpdating = False
Dim i As Integer
Columns(11).Insert
For i = 8 To 17
Cells(i, 11) = Abs(Cells(i, 12) - Cells(13, 6))
Next
Range("J8:R17").Sort key1:=Range("K8:K17"), order1:=xlAscending, Header:=xlNo
Columns(11).Delete


Dim j As Integer
Columns(14).Insert
For j = 8 To 17
Cells(j, 14) = Abs(Cells(j, 15) - Cells(9, 6))
Next
Range("J8:R17").Sort key1:=Range("N8:N17"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
Application.ScreenUpdating = True

End Sub
[/VBA]

[gimli]

Ok..so I attached a spreadsheet with the problem code. The code will crap out upon drop down selection change.

I want it to run when drop down selection changes. Like I said above..the sub works if I use call it when a cell changes.

thanks much

[Bob Phillips]

[vba]
Option Explicit

Private ReEntry As Boolean

Private Sub TOLCHOICE_Change()
Dim i As Long
Dim j As Long

If Not ReEntry Then

ReEntry = True

Application.ScreenUpdating = False
Columns(11).Insert
For i = 8 To 17
Cells(i, 11) = Abs(Cells(i, 12) - Cells(13, 6))
Next
Range("J8:R17").Sort key1:=Range("K8:K17"), order1:=xlAscending, Header:=xlNo
Columns(11).Delete

Columns(14).Insert
For j = 8 To 17
Cells(j, 14) = Abs(Cells(j, 15) - Cells(9, 6))
Next
Range("J8:R17").Sort key1:=Range("N8:N17"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
Application.ScreenUpdating = True

ReEntry = False
End If
End Sub
[/vba]

[gimli]

XID
thanks...why does that work? Just trying to understand

[gimli]

Ok..I used your code and it works for the drop down. But I also want to run the same sort when cells F9,F13 OR F8 changes. When I put this code in the case calls dont work...cortneyishot is the same sort you have in your code.

[VBA]
Option Explicit
Private ReEntry As Boolean

Private Sub TOLCHOICE_Change()
Dim i As Long
Dim j As Long

If Not ReEntry Then

ReEntry = True

Application.ScreenUpdating = False
Columns(11).Insert
For i = 8 To 17
Cells(i, 11) = Abs(Cells(i, 12) - Cells(13, 6))
Next
Range("J8:R17").Sort key1:=Range("K8:K17"), order1:=xlAscending, Header:=xlNo
Columns(11).Delete

Columns(14).Insert
For j = 8 To 17
Cells(j, 14) = Abs(Cells(j, 15) - Cells(9, 6))
Next
Range("J8:R17").Sort key1:=Range("N8:N17"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
Application.ScreenUpdating = True

ReEntry = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address
Case "$F$9"
Call cortneyishot
Case "$F$13"
Call cortneyishot
Case "$F$8"
Call cortneyishot
End Select
Exits:
Application.EnableEvents = True
End Sub
[/VBA]

[gimli]

XID,

Here is an updated file..with your code and case call which doesnt work if you dont mind looking at it. Keep in mind I may want to call sub cortney in other cases.

thanks much

[mdmackillop]

Hi Gimli,
Not looked in detail, but I think you want to disable events in your dropdown code to prevent looping of the SheetChange code

[tpoynton]

if it is the column.insert that is failing, it may be because you have data all the way out in column 256 that prevents the insert.

[gimli]

Holly Sheeeite Batman!

Got it working thanks to all you guys help.

Heres what works

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address
Case "$F$9"
Call cortneycoxishot

Case "$F$13"
Call cortneycoxishot

Case "$F$8"
Call cortneycoxishot

End Select
Exits:
Application.EnableEvents = True
End Sub
Private Sub TOLCHOICE_Change()
On Error GoTo Exits
Application.EnableEvents = False
Call cortneycoxishot

Exits:
Application.EnableEvents = True
End Sub
[/VBA]