Double click fails to run the code, just selects the cell
 

If I double click any one of these cell of this line of code, the double click just selects that cell, cursor blinking and cell in the edit mode.

If Target.Address = "$G$14,$G$36,$G$58,$G$80" Then

Each of these cells contain a Vlookup formula:

=IF(ISERROR(VLOOKUP(G7,$AJ$4:$AL$43,3,0)),"",VLOOK UP(G7,$AJ$4:$AL$43,3,0))

and will return a blank cell, or one of these strings, Notes_1, Notes_2 or Notes_3

I think my "If Target.Address =" line is the culprit. I have made the code work with a single "If Range("G14").Value = "Notes_1" Then" statement.

Thanks.
Howard

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyIntl As String
Dim MyNote As String

MyNote = Target.Value

If Target.Address = "$G$14,$G$36,$G$58,$G$80" Then
MyIntl = InputBox("Enter your Initials :", "Read Notes.")
If Len(MyIntl) = 0 Then
MsgBox "Enter your initials to read notes", vbCritical
Else
Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = MyIntl
Cells(Rows.Count, "AN").End(xlUp) _
.Offset(0, 1) = MyNote & " " & Format(Now(), "dd/MM/yyy hh:mm AMPM")
If Target.Value = "Notes_1" Then MsgBoxNotes_1
If Target.Value = "Notes_2" Then MsgBoxNotes_2
If Target.Value = "Notes_3" Then MsgBoxNotes_3
End If
Cancel = True
End If

End Sub

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 02:44:28 -0700 (PDT) schrieb Howard:

If Target.Address = "$G$14,$G$36,$G$58,$G$80" Then

in an IF-Statement you have to write
If Target.address ="$G$14" or target.address.....
Put it in a Select Case-Statement. There you can write all addresses
with comma as delimiter:

Select Case Target.Address
Case "$G$14", "$G$36", "$G$58", "$G$80"
MyIntl = InputBox("Enter your Initials :", "Read Notes.")
If Len(MyIntl) = 0 Then
MsgBox "Enter your initials to read notes", vbCritical
Else
Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = MyIntl
Cells(Rows.Count, "AN").End(xlUp) _
.Offset(0, 1) = MyNote & " " & Format(Now(), "dd/MM/yyy
hh:mm AMPM")
' If Target.Value = "Notes_1" Then MsgBox Notes_1
' If Target.Value = "Notes_2" Then MsgBox Notes_2
' If Target.Value = "Notes_3" Then MsgBox Notes_3
End If
Cancel = True
End Select

If Target.Value = "Notes_1" Then MsgBox Notes_1
If Target.Value = "Notes_2" Then MsgBox Notes_2
If Target.Value = "Notes_3" Then MsgBox Notes_3

will not work for me. What is MsgBoxNotes_1??


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 11:57:17 +0200 schrieb Claus Busch:

If Target.Address = "$G$14,$G$36,$G$58,$G$80" Then

in an IF-Statement you have to write
If Target.address ="$G$14" or target.address.....
Put it in a Select Case-Statement. There you can write all addresses
with comma as delimiter:

or you write :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Intersect(Target, Range("G14,G36,G58,G80")) Is _
Nothing Then Exit Sub

So you don't have to write an IF-Statement because the code only will be
fired if the target.address is the expected address.

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

Thanks Claus,

I used this statement and works well as you know. However, if I double click on one of the cells that the formula has returned a (blank) "" it evokes the input box. Same with the select case statement. I'm guessing with the formula in the cell it is negating the "...is nothing then" portion of the statement here.

<If Intersect(Target, Range("G14,G36,G58,G80")) Is _
Nothing Then Exit Sub

Here is one of the MsgBoxNotes_1, just a macro displaying a msgbox.

Sub MsgBoxNotes_1()
Dim d As String, e As String, f As String
d = Range("AN5")
e = Range("AN6")
f = Range("AN7")
MsgBox d & vbCr & vbCr & e & vbCr & vbCr & f
End Sub

Howard

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 03:52:19 -0700 (PDT) schrieb Howard:

<If Intersect(Target, Range("G14,G36,G58,G80")) Is _
Nothing Then Exit Sub

this will only check the range for correct addresses and has nothing to
do with a formula or a value in a cell. Only if the target is one of the
cells the macro will be fired.
If the inputbox depends on a certain value of the addresses you must
check this in an IF-Statement


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

On Sunday, May 5, 2013 4:24:02 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 5 May 2013 03:52:19 -0700 (PDT) schrieb Howard:



<If Intersect(Target, Range("G14,G36,G58,G80")) Is _

Nothing Then Exit Sub



this will only check the range for correct addresses and has nothing to

do with a formula or a value in a cell. Only if the target is one of the

cells the macro will be fired.

If the inputbox depends on a certain value of the addresses you must

check this in an IF-Statement





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hmmm, that certainly makes sense. I was too hung up on the address part of it.

Thanks, I can make that work for me now that I'm clear on what my issue is.

Regards,
Howard

Double click fails to run the code, just selects the cell
 

If the inputbox depends on a certain value of the addresses you must
check this in an IF-Statement


Hmmm, that certainly makes sense. I was too hung up on the address part of it.

Thanks, I can make that work for me now that I'm clear on what my issue is.

Regards,

Howard

Perhaps I spoke to soon...!

Wouldn't this be a logical test of the target cell to exit sub if true?
I still get the cell selected with the below.

On the sheet =Len(G80) returns 0

If Len(Target.Value) = 0 Then Exit Sub

I tested it with this (the commented-out lines) and the msgbox returned 0.


'Dim i As Integer
'i = Len(Target.Value)
'MsgBox I

and tried this, did not work.

If Len(Target.Value) = 0 Then Exit Sub
Cancel = True


Howard

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 06:40:56 -0700 (PDT) schrieb Howard:

Wouldn't this be a logical test of the target cell to exit sub if true?
I still get the cell selected with the below.

On the sheet =Len(G80) returns 0

If Len(Target.Value) = 0 Then Exit Sub

Len(Target) gives you the length of the target text. If the formula in
G80 returns an empty string ("") then len = 0.

Which cells should show the inputbox when double-click and which value
should they have to show and with which value the code should go to the
Else-Part?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

On Sunday, May 5, 2013 7:01:46 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 5 May 2013 06:40:56 -0700 (PDT) schrieb Howard:



Wouldn't this be a logical test of the target cell to exit sub if true?

I still get the cell selected with the below.



On the sheet =Len(G80) returns 0



If Len(Target.Value) = 0 Then Exit Sub



Len(Target) gives you the length of the target text. If the formula in

G80 returns an empty string ("") then len = 0.



Which cells should show the inputbox when double-click and which value

should they have to show and with which value the code should go to the

Else-Part?





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Cells G14, G36, G58 and G80 should show the input box only if their value when double clicked is either of the strings Notes_1, Notes_2 or Notes_3.

If any of those cells are double clicked without one of the Notes_1, _2 _3 showing, then exit sub. (That's what I'm calling blank)

So any one of the cells could have any one of the Notes_? or be blank. Or any combination of blank's and Notes_?'s among them.

Howard

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 08:07:19 -0700 (PDT) schrieb Howard:

Cells G14, G36, G58 and G80 should show the input box only if their value when double clicked is either of the strings Notes_1, Notes_2 or Notes_3.

then try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("G14,G36,G58,G80")) Is _
Nothing Then Exit Sub

Dim MyIntl As String
Dim MyNote As String

MyNote = Target.Value
Cancel = True
If InStr(Target, "Notes") Then
MyIntl = InputBox("Enter your Initials :", "Read Notes.")
If Len(MyIntl) = 0 Then _
MsgBox "Enter your initials to read notes", vbCritical
Else
Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = Target
Cells(Rows.Count, "AN").End(xlUp) _
.Offset(0, 1) = MyNote & " " & Format(Now(), "dd/MM/yyy hh:mm AMPM")
If Target.Value = "Notes_1" Then MsgBoxNotes_1
If Target.Value = "Notes_2" Then MsgBoxNotes_2
If Target.Value = "Notes_3" Then MsgBoxNotes_3
End If

End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

Hi Howard,

Am Sun, 5 May 2013 17:19:48 +0200 schrieb Claus Busch:

then try:

sorry, I copied the wrong code. Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("G14,G36,G58,G80")) Is _
Nothing Then Exit Sub

Dim MyIntl As String
Dim MyNote As String

MyNote = Target.Value
Cancel = True
If InStr(Target, "Notes") Then
MyIntl = InputBox("Enter your Initials :", "Read Notes.")
If Len(MyIntl) = 0 Then
MsgBox "Enter your initials to read notes", vbCritical
Else
Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = Target
Cells(Rows.Count, "AN").End(xlUp) _
.Offset(0, 1) = MyNote & " " & Format(Now(), "dd/MM/yyy hh:mm
AMPM")
If Target.Value = "Notes_1" Then MsgBoxNotes_1
If Target.Value = "Notes_2" Then MsgBoxNotes_2
If Target.Value = "Notes_3" Then MsgBoxNotes_3
End If
End If
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Double click fails to run the code, just selects the cell
 

On Sunday, May 5, 2013 8:25:43 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 5 May 2013 17:19:48 +0200 schrieb Claus Busch:



then try:



sorry, I copied the wrong code. Try:



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As

Boolean)

If Intersect(Target, Range("G14,G36,G58,G80")) Is _

Nothing Then Exit Sub



Dim MyIntl As String

Dim MyNote As String



MyNote = Target.Value

Cancel = True

If InStr(Target, "Notes") Then

MyIntl = InputBox("Enter your Initials :", "Read Notes.")

If Len(MyIntl) = 0 Then

MsgBox "Enter your initials to read notes", vbCritical

Else

Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = Target

Cells(Rows.Count, "AN").End(xlUp) _

.Offset(0, 1) = MyNote & " " & Format(Now(), "dd/MM/yyy hh:mm

AMPM")

If Target.Value = "Notes_1" Then MsgBoxNotes_1

If Target.Value = "Notes_2" Then MsgBoxNotes_2

If Target.Value = "Notes_3" Then MsgBoxNotes_3

End If

End If

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Nailed it, you did. I made one change on this line and it works perfect.

Cells(Rows.Count, "AN").End(xlUp).Offset(1, 0) = MyIntl 'Target

That InStr is a pretty interesting fellow, have read some on it. Sure seems to be more in use of late. Gonna have to keep that in mind, if I can.

Many thanks.
Howard

Double click fails to run the code, just selects the cell
 

Howard,

Cells(Rows.Count, "AN").End(xlUp) (2) = MyIntl 'Target

...will also work for selecting the next blank row, obviating the need
for Offset()! So...

lNextRow = Cells(Rows.Count, "AN").End(xlUp) (2).Row

...refs the row below...

Cells(Rows.Count, "AN").End(xlUp).Row

...and this refs 2 rows below when a blank row (space) is desired...

Cells(Rows.Count, "AN").End(xlUp) (3).Row

--
Garry

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