You are being a litle too creative. There should be no sub test().
After you hve right-clicked and selected View Code, erase EVERYTHING you see.
paste in the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("Z100")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call celltoast
Application.EnableEvents = True
End Sub
Please note that "Worksheet_SelectionChange" is a special name. You can't
change it to test.
Then close the VBA window.
Re-open VBA and then make sure YOUR sub celltoast is in the standard module.
--
Gary''s Student - gsnu200738
"geebee" wrote:
> ok. i now have the following... i right clicked on the sheet name and
> selected "view code". then typed the following:
>
> sub test(byval as target as integer)
>
> if intersect(target, range("a4")) is nothing then
> exit sub
> end if
> application.enableevents = false
> call celltoast
> application.enableevents = true
>
> sub celltoast()
> range("c2").select
> end sub
>
> nothing is happening. am i missgin something?
>
> thanks in advance,
> geebee
>
>
> "Gary''s Student" wrote:
>
> > My sub should go in the worksheet code area, not the ThisWorkbook code area.
> >
> > 1. Erase my code from ThisWorkbook area
> > 2. From Excel, right-click the tab at the bottom of the window and select
> > View Code
> > 3. You should now be in the worksheet code area
> > 4. Paste my stuff in
> >
> > By the way, make sure your code is in a standard module and that it is not
> > Private.
> >
> > --
> > Gary''s Student - gsnu200738
> >
> >
> > "geebee" wrote:
> >
> > > its not working. nothing is hapening. i made sur that the code is in the
> > > ThisWorkBook section instead of a separate module. And I havea sub called
> > > celltoast(). not a macro, but a sub. i even added a messagebox to the code
> > > you gave me, but im not getting a messagebox to appear. what am i doing
> > > wrong?
> > >
> > > thanks in advance,
> > > geebee
> > >
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > It is possible. Let's say your macro celltoast is in a standard module. Say
> > > > we want the macro called whenever cell Z100 is clicked.
> > > >
> > > > In the worksheet code area enter:
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > If Intersect(Target, Range("Z100")) Is Nothing Then
> > > > Exit Sub
> > > > End If
> > > > Application.EnableEvents = False
> > > > Call celltoast
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > > REMEMBER: the worksheet code area, not a standard module
> > > > --
> > > > Gary''s Student - gsnu200737
> > > >
> > > >
> > > > "geebee" wrote:
> > > >
> > > > >
> > > > > hi,
> > > > >
> > > > > i would like to know how i can activate a VBA sub celltoast() when i click a
> > > > > cell in MS excel. is this possible? if so, how?
> > > > >
> > > > > thanks in advance,
> > > > > geebee
|
Similar Threads
