Get rid of need for F2.Select

  • Thread starter Thread starter Howard
  • Start date Start date
H

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

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

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
 
Hi Howard,

Am Sat, 4 May 2013 09:11:34 +0200 schrieb Claus Busch:
write: Cancel=True in your code
Click to expand...

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

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

Thanks, Claus.

Howard
 
Back
Top