Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Double-Click on cell

Status
Not open for further replies.

edwardpestian

Technical User
Apr 28, 2006
47
US
Is there a way to only single click on a specific cell, but have the curser appear is if you had double clicked on it. I need this function for cells E6, E6, and G6 only.

Thanks in advance.

Regards,

ep
 



?

There is no click event or double-click event on a worksheet.

There is a SclectionChange event, though.

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
Could you explain to me how I would use it to achieve the desired result?

Thanks.

ep
 


Tell me what the desired result is.

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
To have the cell, when selected, have the flashing curser in it instead of the blue outline around it.

Thanks.

ep
 


And the PURPOSE is what?

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
I have a rectangular autoshape with rounded corners around the cell. The row and column headers are also hidden. When the user selects one of the three user input cells (the only ones with the autoshape around them), they are unable to know that they have selected because they cannot see the blue bounding box.

Regards,

ep
 

"I have a rectangular autoshape with rounded corners around the cell."

No! You have a shape in the Worksheet.

When you APPEAR to click on the cell, you are selecting the shape.


Here goes...

Right click the shape and Assign Macro.

Macro record coloring the shape the way you want when its selected.

call that macro from the rectangle_click macro.

In the Worksheet_SelectionChange event, code changeing the rectangle back to the "normal" state.

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
I am actually selecting the cell and not the shape when I click on the cell.

ep
 
Each cell has a callout and an autoshape associated with it. I have the following code that hides both shapes once data is placed in the cell. I just want to take it one step further with regards to the previous posts.

thanks,

ep

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then
If Target.Value = "" Then
Shapes("AutoShape 1").Visible = msoTrue
Shapes("AutoShape 4").Visible = msoTrue
Else
Shapes("AutoShape 1").Visible = msoFalse
Shapes("AutoShape 4").Visible = msoFalse
End If
ElseIf Target.Address = "$E$6" Then
If Target.Value = "" Then
Shapes("AutoShape 2").Visible = msoTrue
Shapes("AutoShape 5").Visible = msoTrue
Else
Shapes("AutoShape 2").Visible = msoFalse
Shapes("AutoShape 5").Visible = msoFalse
End If
ElseIf Target.Address = "$G$6" Then
If Target.Value = "" Then
Shapes("AutoShape 3").Visible = msoTrue
Shapes("AutoShape 6").Visible = msoTrue
Else
Shapes("AutoShape 3").Visible = msoFalse
Shapes("AutoShape 6").Visible = msoFalse
End If
End If

End Sub
 



Well thanks for hiding you cards until now!

What else is Visible = FALSE?

How 'bout being up-front with your application and what you need!

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
My apologies Skip, I didn't mean to "hide my cards." I was simply trying to communicate my need, and didn't think that the code that I already had was pertinant to my delimma.

Nothing else is Visible = FALSE?
 


So why not a similar thing with the BeforeDoubleClick event.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Integer
    Select Case Target.Address
        Case [E5].Address
            n = 1
        Case [E6].Address
             n = 2
        Case [G6].Address
            n = 3
    End Select
    If Target.Value = "" Then
        Shapes("AutoShape " & n).Visible = msoTrue
        Shapes("AutoShape " & n + 3).Visible = msoTrue
    Else
        Shapes("AutoShape " & n).Visible = msoFalse
        Shapes("AutoShape " & n + 3).Visible = msoFalse
    End If
End Sub


Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
Entering edit mode is not easily done natively, in fact, there is no way to do this without either SendKeys (very unreliable IMHO) or API.

Here is an API call Edward ...

Code:
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _ 
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long) 
Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long 
 ' Constant declarations:
Const VK_F2 = &H71 
Const KEYEVENTF_EXTENDEDKEY = &H1 
Const KEYEVENTF_KEYUP = &H2 
Sub kk() 
    Dim keys(0 To 255) As Byte 
    Cells(1, 1).Activate 
    GetKeyboardState keys(0) 
     'Simulate Key Press
    keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0 
     'Simulate Key Release
    keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0 
End Sub

Works in Excel 2000.

HTH

-----------
Regards,
Zack Barresse
 
Zach,

is this for all cells? I only need it for cells E5, E6 and G6. Do I simple copy and paste this?

Thanks. Good to hear from you.

ep
 
Edward, put this code in a standard module ...

Code:
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long
 ' Constant declarations:
Const VK_F2 = &H71
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2

Sub EditMode(rngEdit As Range)
    Dim keys(0 To 255) As Byte
    rngEdit.Activate
    GetKeyboardState keys(0)
     'Simulate Key Press
    keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
     'Simulate Key Release
    keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0
End Sub

Put this in your worksheet module ...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("E5,E6,G6")) Is Nothing Then Exit Sub
    Call EditMode(Target)
End Sub

Voila! :)

Take care my friend.

-----------
Regards,
Zack Barresse
 
I put the first bit of code in a standard module, and the second bit of code in the worsheet module. When I click on the cell I get a runtime error 52. File not found user 32.

Any ideas.

ep
 
Do a windows search for:

user32.*

You may not have a complete installation or need to reregister the dll.

-----------
Regards,
Zack Barresse
 
Zach & EP,

Something very strange going on here. user32.dll is a core Windows component. I can't image how your system could work properly without it. This also brings up an odd compile error condition in a thread I helped you (EP) with recently thread707-1239829. I'm wondering if there aren't system issues here?


Regards,
Mike
 
A file may have been corrupted or an install not performed 100% accurately. A complete reinstall of the application may help.

-----------
Regards,
Zack Barresse
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top