• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Worksheet_Change: Check if user is deleting/inserting rows or columns [SOLVED]

inddon

Member
Hello There,

I have the below code to capitalize entered text in specific range of cells and it works good.

The problem is when I delete or insert chunk of rows or columns, the below code gets fired and uses quite a bit of the memory (the laptop fan noise shoots up).

What I would like is the below code to fire only while entering the cell values and not during insert/delete of rows and columns.

Could you please advise.

Thanks & regards
Don



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim cell As Range
   
  'converts texts into upper case for selected columns
  If Not Application.Intersect(Target, Range("H6:H5000,K6:K5000")) Is Nothing Then
  Application.EnableEvents = False
  For Each cell In Intersect(Target, Range("H6:H5000,K6:K5000"))
  If Not cell.HasFormula Then
  cell.Value = UCase(cell)
  End If
  Next
  Application.EnableEvents = True
  End If
End Sub
 
Hi Don,

At beginning, right after first Dim, add this line:
Code:
If Target.Count > 1 Then Exit Sub
 
Also note that your existing approach assumes that the user has just entered something into every column between H and K. Suggest you amend to the below:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Application.Intersect(Target, Range("SomeRange")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            If Not cell.HasFormula Then
                cell.Value = UCase(cell)
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

...where SomeRange is a named range pointing to the columns of interest. (It's almost always better to use Named Ranges in VBA code than hard coded ones, because those hard coded ranges don't update if someone inserts or deletes new columns/rows into the workbook)

Chip Pearson uses something slightly different on his site. See http://www.cpearson.com/excel/ChangingCase.aspx
If it's on Chips stite, it's probably the most efficient way.
 
Also note that your existing approach assumes that the user has just entered something into every column between H and K. Suggest you amend to the below:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Application.Intersect(Target, Range("SomeRange")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            If Not cell.HasFormula Then
                cell.Value = UCase(cell)
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

...where SomeRange is a named range pointing to the columns of interest. (It's almost always better to use Named Ranges in VBA code than hard coded ones, because those hard coded ranges don't update if someone inserts or deletes new columns/rows into the workbook)

Chip Pearson uses something slightly different on his site. See http://www.cpearson.com/excel/ChangingCase.aspx
If it's on Chips stite, it's probably the most efficient way.



That is a very good tip. I learned something new from you today.

Regards
Don
 
Also note that your existing approach assumes that the user has just entered something into every column between H and K. Suggest you amend to the below:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Application.Intersect(Target, Range("SomeRange")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            If Not cell.HasFormula Then
                cell.Value = UCase(cell)
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub



...where SomeRange is a named range pointing to the columns of interest. (It's almost always better to use Named Ranges in VBA code than hard coded ones, because those hard coded ranges don't update if someone inserts or deletes new columns/rows into the workbook)

Chip Pearson uses something slightly different on his site. See http://www.cpearson.com/excel/ChangingCase.aspx
If it's on Chips stite, it's probably the most efficient way.



Hello Jeffrey,

I tried to give the named range as below, it gives error.
Code:
If Not Application.Intersect(Target, Range("Currency","Country", "City")) Is Nothing Then

Could you please advice on how you can mention multiple named ranges in this case?

Thanks & regards
Don
 
Hi Don ,

Specifying multiple ranges in VBA is done by using the UNION function , as in :

Code:
If Not (Application.Intersect(Target, Union(Range("Currency"),Range("Country"),Range("City"))) Is Nothing Then
Narayan
 
Hi Don ,

Specifying multiple ranges in VBA is done by using the UNION function , as in :

Code:
If Not (Application.Intersect(Target, Union(Range("Currency"),Range("Country"),Range("City"))) Is Nothing Then
Narayan


Hello Narayan,

It gives a Runtime error 424.

Could you please advise.

Thanks & regards
Don
 
Hi Don ,

Sorry , my mistake ; try this :
Code:
If Not (Application.Intersect(Target, Union(Range("Currency"), Range("Country"), Range("City"))) Is Nothing) Then
Narayan
 
In addition to the great way that Narayan is using the Intersect operator, you could also define a new 'Master' named range that points to the other named ranges, and just use the master in VBA.

No benefits really in this particular case, but handy to know. I often have different 'master' names that reference different bundles of 'slave' named ranges that I use as appropriate.
 
In addition to the great way that Narayan is using the Intersect operator, you could also define a new 'Master' named range that points to the other named ranges, and just use the master in VBA.

No benefits really in this particular case, but handy to know. I often have different 'master' names that reference different bundles of 'slave' named ranges that I use as appropriate.


Hello Jeffrey,

Thank you for your response.

Could you give me an example of your proposed advice. It will help in understanding.


regards
Don
 
What I mean is that instead of using this:
Code:
If Not Application.Intersect(Target, Union(Range("Currency"), Range("Country"), Range("City")))Is Nothing) Then

...you could define a name using the Name Manager called 'UnionedNames' as in the attached picture


...and then just refer to that name in VBA like this:
Code:
If Not Application.Intersect(Target, Range("UnionedNames")) IsNothing Then
 

Attachments

  • NameManager.gif
    NameManager.gif
    12 KB · Views: 5
What I mean is that instead of using this:
Code:
If Not Application.Intersect(Target, Union(Range("Currency"), Range("Country"), Range("City")))Is Nothing) Then

...you could define a name using the Name Manager called 'UnionedNames' as in the attached picture


...and then just refer to that name in VBA like this:
Code:
If Not Application.Intersect(Target, Range("UnionedNames")) IsNothing Then


Wow! Superb. I am going to try this out.

I have learned something new from you today. Thanks :)

Regards
Don
 
Hello Luke,

I am grateful for your answer as well. But could you please explain what was happening in the first place? And why does this change solve the problem (i.e. deleting only one cell does not trigger the error)?

Thanks,
Lurco
 
Hi Lurco,

The change event macro gets called whenever there is a change to the worksheet, including deleting cells. In this case, OP didn't want macro to trigger when deleting a large block of cells. A common test to put at the beginning of event macros is to make sure user was only modifying one cell.

The Target variable refers to the range that was changed. So, we check the count (aka, how many cells) the user changed. If the answer is more than 1, then the macro immediately quits, and the problem is resolved.
 
Back
Top