Activate cell with single click?

Question

All-

Is it possible to single click on a cell and have the cursor appear? Currently, you have to double click in the cell for this to happen.

My reason behind this is I have users pasting information into a cell, and sometimes it comes from the web and the formatting screws up all of the validations. I found that if they double click into the cell, that the issue is resolved and the data comes in as text and the validations are left.

Help is appreciated, thanks

Answer 1

All-

Is it possible to single click on a cell and have the cursor appear? Currently, you have to double click in the cell for this to happen.

My reason behind this is I have users pasting information into a cell, and sometimes it comes from the web and the formatting screws up all of the validations. I found that if they double click into the cell, that the issue is resolved and the data comes in as text and the validations are left.

Help is appreciated, thanks

That's an interesting idea albiet with a very specialized application.  Not to mention that it might be better to educate the consumers.  Nonetheless, it is an interesting idea and programming exercise.

The code below goes in the worksheet's code module.

The way it works is the following: If a single cell is selected, if the cell is empty, and if the clipboard contains an element with a text format, that becomes the cell's value and the clipboard is emptied.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub '''''

    If Target.Value <> "" Then Exit Sub '''''

    Dim DataObj As DataObject: Set DataObj = New DataObject

    On Error Resume Next

    With DataObj

    .GetFromClipboard

    If Err.Number <> 0 Then Exit Sub    '''''

    Dim S As String

    S = Application.WorksheetFunction.Clean(.GetText())

    If S = "" Then Exit Sub '''''

    Target.Value = S

    .SetText Empty

    .PutInClipboard

        End With

    End Sub

Answer 2

Excel goes into the Edit mode by:

1. Double-clicking (if chosen as an option)
2. Single-clicking in the formula bar
3. Pressing F2

Training your users to do one of those three things prior to pasting is the best solution.

Bernie

Answer 3

That is awesome!  Is there a way to have whatever is within the clipboard be formatted to text?  The issue is whenever something is copied from the web, it comes with all of the web formatting - I simply want it to paste as text - is that possible?

Answer 4

Neat code. Is there a way to tell if there is a table on the clipboard?

When I copy a table from the web, normal pasting with a single cell selected creates a table in Excel, which further has an option at the lower right of "Keep destination formatting" 

With your code, the table is pasted as text into a single cell.

Bernie

Answer 5

No, I don't think that is possible (and it would be enormously annoying). You should teach the users to paste special as values/plain text, or to paste into the formula bar.