Keep contents of cell when clicking on it

[Karissa]

I am trying to figure out how to allow for editing a cell as soon as you
click on it ( so you don't have to click on it twice) I have a form that has
a cell made for comments and people keep complaining that every time they
click on the cell and start typing and all their previous string gets erased.
Is there a way to not have to click on it twice to go into edit mode in a
cell?

thanks for any help!

 

[Gary''s Student]

Here is an example for the single cell F7. Put this event macro in the
worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("F7")) Is Nothing Then Exit Sub
Application.SendKeys "{F2}"
DoEvents
End Sub

The code automates typeing the F2 key.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Karissa]

Beautiful! thank you very much.

Also, by chance is there a way that when you are in that particular cell
when you hit enter it makes another line in that same cell, so you don't have
to hit ALT+Enter?

 

[Gary''s Student]

You could put that functionality in a Form, but it would require much more
programming.

Sorry.

 

[Karissa]

That's fine thanks... I go have another issue though. How do I do that same
macro with multiple cells. I tried to just copy and paste it again and change
the initial name and the cell reference but it only effects the first one.

Thank you for your help!

 

[Gary''s Student]

You can have only one copy of the macro.

We can adjust the code to accomodate any number of cells:

replace the:
Range("F7")
with:
Range("A:A")
to make the macro work with any cell in column A

use:
Range("B9:B100")
for cells B9 thru B100, etc.

 

[Karissa]

thank you very helpful
--
Karissa

You can have only one copy of the macro.

We can adjust the code to accomodate any number of cells:

replace the:
Range("F7")
with:
Range("A:A")
to make the macro work with any cell in column A

use:
Range("B9:B100")
for cells B9 thru B100, etc.

 

[Billy Liddel]

Here is Gary's macro rewritten to add the line feed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sComment As String
Set Target = Range("C3:C20")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

If Len(ActiveCell) = 0 Then
Application.SendKeys "{F2}"
Else
sComment = ActiveCell.Text
ActiveCell = sComment & vbLf
Application.SendKeys "{F2}"
End If
DoEvents

End Sub


The only problem with this is that if the user enters the cell by mistake
then another line is added automatically. For this reason you may prefer the
code in a Before_RightClick procedure as below

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim sComment As String

Set Target = Range("C3:C20")
If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

If Len(ActiveCell) = 0 Then
Application.SendKeys "{F2}"
Else
sComment = ActiveCell.Text
ActiveCell = sComment & vbLf
Application.SendKeys "{F2}"
End If

End Sub


Regards
Peter