When cell is clicked on...

Munti

New Member
Joined
Jul 20, 2008
Messages
8
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?? :confused:
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
Rich (BB code):
<o:p></o:p>
Rich (BB code):
Rich (BB code):
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

New Member
Joined
Jul 20, 2008
Messages
8
Ok I'm dumb so please help me out here, I've copied your code, now where do I paste that code into?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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)
Code:
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
 
Last edited:

Munti

New Member
Joined
Jul 20, 2008
Messages
8

ADVERTISEMENT

That's it! Thanks a lot Mick!
 

Munti

New Member
Joined
Jul 20, 2008
Messages
8
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

New Member
Joined
Jul 20, 2008
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Right click the worksheet tab, select view code. Paste this into the screen that pops up:
Rich (BB code):
Rich (BB code):
Rich (BB code):
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:p> </o:p>
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Use a BeforedoubleClick event!! Something like this which will toggle the cells in Column L, M & N
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,652
Messages
5,597,373
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top