PDA

View Full Version : Click on a cell, which one was it?



ukmxer
09-20-2007, 12:07 PM
If I click a cell with the left mouse the name box displays the cell id.

Could anyone tell me where this is stored end what command would I use to add it in to a routine.

e.g I click on C4 I would like to get the contents of C4 and transfer them to another cell. But I can not find how to do it. That is why I would like to know how Name box knows, and then hopefully I can do the same thing in my routine.

rory
09-20-2007, 12:34 PM
If you use the Worksheet_SelectionChange event, it provides a range argument called Target that represents the selected cell(s). You can use Target.Value to get the contents. If you are running the code manually, you can just use Selection.value to get the value of the selected cells.

Bob Phillips
09-20-2007, 12:55 PM
The cell clicked on is the Activecell. You can address all of its properties through Activecell.

Zack Barresse
09-20-2007, 01:55 PM
Hello,

Maybe you could describe your process a little more here. Depending on what you are doing, maybe we could suggest an alternative method or some ways of making it easier on you. :)

ukmxer
09-20-2007, 02:00 PM
Thank you guys, I have been searching using things like "Excel cell contents", "Excel mouse click" and allsorts of other things. Got loads of info but not what I wanted.:banghead:

YOU have set me on the right road in one question, THANK YOU.

ukmxer
09-20-2007, 02:04 PM
Sorry Fireftyr, missed your comment.

I am writing a lap scorer for racing using excel and this is my first attempts with VBA so I am struggling like feck at the moment. But I would like to thank each and everyone of you who keeps offering help and support.

ukmxer
09-21-2007, 12:11 AM
Following on, I have tried to make one cell store the result of two cells selected by clicking and then added them together.

I only seem to show the cell thats active when I click on the cmdbtn I have used to run the macro.

Could anyone offer some sample code. Thank you.

Bob Phillips
09-21-2007, 01:20 AM
Show your code?

ukmxer
09-21-2007, 02:16 AM
Please be gentle I am a beginner.

Dim num1 As Integer
Dim num2 As Integer
MsgBox "Click first Cell "
num1 = ActiveCell.Value
Sheet6.Cells(10, 8) = num1
MsgBox "Click second Cell "
num2 = ActiveCell.Value
Sheet6.Cells(11, 8) = num2
Sheet6.Cells(12, 8) = Sum(H10, H11)


All I want to do is click one cell Click another and add.

Its killing me searching the net.

rory
09-21-2007, 03:43 AM
Try this:
Dim rngFirst As Range, rngSecond As Range
Set rngFirst = Application.InputBox(prompt:="Click first cell", Title:="Select 1st cell", Type:=8)
Set rngSecond = Application.InputBox(prompt:="Click second cell", Title:="Select 2nd cell", Type:=8)
With Sheet6
.Cells(10, 8).Value = rngFirst.Value
.Cells(11, 8).Value = rngSecond.Value
.Cells(12, 8).FormulaR1C1 = "=Sum(R[-2]C:R[-1]C)"
End With

ukmxer
09-21-2007, 05:33 AM
Rory, I have put this in my cmdbtn_click sub and it works for the two inputs but I am getting a 424 runtime object reqd whan I would expect to see the result.

I am sorry about this but have I put it in the wrong place.

rory
09-21-2007, 05:35 AM
On which line? Did you press OK after selecting each cell rather than Cancel?

rory
09-21-2007, 05:41 AM
This version will put 0 in the relevant cell if you don't choose a valid range:
Dim varOne, varTwo
varOne = Application.InputBox(prompt:="Click first cell", Title:="Select 1st cell", Type:=8)
varTwo = Application.InputBox(prompt:="Click second cell", Title:="Select 2nd cell", Type:=8)
With Sheet6
If varOne <> False Then
.Cells(10, 8).Value = varOne
Else
.Cells(10, 8).Value = 0
End If
If varTwo <> False Then
.Cells(11, 8).Value = varTwo
Else
.Cells(11, 8).Value = 0
End If
.Cells(12, 8).FormulaR1C1 = "=Sum(R[-2]C:R[-1]C)"
End With

ukmxer
09-21-2007, 06:16 AM
Rory, I am still having problems, could my sheet be set different to yours.

I have done it on a blank sheet and still having difficulty, so I have zipped it up.

Sorry for being a total pr**k.

rory
09-21-2007, 06:21 AM
You don't have a Sheet6! (I assumed you did since it was in your original code!)
Try this, adapting sheet names as necessary:



Dim varOne, varTwo
varOne = Application.InputBox(prompt:="Click first cell", Title:="Select 1st cell", Type:=8)
varTwo = Application.InputBox(prompt:="Click second cell", Title:="Select 2nd cell", Type:=8)
With Sheets("Sheet1")
If varOne <> False Then
.Cells(10, 8).Value = varOne
Else
.Cells(10, 8).Value = 0
End If
If varTwo <> False Then
.Cells(11, 8).Value = varTwo
Else
.Cells(11, 8).Value = 0
End If
.Cells(12, 8).FormulaR1C1 = "=Sum(R[-2]C:R[-1]C)"
End With

ukmxer
09-21-2007, 07:05 AM
Rory , Sorry for being an ass, I am doing my best to learn the basics.

Thank you for your help AND patience.

All ok now, can you suggest a book that will help me get into prgramming for excel.

rory
09-21-2007, 07:45 AM
Depends how quickly you learn! 'Dummies'-type books will get you started but I think they are a waste of money because they are pretty limited. John Walkenbach's VBA books are good (as are all his Excel books that I have read) and the Wrox Press 'Excel 200n VBA' series (there are versions for 2000, 2002, 2003 and I think 2007) are good - either the 2002 or 2000 would probably be my pick.

Bob Phillips
09-21-2007, 08:07 AM
Depends how quickly you learn! 'Dummies'-type books will get you started but I think they are a waste of money because they are pretty limited. John Walkenbach's VBA books are good (as are all his Excel books that I have read) and the Wrox Press 'Excel 200n VBA' series (there are versions for 2000, 2002, 2003 and I think 2007) are good - either the 2002 or 2000 would probably be my pick.

Not the 2003 version, that is a very poor revision, lots of errors.

RECrerar
09-21-2007, 08:08 AM
Hey, Just a bit of input on the books. I just started VBA a couple of months ago and used the Excel VBA Programming for Dummies as a starter, and found it invaluable. It is quite simple and I found I soon had questions that it couldn't answer. I now have the Excel 2002 VBA book that Rory mentioned and am finding it a very useful reference, but its not as easy to read and understand as the -For Dummies one and I think I would have been totally lost had I started with it.

I would probably recommend seeing if you can borrow/get out of the library a copy of the Dummies book and then possibly invest in one of the ones that Rory Recommended