VBA Workbook SheetChange Conflict

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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.


Code:
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! :cool:

Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
At the beginning of your procedures put:

Application.EnableEvents = False

and at the end:

Application.EnableEvents = True
 

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
At the beginning of your procedures put:

Application.EnableEvents = False

and at the end:

Application.EnableEvents = True


Ahhhh, :eek: 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.:oops:

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

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
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! :cool:


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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
When you have this:

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
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
 

Forum statistics

Threads
1,136,853
Messages
5,678,132
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top