Hi Dan -
VBA does refer to ‘Target As Range’ because it is a range, but by default
rules of syntax for the BeforeDoubleClick procedure, it converts Target to
it’s value using ByVal. The conversion imparts a measure of protection
against unintentionally modifiying Target’s cell contents in the procedure.
You can’t modify the cells contents simply referring to ‘Target’. It must be
done explicitly somehow, ensuring that changes are intentional.
Modifying any component of the argument list results in an error. This is
the reason Nick stressed the technique of ‘letting excel create the routine
stubs.’ The VB editor sets the ground rules for the procedure by
automatically providing a syntax ‘boilerplate’; violating the boilerplate
ground rules results in errors.
That being said, once created, the procedure can be enhanced and copied as
long as you don’t edit the ‘boilerplate’ syntax. Below is a stab at your
latest specifications. Copy it to the proper worksheet module and modify the
range in the third line…
------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Set containmentRng = Range("A1:A79") ' <==enter your sepcific range here.
Set isect = Application.Intersect(Cells(Target.Row, Target.Column),
containmentRng)
If isect Is Nothing Then
MsgBox "Double-clicked cell IS NOT inside containment range."
Else
MsgBox "Double-clicked cell IS inside containment range."
End If
End Sub
------------------------
--
Jay
"Dan" wrote:
> Another thing - in the syntax for the function, Target is supposed to be a
> range. However, Target contains the value of the cell that was dbl-clicked.
> Not the range as I expected.
> I need to determine the cell name that was dbl clicked, then determine if
> the cell is inside a specific range or not.
>
> Why is Target the cell value, not it's range?
>
> Thanks -
>
> "Dan" wrote:
>
> > Thanks Nick -
> > My top left combo box doesn't contain anything other than "(General)", and
> > the right one has all of my subs listed, but nothing else.
> > How do I get these combo boxes to give me other options?
> > Thanks!
> >
> > "NickHK" wrote:
> >
> > > Dan,
> > > You cannot make up events and expect Excel to fire them. It is best to let
> > > Excel create the routine stubs for you to ensure they are correct.
> > > On the worksheet module, select Worksheet from the top-left combo box then
> > > select the required event from the top-right combo box.
> > > See the difference:
> > >
> > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > > Boolean)
> > > Cancel = True
> > > MsgBox "Range= " & Target
> > > End Sub
> > >
> > > Also, I avoid using default properties. Be explicit.
> > > It looks like you want
> > > Target.Address
> > > but you are getting
> > > Target.Value
> > >
> > > NickHK
> > >
> > > "Dan" <(E-Mail Removed)> wrote in message
> > > news:61D6680D-D0DA-46CD-A3FE-(E-Mail Removed)...
> > > > I have this code in my project, and double clicking on the sheet doesn't
> > > > appear to do anything:
> > > >
> > > > Public Sub BeforeDoubleClick(ByVal SelRange As Range, Cancel As Boolean)
> > > > Cancel = True
> > > > msgbox "Range= " & SelRange
> > > > End Sub
> > > >
> > > > What am I doing wrong?
> > > >
> > > > Thanks -
> > > > Dan
> > >
> > >
> > >
|
Similar Threads
