"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count 1"don't work
The target.cells.count allows more than 1 cell selected in range, but takes the input in the activecell of the multiple cell selection.
The ElseIf "X_Clear" works fine from a separate sub, but it won't respond in the change event macro. Thanks. Howard Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$A$1:$H$10")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub Dim c As Range Dim Data1 As Range Set Data1 = Range("A1:H10") For Each c In Range("Data1") If c.Value = 0 Then c.Interior.ColorIndex = xlNone ElseIf c.Value = 5 Then c.Interior.ColorIndex = 4 ElseIf c.Value <= 4 Then c.Interior.ColorIndex = 3 ElseIf c.Value = "X_Clear" Then Dim Ans As Variant Ans = MsgBox(" " _ & " Clear Data1 ?", vbYesNo) Select Case Ans Case vbYes Range("Data1").ClearContents Range("Data1").Interior.ColorIndex = xlNone Case vbNo Exit Sub End Select End If Next End Sub |
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count 1" don't work
Hi Howard,
Am Fri, 12 Jul 2013 04:36:23 -0700 (PDT) schrieb Howard: The target.cells.count allows more than 1 cell selected in range, but takes the input in the activecell of the multiple cell selection. The ElseIf "X_Clear" works fine from a separate sub, but it won't respond in the change event macro. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$A$1:$H$10")) Is Nothing Or _ Target.Count 1 Then Exit Sub Dim c As Range Dim Data1 As Range Dim Ans As Integer Dim mycolor As Integer Set Data1 = Range("A1:H10") For Each c In Data1 Select Case c.Value Case "X_Clear" Ans = MsgBox(" " _ & " Clear Data1 ?", vbYesNo) Case 0 mycolor = xlNone Case Is = 5 mycolor = 5 Case Is <= 4 mycolor = 3 End Select If Ans = vbYes Then Data1.ClearContents Data1.Interior.ColorIndex = xlNone End If c.Interior.ColorIndex = mycolor Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count 1"don't work
Bravo, Claus, works fine.
Am I misunderstanding the line below about Target.Count? I can still select more than one cell in the target range and the activecell of that selection will take an input. I guess its really is no big deal, just thought that would eliminate multiple selections. Or Target.Count 1 Then Exit Sub Thanks. Howard |
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count 1" don't work
Hi Howard,
Am Fri, 12 Jul 2013 06:02:36 -0700 (PDT) schrieb Howard: Am I misunderstanding the line below about Target.Count? I can still select more than one cell in the target range and the activecell of that selection will take an input. I guess its really is no big deal, just thought that would eliminate multiple selections. yes, but you only change the active cell and that is only one cell. But you will get an error message when you select more cells and try to delete them. Or if you select more than one cell and make an input in the active cell and then press CTRL+Shift+Enter all cells will be filled but the makro doesn't start because target.count 1 Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count 1"don't work
On Friday, July 12, 2013 6:32:07 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Fri, 12 Jul 2013 06:02:36 -0700 (PDT) schrieb Howard: Am I misunderstanding the line below about Target.Count? I can still select more than one cell in the target range and the activecell of that selection will take an input. I guess its really is no big deal, just thought that would eliminate multiple selections. yes, but you only change the active cell and that is only one cell. But you will get an error message when you select more cells and try to delete them. Or if you select more than one cell and make an input in the active cell and then press CTRL+Shift+Enter all cells will be filled but the makro doesn't start because target.count 1 Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Okay, got it. Thanks again. Regards, Howard |
All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com