PDA

View Full Version : Case Statement



gimli
05-03-2010, 06:25 AM
Hey all,

Curious how this statement will work. F13 calls sub test1 and the next line test 2. Will both the subs be called or will it stop at test1? Seems like it only runs test 1. Im guessing I should change the code and put test 1 and test 2 under F13.

Thanks much


private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address

Case "$F$13"
Call test1

Case "$F$8", "$F$9", "$F$13", "$E$13"
Call test 2

Case "$F$12"
Call test3



End Select
Exits:
Application.EnableEvents = True
End Sub

Bob Phillips
05-03-2010, 06:32 AM
It will stop at test1, but why would you expect otherwise, Target cannot be F13 and E13 (it could be E13:F13 but that is another matter).

gimli
05-03-2010, 06:48 AM
Ok...so this seems to work. F13 now calls both subs and they run.


Dont understand what you mean here

"Target cannot be F13 and E13 (it could be E13:F13 but that is another matter). "


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address

Case "$F$13"
Call test1
Call test2

Case "$F$8", "$F$9", "$E$13"
Call test2

Case "$F$12"
Call test3




End Select
Exits:
Application.EnableEvents = True
End Sub

lynnnow
05-03-2010, 06:55 AM
Dont understand what you mean here

"Target cannot be F13 and E13 (it could be E13:F13 but that is another matter). "



It means if you want a particular code to run on a selection of more than one cell it will have to be E13:F13. F13 and E13 will be two separate cell selections.

gimli
05-03-2010, 07:26 AM
ok thanks

Bob Phillips
05-03-2010, 11:25 AM
Ok...so this seems to work. F13 now calls both subs and they run.


Dont understand what you mean here

"Target cannot be F13 and E13 (it could be E13:F13 but that is another matter). "



What I mean is that you can select a single cell, in which case Target.Address is that single cell, such as $F$13, or you can select multiple cells, such as E13 and F13, in which case Target.Address is $E$13:$F$13, or multiple cells such as E13 and G9, in which case Target.Address is $E$13,$G$9. You have to allow for all cases, or else reject multiple selections.

gimli
05-03-2010, 11:34 AM
Ok thanks for reply. :hi: