You are here:

Excel/Add Multiple Sets Of Cells For VBA Worksheet_Change

Advertisement


Question
QUESTION: My goal is to have a set of cells that change depending on the value of other cells. For example, I need cells A1:A5 to operate independently from the cells in A6:A10.

Right now I have it set up if I put the word "Enabled" in A1 then all other cells in the A1:A10 will change to "Paused" except A1. But I want to be able to put "Enabled" in cell A1 and only change within the set of A1:A5 then be able to put "Enabled" within another group of cells without changing anything in cells A1:A5.

Here is my code so far:

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   Set watch_range = Range("A1:A10")
   If Union(watch_range, Target).Areas.Count = 1 Then
       Set subrange = Range("A1:A5", "A6:A10")
       n = Target.Value
       subrange.Value = "Paused"
       Target.Value = "Enabled"
   End If
   Application.EnableEvents = True
End Sub

ANSWER: Derek,

You code doesn't match you description.  It doesn't require that the word enabled is entered.  This would be my guess as to the code that would do what you describe.  It requires that enabled is entered in A1 and then writes A2:A5 to Paused.   It will also act the same for enabled entered in A6 with A7:A10 altered to say Paused.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Row > 10 Then Exit Sub
Application.EnableEvents = False
 
If Target.Address = "$A$1" Then
 If InStr(1, Target, "enabled", vbTextCompare) = 1 And Len(Target) = 7 Then
  Range("A2:A5").Value = "Paused"
 End If
End If
If Target.Address = "$A$6" Then
 If InStr(1, Target, "enabled", vbTextCompare) = 1 And Len(Target) = 7 Then
  Range("A7:A10").Value = "Paused"
 End If
End If
Application.EnableEvents = True
End Sub

that worked for me.  

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hey Tom,

Thank you for your prompt response! Sorry, for not explaining better.

I want the A1:A5 cells to be a set. If I enter "Enabled" anywhere within this set, the other cells within the set will change to "Paused".

I also want a second set A6:A10 to have an identical function where I can enter "Enabled" anywhere within this second set, and the other cells will change to "Paused" without affecting the first set of cells.

I hope this makes better sense!

Derek

ANSWER: Derek,

this worked for me.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim watchrange As Range, cell As Range, i As Long
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Row > 10 Then Exit Sub
Application.EnableEvents = False
Dim v(1 To 2) As String
v(1) = "A1:A5"
v(2) = "A6:A10"
For i = 1 To 2
Set watchrange = Range(v(i))
If Not Intersect(Target, watchrange) Is Nothing Then
 If InStr(1, Target, "enabled", vbTextCompare) = 1 And Len(Target) = 7 Then
  For Each cell In watchrange
      If cell.Address <> Target.Address Then
         cell.Value = "Paused"
      End If
  Next
 End If
End If
Next i
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Ah Yes! Great that works exactly how I wanted. Thank you!

I have been messing around with code to shift this system to other columns and rows as well as adding multiple ranges.

One other question, is there a way to have the set of cells interact with one another no matter where they are in the column? For example, if I have the system running A1:A50 with sets of five cells - 10 sets - and A1,A2,A3,A4,A5 are sorted and become A11, A22, A37, A39, A48, Can I change A22 to "Enabled" and A11, A37, A39, A48 will automatically change to "Paused"?

Is this consider locking or protecting the cells?

Thanks again for your help!

Derek

Answer
Derek,

If you mean you would have
v(1) = "A1:A5"

in the code and then if you sorted it, the code would know to use A11,A22,A37,A39,A48 instead then no that isn't possible.

If you wanted to manually change

v(1) = "A1:A5"

to
v(1) = "A11,A22,A37,A39,A48"

then the code would work with a non contiguous range - but the code would not automatically adjust because you sorted or otherwise moved a cell.   You can create a named range to reference your cells - but even a named range is not going to adjust based on a sort.
--
Regards,
Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.