Can't edit cell by double clicking.

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
I'm not sure what you're trying to do, but this line is preventing you from editing by double-clicking:

Code:
Cancel=True

Maybe you can work around it.

Mac
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
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

Board Regular
Joined
Jul 31, 2006
Messages
123

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
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

Board Regular
Joined
Jul 31, 2006
Messages
123
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

Board Regular
Joined
Jul 31, 2006
Messages
123
Not to worry, I've found the answer to that glitch.

So. Problem solved.

Thanks Tom!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,956
Messages
5,545,177
Members
410,668
Latest member
Gaexel
Top