Get rid of need for F2.Select

[Howard]

The code I wrote here works fine except if I don't have the Range("F2").Select in the event sub the double-click leaves F1 in the edit mode.

I can live with that but the range.select haunts me and I'd really prefer that
F1 became the target cell after the code runs, but NOT in the edit mode.

Thanks.
Howard

(Off topic... I notice a bunch of words are underscored in red. Is this part of the new forum upgrade?)

Option Explicit

Sub MsgBoxNotes()
Dim d As String, e As String, f As String
d = Range("A1")
e = Range("A2")
f = Range("A3")
MsgBox d & vbCr & vbCr & e & vbCr & vbCr & f
End Sub

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

If Target = Range("F1") 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, Range("F1").Offset(1, 0).Column).End(xlUp) _
.Offset(1, 0) = MyIntl
Cells(Rows.count, Range("F1").Offset(1, 1).Column).End(xlUp) _
.Offset(1, 0) = Format(Now(), "dd/MM/yyy hh:mm AMPM")
Range("F2").Select
MsgBoxNotes
End If
End If

End Sub

 

[Claus Busch]

Hi Howard,

Am Fri, 3 May 2013 23:52:40 -0700 (PDT) schrieb Howard:

I can live with that but the range.select haunts me and I'd really prefer that
F1 became the target cell after the code runs, but NOT in the edit mode.

write: Cancel=True in your code

Else
Cells(Rows.count, Range("F1").Offset(1, 0).Column).End(xlUp) _
.Offset(1, 0) = MyIntl
Cells(Rows.count, Range("F1").Offset(1, 1).Column).End(xlUp) _
.Offset(1, 0) = Format(Now(), "dd/MM/yyy hh:mm AMPM")
Range("F2").Select
MsgBoxNotes
End If
End If

What should the Else-Part do? Initials in the first empty row in F and
in the correponding cell in G the date? Then try:

Else
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = MyIntl
Cells(Rows.Count, "F").End(xlUp) _
.Offset(0, 1) = Format(Now(), "dd/MM/yyy hh:mm AMPM")
MsgBoxNotes
End If
End If
Cancel = True


Regards
Claus Busch

 

[Claus Busch]

Hi Howard,

Am Sat, 4 May 2013 09:11:34 +0200 schrieb Claus Busch:

write: Cancel=True in your code

write it in the IF-Statemant for the target. Then you can go to edit
mode in all other cells except F1:
If Target = Range("F1") Then
MyIntl = InputBox("Enter your Initials :", "Read Notes.")
Cancel = True


Regards
Claus Busch

 

[Howard]

What should the Else-Part do? Initials in the first empty row in F and

in the correponding cell in G the date? Then try:

Yes, (and the time) and your code solved everything. So smooth again.

Thanks, Claus.

Howard