Can't edit cell by double clicking.

[StrawS]

Hello,

After following the instructions at http://www.contextures.com/xlDataVal10.html I can't edit any cell on my worksheet by double-clicking on it (except those with data-validation).

The link I mentioned is commonly referred to for the issue it fixes, but I have searched this board for an hour and haven't found any answers to its side effect.

Does anyone have an answer?

Thanks for your time
Straws

 

[Macropheliac]

I'm not sure what you're trying to do, but this line is preventing you from editing by double-clicking:

Cancel=True

Maybe you can work around it.

Mac

 

[StrawS]

cool! you're onto it.
Is there any way to make that line run only if the cell that has been double-clicked has data validation?

And if there is, will it screw up the rest of the code?

 

[Tom Urtis]

Is there any way to make that line run only if the cell that has been double-clicked has data validation?

And if there is, will it screw up the rest of the code?

Since you have not posted any of your code there is no way for anyone to know what will and will not be "screwed up".

This in your sheet module will allow cell editing upon double-click for cells that do not have data validation, meaning that a cell with data validation will be recognized and the codeline
Cancel = True
will be applied as you seem to have requested:




Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Target.Validation.Type <> 0 Then
If Err.Number <> 0 Then
Err.Clear
Else
Cancel = True
End If
End If
End Sub

 

[StrawS]

Thank you Tom,
when I said

will it screw up the rest of the code?

I was referring to the code I talked about in my first post:
http://www.contextures.com/xlDataVal10.html

With that information, do you know whether it will conflict with the existing code?
Also, where in the double-click part of my sheet module should I put your code? since the code at that link is also in the double-click section.

Thanks for your help
Mark

 

[Tom Urtis]

If you are asking how to modify Deborah's code on that link such that double-clicking a cell that has a certain type of data validation (List type in her code) would not lead to Edit mode for that cell, but would lead to Edit mode for all other cells, including cells with data validation that is non List type, this would do that:



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

Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then

Cancel = True

'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

 

[StrawS]

Hi Tom,
Thanks for your code.

Unfortunately though, now the code in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

is hanging on this line:

Set cboTemp = ws.OLEObjects("TempCombo")

with this error:

Run-time error '1004': Method 'OLEObjects' of object '_Worksheet' failed

Since I am a novice to VBA, I don't really understand the code, let alone the possible reasons for this error.
So any help you can give would be great.
Thanks!

Straws

 

[StrawS]

Not to worry, I've found the answer to that glitch.

So. Problem solved.

Thanks Tom!