VBA Workbook SheetChange Conflict

[KP_SoCal]

Below is a code that most of you experts will realize right away that it will crash my excel document as soon as I run it.

In each of my sheet names that begin with "Series" I have a data validation combo box embedded in the cell. Each time I select a certain value, I use the "SheetChange" event to bring me to the appropriate sheet.

The part that starts giving me problems is this. Lets say I select "Widget4" in my dropdown list for Sheet name "Series1". I not only want to go to "Series4" sheet, but want the value in cell A6 to reflect "Widget4" as a value instead of "Widget1", which is the default value.

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

       If ActiveSheet.Name Like "Series*" Then
            If Not Intersect(Target, Range("A6")) Is Nothing Then
                   Select Case Range("C5")
                        Case "1"
                            Sheets("Series1").Select
                            Range("A6") = Range("B8")
                        Case "4"
                            Sheets("Series4").Select
                            Range("A6") = Range("B8")
                        Case Else
                            MsgBox "Something weird is going on...", vbCritical, "Uuh-oohh"
                    End Select
            
            End If
       End If
     
End Sub

I tried doing setting this value in the "Sheet Activation" event (see code below), but as you can guess, the two events are in total conflict with one another.

If any of you experts have a solution to this dilemma, I would be most grateful. Thanks!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    For Each wsSheet In Worksheets
    
       If wsSheet.Name Like "Series*" Then
                With wsSheet.Range("A6")
                    .Value = wsSheet.Range("A5")
                End With
       End If
     
    Next wsSheet

End Sub

 

[Andrew Poulsom]

At the beginning of your procedures put:

Application.EnableEvents = False

and at the end:

Application.EnableEvents = True

 

[KP_SoCal]

At the beginning of your procedures put:

Application.EnableEvents = False

and at the end:

Application.EnableEvents = True

Ahhhh, I've literally spent hours trying to come up with work-arounds for this. I must've tried to re-write it a hundred different ways trying to get it to work.

Your suggestion absolutely held the key. Yay and thank you, thank you!!! I really appreciate it...

 

[KP_SoCal]

Hey Andrew, I attempted to set my range in a Dim statement so I can use this line of code--myRange1.Value = myRange2.Value--in my Case statement instead of using Range("A6").Value = Range("A5").Value.

However, when the code is executed, myRange1.Value = myRange2.Value is not processed. However, when it's written as Range("A6").Value = Range("A5").Value, it is processed.

Do you know what adjustments I need to make to myRange1.Value = myRange2.Value to get it to work? Thanks again!



Full Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myRange1 As Range
Dim myRange2 As Range
Set myRange1 = Range("A6")
Set myRange2 = Range("A5")
 
Application.EnableEvents = False
    If ActiveSheet.Name Like "Series*" Then
         If Not Intersect(Target, Range("A6")) Is Nothing Then
                Select Case Range("C5")
                     Case "1"
                         Sheets("Series1").Select
                         myRange1.Value = myRange2.Value
                         'Range("A6").Value = Range("A5").Value
                     Case "2"
                         Sheets("Series2").Select
                          myRange1.Value = myRange2.Value
                         'Range("A6").Value = Range("A5").Value
                     Case Else
                         MsgBox "A very weird Series must have been added.  Check this out!", vbCritical, "Uuh-oohh"
                 End Select
 
 
         End If
    End If
 
Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

That's because you haven't qualified the ranges and the active sheet is being used. You might get away with:

With Sh
    Set myRange1 = .Range("A6")
    Set myRange2 = .Range("A5")
End With

but if C5 can be 1 or 2 on either sheet it won't work because you are switching sheets.

By the way you should use Sh instead of ActiveSheet.

 

[KP_SoCal]

Thanks for the quick reply. I made the adjustments, but myRange1.Value = myRange2.Value still isn't applying the update. Still, the only way to get it to update is explicitly write it out like this Range("A6").Value = Range("A5").Value.

I'm such a beginner with VBA, I might be misunderstanding something or overlooking something very obvious. It might be something to do with C5 possibly equating to either "1" or "2" (text values) on either worksheet. Still though, if that was the case, then I would think Range("A6").Value = Range("A5").Value wouldn't work either? Thanks so much for the help!


Code Start
-------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myRange1 As Range
Dim myRange2 As Range

With Sh
Set myRange1 = .Range("A6")
Set myRange2 = .Range("A5")
End With

Application.EnableEvents = False
If Sh.Name Like "Series*" Then
If Not Intersect(Target, Range("A6")) Is Nothing Then
Select Case Range("C5")
Case "1"
Sheets("Series1").Select
myRange1.Value = myRange2.Value
Case "2"
Sheets("Series2").Select
myRange1.Value = myRange2.Value
Case Else
MsgBox "A very weird Series must have been added. Check this out!", vbCritical, "Uuh-oohh"
End Select


End If
End If

Application.EnableEvents = True
End Sub

-------------------------
Code End

 

[Andrew Poulsom]

When you have this:

Sheets("Series1").Select
Range("A6").Value = Range("A5").Value

the Range properties aren't qualified with a worksheet, so the active sheet is used and that happens to be Series1. If Series1 isn't active when the event fires, the myRange1 variable wouldn't refer to A6 on Series1, but to A6 on the active sheet. So your code is running, but probably on the wrong sheet.

 

[KP_SoCal]

Ok, gochya. Anyhow, I think I at least have enough information to get in the general direction I need. Hey, thanks for the tip on replacing "Activesheets" with "SH". Good stuff!

KP