If target.intersect begins with a bracket "(" then exit sub..

[omairhe]

Hello all,

In the following code if the change in value occurs in range M5:M1000 the code will run. However, this is true with any value change. What if I would like to neglect the code operation from change code if the value in that range (M5:M1000) begin with a "(" bracket. And thus do nothing if this case is true.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("M5:M1000")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Thanks and will appreciate.

 

[My Aswer Is This]

Hello all,

In the following code if the change in value occurs in range M5:M1000 the code will run. However, this is true with any value change. What if I would like to neglect the code operation from change code if the value in that range (M5:M1000) begin with a "(" bracket. And thus do nothing if this case is true.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("M5:M1000")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Thanks and will appreciate.

I would like to see your entire code. You only showed us a portion of your code.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/14/2020  12:43:31 AM  EST

If Not Intersect(Target, Range("M5:M1000")) Is Nothing Then
    If Left(Target.Value, 1) <> "(" Then
        MsgBox "Do something"
    End If
End If
End Sub

 

[omairhe]

I would like to see your entire code. You only showed us a portion of your code.

Ok, but its not that pretty ...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("M5:M1000")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range(Target.Address).EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(0, 11).Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    ActiveCell.Offset(1, 1).Range("A1").Select
    With Selection
    .Value = "(All)"
    .Locked = False
    End With
    Dim sRow As Long, eRow As Long
    Dim rng As Range
    eRow = ActiveCell.Row - 1
    sRow = ActiveCell.Offset(-1, 0).End(xlUp).Row
    Set rng = Rows(sRow & ":" & eRow)
    rng.Sort key1:=Range("M" & sRow), order1:=xlAscending
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

 

[omairhe]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/14/2020  12:43:31 AM  EST

If Not Intersect(Target, Range("M5:M1000")) Is Nothing Then
    If Left(Target.Value, 1) <> "(" Then
        MsgBox "Do something"
    End If
End If
End Sub

Thanks , I am gonna give it a try now.

 

[omairhe]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/14/2020  12:43:31 AM  EST

If Not Intersect(Target, Range("M5:M1000")) Is Nothing Then
    If Left(Target.Value, 1) <> "(" Then
        MsgBox "Do something"
    End If
End If
End Sub

Getting an error Runtime error 13, Type mismatch

I am working with pivot tables and want to add new counrty name and sort it automatically. Here is a preview of the sheet I am working with.

If the user selects the dropdown menu and selects (All). This entry will be passed on and reach the top of the list area. Hence I needed to bypass the open bracket "(".

I am posting the excel file using Dropbox Transfer if it is ok...

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

Thanks for your help.

 

[omairhe]

Please note that my actual data has more columns to it than the demo file I posted here. Therefore I need the entire row to be copied down to the list without affecting the integrity of other rows.

But it works pretty much the same. Also the pivot table cells are already formatted as white background color and looks neat in the orginal file.

 

[My Aswer Is This]

The error your getting is not because of my small amount of code.
I suspect it's part of your code.
I'm not able to help with all the rest of your code. I'm not familiar with Pivot Tables. And I never down load code posted on this site.

 

[omairhe]

The error your getting is not because of my small amount of code.
I suspect it's part of your code.

Yes I agree. Perhaps something to do with column L which gets changed along with column M every time the user selects from the dropdown menu.
Anyways thanks and I appreciate your time.

P.s. I am still open for suggestions from anyone and will greatly appreciate any help at all. ; )

 

[omairhe]

After some playing around with your code I was able to solve this like.

If Not Intersect(Target, Range("M5:M1000")) Is Nothing Then
If Left(Target.Offset(0, 1).PivotItem.Value, 1) <> "(" Then

I guess they say it right. 'Little goes a long way.' Hehe
But yh sorry to not have posted the information about the pivot table in my original post.

Ty