Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |