Run time error: Application-defined or object-defined error

[Iron_Man]

Hi all,

This is my first message, I hope I'm not doubling up and the answers to my problem isn't already in the forum (which I've gone through extensively).

I've just started VBA from scratch and with the help of several macros I found here and there, I wrote this code which works fine. My problem is that when I open the file on the spreadsheet "Berth Summary", I get the error listed in the subject of this thread.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    If ActiveSheet.Name = "Berth Summary" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorCoke")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Coke Schedule", "ScenarioSelectorCoke")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorAlF3")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "AlF3 Schedule", "ScenarioSelectorAlF3")
        End If
    ElseIf ActiveSheet.Name = "Al Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        End If
    ElseIf ActiveSheet.Name = "Coke Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorCoke")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Coke Schedule", "ScenarioSelectorCoke")
        End If
    ElseIf ActiveSheet.Name = "AlF3 Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAlF3")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "AlF3 Schedule", "ScenarioSelectorAlF3")
        End If
    
    End If


End Sub

Could anyone point me in the right direction or highlight what is clearly going wrong? I have no knowledge in coding and feel that it is a simple mistake

Thanks in advance!

IM

 

[mole999]

must say i would be tempted to do

On Berth Summary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Name = "Berth Summary" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorCoke")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Coke Schedule", "ScenarioSelectorCoke")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorAlF3")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "AlF3 Schedule", "ScenarioSelectorAlF3")
        End If
    End Sub

On A1 Schedule

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Name = "Al Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        End If
    End Sub

the related code only on the related sheet (though I appreciate this not always possible if sheets are destroyed and rebuilt)

 

[Iron_Man]

Hi everyone and mole999,

Apologies for the delay in responding, I was travelling and didn't get to check back on this thread.

Anywayyyyyy, I thought it may provide more clarity if I explained what I'm trying to do and include the two macros I have:

. if I change ScenarioselectorAl (name range on sheet only) on "Berth Summary", I'd like ScenarioselectorAl (name range on sheet only) to have the same value on "Al Schedule"; vice versa if I change ScenarioselectorAl on Al Schedule, I'd like the same value on Scenarioselector Al on "Berth Summary"

. if I change ScenarioselectorCoke (name range on sheet only) on "Berth Summary", I'd like ScenarioselectorCoke (name range on sheet only) to have the same value on "Coke Schedule"; vice versa if I change ScenarioselectorCoke on Coke Schedule, I'd like the same value on ScenarioselectorCoke on "Berth Summary"

. . if I change ScenarioselectorAlF3 (name range on sheet only) on "Berth Summary", I'd like ScenarioselectorAlF3 (name range on sheet only) to have the same value on "AlF3 Schedule"; vice versa if I change ScenarioselectorAlF3 on AlF3 Schedule, I'd like the same value on ScenarioselectorAlF3 on "Berth Summary".

So, I have these two macros which work reasonably well but create a run time error if I open the workbook on "berth Summary", likely to happen every single time:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    If ActiveSheet.Name = "Berth Summary" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorCoke")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Coke Schedule", "ScenarioSelectorCoke")
        ElseIf Not Intersect(Target, Sh.Range("ScenarioSelectorAlF3")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "AlF3 Schedule", "ScenarioSelectorAlF3")
        End If
    ElseIf ActiveSheet.Name = "Al Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAl")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Al Schedule", "ScenarioSelectorAl")
        End If
    ElseIf ActiveSheet.Name = "Coke Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorCoke")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "Coke Schedule", "ScenarioSelectorCoke")
        End If
    ElseIf ActiveSheet.Name = "Al2F3 Schedule" Then
        If Not Intersect(Target, Sh.Range("ScenarioSelectorAlF3")) Is Nothing Then
            Call DO_All(Sh, Target, "Berth Summary", "AlF3 Schedule", "ScenarioSelectorAlF3")
        End If
    
    End If


End Sub


Private Sub DO_All(ByVal Sh As Object, ByVal Target As Range, ByVal Sheet1 As String, ByVal Sheet2 As String, ByVal DestRange As String, Optional ByVal sheet3 As String = "")
    Application.EnableEvents = False
    
    If Len(sheet3) = 0 Then
        If ActiveSheet.Name = Sheet1 Then
            Sheets(Sheet2).Range(DestRange) = Target.Value
        ElseIf ActiveSheet.Name = Sheet2 Then
            Sheets(Sheet1).Range(DestRange) = Target.Value
        End If
    Else
        If ActiveSheet.Name = Sheet1 Then
            Sheets(Sheet2).Range(DestRange) = Target.Value
            Sheets(sheet3).Range(DestRange) = Target.Value
        ElseIf ActiveSheet.Name = Sheet2 Then
            Sheets(Sheet1).Range(DestRange) = Target.Value
            Sheets(sheet3).Range(DestRange) = Target.Value
        ElseIf ActiveSheet.Name = sheet3 Then
            Sheets(Sheet1).Range(DestRange) = Target.Value
            Sheets(Sheet2).Range(DestRange) = Target.Value
        End If
    End If
 
    Application.EnableEvents = True
        
End Sub

Finally, the person who helped me with this wrote it so I could eventually add ScenarioselectorAl (for example) onto a third sheet and have the same change across the three sheets.

Thanks in advance to anyone who can help me this. I spend about 100 hours building the entire model and this is my last piece

IM

 

[mole999]

if one or other sheet is accessed. and then you update the other to show the same, do you not destroy the validity of the data

if a = 23 and b = 9

ex a on to b will make 23

b onto a will make 9

ONCE ONLY

 

[Iron_Man]

At any point in time, the two cells (eg ScenarioSelectorAl) should have the same value. They drive different tables on different sheets but using the same set of data. The ScenarioSelector cells define which set of data is being used across the two spreadsheets.

Does this make sense?

 

[Iron_Man]

Hi guys,

Well I feel rather silly, I think the error was related to another macro which I deleted today as I did not change anything and the error has now disappeared.

Thanks anyway for all your help!

Johann