select case syntax to other sheets

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Excel 2010

Sheet 1 B1 has a drop down with Don, Kim, Bob & " ".

What is the proper syntax to get Case Is = "Kim" & Case Is = "Bob" to work properly. Case Is = "Don" works but probably because it is on sheet1.

Thanks.
Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim TheDon As Range, TheKim As Range, TheBob As Range
Set TheDon = Sheets("sheet1").Range("C1:D10")
Set TheKim = Sheets("sheet2").Range("E1:F10")
Set TheBob = Sheets("sheet3").Range("G1:H10")

Select Case ActiveCell.Value

Case Is = "Don"
TheDon.Select
MsgBox "Don's stuff"

Case Is = "Kim"
TheKim.Select
MsgBox "Kim's stuff"

Case Is = "Bob"
TheBob.Select
MsgBox "Bob's stuff"

Case Is = " "
MsgBox "Blank (space) stuff"
End Select
End Sub
 
Your code logic is based on ActiveCell, which is always going to be on
ActiveSheet. If you want to test the value of other cells on other
sheets you'll need to activate those sheets respectively, OR use a
fully qualified reference to them.

In your case a Select Case construct is *not* a good approach. Perhaps
if you better explain what it is you're trying to do people here will
be better able to offer help!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Howard,

Am Thu, 18 Oct 2012 11:49:49 -0700 (PDT) schrieb Howard:
Excel 2010

Sheet 1 B1 has a drop down with Don, Kim, Bob & " ".

What is the proper syntax to get Case Is = "Kim" & Case Is = "Bob" to work properly. Case Is = "Don" works but probably because it is on sheet1.
Click to expand...

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheDon As Range, TheKim As Range, TheBob As Range

If Target.Address <> "$B$1" Then Exit Sub

ActiveWorkbook.Names.Add Name:="TheDon", _
RefersTo:=Sheets("sheet1").Range("C1:D10")
ActiveWorkbook.Names.Add Name:="TheKim", _
RefersTo:=Sheets("sheet2").Range("E1:F10")
ActiveWorkbook.Names.Add Name:="TheBob", _
RefersTo:=Sheets("sheet3").Range("G1:H10")

Select Case ActiveCell.Value
Case Is = "Don"
Application.Goto "TheDon"
MsgBox "Don's stuff"
Case Is = "Kim"
Application.Goto "TheKim"
MsgBox "Kim's stuff"
Case Is = "Bob"
Application.Goto "TheBob"
MsgBox "Bob's stuff"
Case Is = " "
MsgBox "Blank (space) stuff"
End Select
End Sub


Regards
Claus Busch
 
Excel 2010



Sheet 1 B1 has a drop down with Don, Kim, Bob & " ".



What is the proper syntax to get Case Is = "Kim" & Case Is = "Bob" to work properly. Case Is = "Don" works but probably because it is on sheet1.



Thanks.

Regards,

Howard



Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

Dim TheDon As Range, TheKim As Range, TheBob As Range

Set TheDon = Sheets("sheet1").Range("C1:D10")

Set TheKim = Sheets("sheet2").Range("E1:F10")

Set TheBob = Sheets("sheet3").Range("G1:H10")



Select Case ActiveCell.Value



Case Is = "Don"

TheDon.Select

MsgBox "Don's stuff"



Case Is = "Kim"

TheKim.Select

MsgBox "Kim's stuff"



Case Is = "Bob"

TheBob.Select

MsgBox "Bob's stuff"



Case Is = " "

MsgBox "Blank (space) stuff"

End Select

End Sub
Click to expand...


Garry and Claus,

I began by trying to develop a reply to a post in MISC. However, I got bogged down in this select case attempt. I'm not sure if it will satisfy the poster in MISC but I will pass it on with credit to Claus. It does what I was trying to accomplish.

Regards,
Howard
 
Hi Howard,



Am Thu, 18 Oct 2012 21:12:34 +0200 schrieb Claus Busch:






change to:

Select Case Target.Value



Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Will do and thanks a ton. I archived your example for future ref.

See MISC post titled "Question regarding an auto-search facility" by D4WNO posted today. I passed your code on to that post, and will follow up with the small change you suggested.

Regards,
Howard
 
Back
Top