No response to change_event

[Howard]

Overlooking the obvious again, I suppose.

This used to work just fine. I dug it out of my archive to adapt to another use and its a no go.

If it type in Don, Bob or Kim into B1 - Enter nothing happens. It does not add and name the three ranges and of course then it will not GoTo any of them either.

I have confidence in the code as I believe I got help with it here from Claus or Garry.

Code is in sheet 1 module.
(And tried it in a standard module also, same no-go)

I tried it a couple times with EnableEvents = True to assure that was not the problem, no help there.

Thanks.
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = True
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("C110")
ActiveWorkbook.Names.Add Name:="TheKim", _
RefersTo:=Sheets("sheet2").Range("E1:F10")
ActiveWorkbook.Names.Add Name:="TheBob", _
RefersTo:=Sheets("sheet3").Range("G1:H10")

Select Case Target.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

 

[Claus Busch]

Hi Howard,

Am Sat, 26 Oct 2013 00:47:27 -0700 (PDT) schrieb Howard:

Code is in sheet 1 module.
(And tried it in a standard module also, same no-go)

so you type the names in sheet1 B1 the code must be in the sheetmodule
of sheet1

I tried it a couple times with EnableEvents = True to assure that was not the problem, no help there.

Put this code in a standard module and run it:
Sub Test()
Application.EnableEvents = True
End Sub

Because I guess your turned the events off anyhow.
Or you enter the names in the false sheet or cell and don't fire the
event.

I tried it with typing the names and with DV with the names in B1
and it worked just fine.


Regards
Claus B.

 

[Howard]

Put this code in a standard module and run it:

Sub Test()

Application.EnableEvents = True

End Sub



Because I guess your turned the events off anyhow.

Or you enter the names in the false sheet or cell and don't fire the

event.



I tried it with typing the names and with DV with the names in B1

and it worked just fine.

That sure enough solved the problem.

I don't know what I did to disable events in the first place. I just selected the original workbook out of my archives and gave it a test run and nothing happened.

At least I'm aware of it now. This is the second time this event thing has snuck up on me.

I guess I just need to be more aware and careful.

Thanks, Claus.

Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 26 Oct 2013 02:02:28 -0700 (PDT) schrieb Howard:

I don't know what I did to disable events in the first place. I just selected the original workbook out of my archives and gave it a test run and nothing happened.

if you use EnableEvents = false you have to make sure that your macro
cannot run into an error or you have to use an errorhandler to turn
EnableEvents back to true.


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Sat, 26 Oct 2013 02:02:28 -0700 (PDT) schrieb Howard:






if you use EnableEvents = false you have to make sure that your macro

cannot run into an error or you have to use an errorhandler to turn

EnableEvents back to true.





Regards

Claus B.

Okay, that is most likely what has happened.

Thanks for the info. I guess I knew that, but its been such a long time since its happened. The brain cells are fading...

Regards,
Howard