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

H

Howard

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)),"",VLOOKUP(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
 
C

Claus Busch

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
Click to expand...

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
 
C

Claus Busch

Hi Howard,

Am Sun, 5 May 2013 11:57:17 +0200 schrieb Claus Busch:
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:
Click to expand...

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
 
H

Howard

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 evokesthe 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
 
C

Claus Busch

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>
Click to expand...

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
 
H

Howard

Hi Howard,



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







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
Click to expand...

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
 
H

Howard

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
Click to expand...

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
 
C

Claus Busch

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
Click to expand...

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
 
H

Howard

Hi Howard,



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







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
Click to expand...

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
 
C

Claus Busch

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.
Click to expand...

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
 
C

Claus Busch

Hi Howard,

Am Sun, 5 May 2013 17:19:48 +0200 schrieb Claus Busch:
then try:
Click to expand...

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
 
H

Howard

Hi Howard,



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






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
Click to expand...

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
 
G

GS

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

...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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get rid of need for F2.Select 3
Trying to run excel vba code when cell value changes from empty to entered value. 1
Advice on multiple macros, same code just a different column 9
Run Macro automatically if cell =x 1
Copying Cell Conflict with Worksheet Change to UpperCase 4
specifying the column for double click 5
If sheet exists and it is named run this code 4
How do I run code when exiting a cell 2

Top