Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Statement using a Range of Conditions
I need to apply a subroutine to a large number of cells within a
spreadsheet. I do not wish to type out individual case condition statements for all the spreadsheet cells required. I am attempting to use a case statement to call the subroutine and I am declaring the cells by making use of a variable of type Range and by declaring the Range within a Workbook event, in the following manner: Public Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) With ActiveSheet Select Case Target.Address Case "$C$6" To "$N$10", "$C$10", "$N$6" Call ThisWorkbook.InsertComment(sheet) Case "$C$13" To "$N$28", "$N$13" Call ThisWorkbook.InsertComment(sheet) Case Else MsgBox "Not Allowed" End Select Cancel = True End With End Sub First of all when declaring the Target.Address with 'Case "$C$6" To "$N $10"', it seems to miss the two cells in the bottom left and top right corners. Hence the reason why I have added the additional conditions "$C $10" and "$N$6". The second case statement, 'Case "$C$13" To "$N$28", "$N$13"', seems to allow the application of the subroutine to cells that are out-with the intended field. The intended field being "$C$13" To "$N$28". Can anyone explain why this is happening and offer a suitable method to allow the accurate allocation of the subroutine to only those cells intended, without having to type a case condition for every cell required. Regards Aardvark -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Statement using a Range of Conditions
Public Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean) Dim rng1 as Range, rng2 as Range With ActiveSheet set rng1 = .Range("C6:N10") set rng2 = .Range("C13:N28") if not intersect(Target,rng1) is nothing then Call ThisWorkbook.InsertComment(sheet) Elseif not intersect(Target,rng2) is nothing then Call ThisWorkbook.InsertComment(sheet) else MsgBox "Not Allowed" End If Cancel = True End With End Sub -- Regards, Tom Ogilvy "Andrew Bird via OfficeKB.com" wrote in message ... I need to apply a subroutine to a large number of cells within a spreadsheet. I do not wish to type out individual case condition statements for all the spreadsheet cells required. I am attempting to use a case statement to call the subroutine and I am declaring the cells by making use of a variable of type Range and by declaring the Range within a Workbook event, in the following manner: Public Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) With ActiveSheet Select Case Target.Address Case "$C$6" To "$N$10", "$C$10", "$N$6" Call ThisWorkbook.InsertComment(sheet) Case "$C$13" To "$N$28", "$N$13" Call ThisWorkbook.InsertComment(sheet) Case Else MsgBox "Not Allowed" End Select Cancel = True End With End Sub First of all when declaring the Target.Address with 'Case "$C$6" To "$N $10"', it seems to miss the two cells in the bottom left and top right corners. Hence the reason why I have added the additional conditions "$C $10" and "$N$6". The second case statement, 'Case "$C$13" To "$N$28", "$N$13"', seems to allow the application of the subroutine to cells that are out-with the intended field. The intended field being "$C$13" To "$N$28". Can anyone explain why this is happening and offer a suitable method to allow the accurate allocation of the subroutine to only those cells intended, without having to type a case condition for every cell required. Regards Aardvark -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case Statement | Excel Discussion (Misc queries) | |||
Case Statement error | Excel Programming | |||
Case Statement Help | Excel Programming | |||
Case statement | Excel Programming | |||
Case statement in variable range | Excel Programming |