When cell is clicked on...

[Munti]

Hi all!

I'm trying to create a pilot logbook for myself, so far it works ok, except I want it to work better. My question is as follows: I have 3 cells with 3 aircraft types in it, L3, M3 and N3 and at the end of my sheet, cell W3, is my total time flown for that log entry. I want to know how I can have excell either ask me if the total time has to go in L3,M3 or N3, or if I can just click on either L3,M3 or N3 and have the total time of W3 go to that particulat cell...

Anyone??

 

[Lewiy]

The following code placed in the Worksheet module should place the value in W3 into the appropriate cell when you click on it.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o:p></o:p>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)<o:p></o:p>
Select Case Target.Address<o:p></o:p>
    Case "$L$3", "$M$3", "$N$3"<o:p></o:p>
        Target.Value = Range("W3").Value<o:p></o:p>
End Select<o:p></o:p>
End Sub

 

[Munti]

Ok I'm dumb so please help me out here, I've copied your code, now where do I paste that code into?

 

[MickG]

Hi An alternative
Right Click the worksheet Tab, Click "View code" (Code window appears)
In top left Drop down, select "Worksheet".
If you dont get a code heading Worksheet_Selection Change, Select from Right Hand Drop down.
Paste code into window (Remove double header and Footer, if you've now got one)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
If Intersect(Target, Range("L3,M3,N3")) Is Nothing Then Exit Sub
    ans = MsgBox("Your Total Flying time will be entered in Range " & Target.Address, vbYesNo + vbInformation)
        If ans = vbYes Then Target.Value = Range("W3").Value
            If ans = vbNo Then Exit Sub

End Sub

This code should now run when you select one of you chosen cells.
Regards Mick

 

[Munti]

That's it! Thanks a lot Mick!

 

[Munti]

Hi all,

Stuck again.... I used Mick's code and modified it a bit, as my sheet goes from row 03 till row 43 with data:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
If Intersect(Target, Range("L3:L43,M3:M43,N3:N43,T3:T43,U3:U43,V3:V43")) Is Nothing Then Exit Sub
ans = MsgBox("Log time? ", vbYesNo + vbInformation)
If ans = vbYes Then Target.Value = Range("W3").Value
If ans = vbNo Then Exit Sub
End Sub

Now I need it to refer from W3 until W43, I've tried modifying the code like this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
If Intersect(Target, Range("L3:L43,M3:M43,N3:N43,T3:T43,U3:U43,V3:V43")) Is Nothing Then Exit Sub
ans = MsgBox("Log time? ", vbYesNo + vbInformation)
If ans = vbYes Then Target.Value = Range("W3:W43").Value
If ans = vbNo Then Exit Sub
End Sub

However it still referes to the W3 cell..... Now what??

 

[Munti]

Sorry let me explain better, what I want is if I use type L6 then it should use the data from W6, but with the code written like I did, nothing happens..it keeps referring to W3.

Anyone??Please

 

[Lewiy]

Right click the worksheet tab, select view code. Paste this into the screen that pops up:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Select Case Target.Column<o:p></o:p>
    Case 12, 13, 14<o:p></o:p>
        Target.Value = Cells(Target.Row, 23).Value<o:p></o:p>
End Select<o:p></o:p>
End Sub

<o> </o>
This will work for all rows on the worksheet, when you click on column L,M or N it will populate with Column W value from that row.

 

[lenze]

Use a BeforedoubleClick event!! Something like this which will toggle the cells in Column L, M & N

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row < 3 Then Exit Sub
Select Case Target.Column
Case 12 'Column L
     Target = Cells(Target.Row, "W")
     Cells(Target.Row, "M").ClearContents
     Cells(Target.Row, "N").ClearContents
Case 13 'Column M
     Target = Cells(Target.Row, "W")
     Cells(Target.Row, "L").ClearContents
     Cells(Target.Row, "N").ClearContents
Case 14 'Column N
     Target = Cells(Target.Row, "W")
     Cells(Target.Row, "L").ClearContents
     Cells(Target.Row, "M").ClearContents
Case Else:
End Select
Cancel = True
End Sub

HTH
lenze