*** Worksheet_Change Error - Combining code in the same worksheet ! ***

[Zwen]

Hi, Am trying to combine two Worksheet_Change commands in the same worksheet but keep getting a Worksheet_Change error message. The codes are below and they are each running off a separate combo boxes located on the same work sheet performing separate functions on two alternative worksheets. I'm aware that they can be combined but so far have not been able to figure out how to do this correctly. Any help would be appreciated. Tx

1#

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$I$24" Then
Sheets("Inputs").Scenarios(Target.Value).Show

End If

End Sub



2#

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$I$29" Then
Sheets("DCF_Analysis").Scenarios(Target.Value).Show

End If

End Sub

 

[shg]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(False, False)
        Case "I24"
            Worksheets("Inputs").Scenarios(Target.Value).Show
        Case "I29"
            Worksheets("DCF_Analysis").Scenarios(Target.Value).Show
    End Select
End Sub

 

[Zwen]

Tx for the help. Unfortunately the second code doesn't work when I use that formulation. The first works perfectly though...

 

[shg]

That code replaces both of yours.

It assumes that the changing cells (I24 and I29) are both on the same sheet, and that the cells change via manual entry (or other code), not as the result of formulas.

 

[Zwen]

I do understand that the code you supplied replaces both of mine and have done so but as per my last the first code works but the second doesn't. Perhaps a bit more detail would be of use.

I'm trying to run two combo (drop downs) boxes located on the same work sheet which drive scenarios each respectively drive scenarios on two other worksheets (i.e. not on the same worksheet that they are located on). I included the first code (1#) on the worksheet several weeks ago and am now trying to add the second (2#) as I have added another combo box to the first.

When I run the second combo box I get a 'debugging error' and the line: 'Worksheets("DCF_Analysis").Scenarios(Target.Value).Show' is highlighted in yellow. When hovered above it states that "Target.Value="GGM" one of the two options for the particular combo box associated with that code.

Hope thats clarified. Tx again.

 

[Zwen]

VBA - Worksheet_Change Error# - How to combine two codes in the same worksheet

Hi, Am trying to combine two Worksheet_Change commands in the same worksheet but keep getting a Worksheet_Change error message when I try and select one of the drop down options.

The codes are below. They are each running off separate combo "drop down" boxes located on a work sheet entitled 'Assumptions' and Code 1# (Combo Box 1#) affects a change in a worksheet entitled "Inputs and Code 2# (Combo Box 2#) affects a change in a worksheet entitled "DCF Analysis". Each combo box is driven by a 'Scenario Manager' (located on the respective work sheets on which the changes are being affected namely "Inputs" and "DCF Analysis").

Code 1 has worked fine for the last two weeks until I tried to add the second combo box to the worksheet and add the associated code.

I'm aware that the codes can be combined but so far have not been able to figure out how to do this correctly. Any help would be appreciated. Tx

1#

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$I$24" Then
Sheets("Inputs").Scenarios(Target.Value).Show

End If

End Sub



2#

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$I$29" Then
Sheets("DCF_Analysis").Scenarios(Target.Value).Show

End If

End Sub

 

[Tom Urtis]

Re: VBA - Worksheet_Change Error# - How to combine two codes in the same worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$24" Then
Sheets("Inputs").Scenarios(Target.Value).Show
elseif If Target.Address = "$I$29" Then
Sheets("DCF_Analysis").Scenarios(Target.Value).Show
End If
End Sub

 

[Joe4]

Re: VBA - Worksheet_Change Error# - How to combine two codes in the same worksheet

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=569105

Please do not post the same question twice. Per forum rules, all post of a duplicate nature will be locked or deleted (http://www.mrexcel.com/forum/showthread.php?t=99490).

If you have a follow-up, clarification, or "bump", please post it back to the original thread.

 

[Joe4]

Since there were replies to both threads, I have merged them together (posts 6,7, and 8 were from new thread).

 

[Zwen]

Hi Tom, Many thanks for the help. The code you gave me is generating an error for both combo boxes as below. Any thoughts?

'Compile error:

Syntax error.'