Bangerter
asked on
onclick events in excel
is there a way to create an onclick event for a cell. what i want to do is lets say that i have a value in each of three cells a1, a2, and a3 and i want to be able to click on any of those cells and have the value in that cell populate cell c1. if someone could post some code that would be great.
that should read
Private Sub Workbook_SheetBeforeRightC lick(ByVal Sh As Object, ByVal Target As _
Excel.Range, Cancel As Boolean)
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End If
End Sub
Private Sub Workbook_SheetBeforeDouble Click(ByVa l Sh As Object, ByVal Target As _
Excel.Range, Cancel As Boolean)
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End If
End Sub
Private Sub Workbook_SheetBeforeRightC
Excel.Range, Cancel As Boolean)
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End If
End Sub
Private Sub Workbook_SheetBeforeDouble
Excel.Range, Cancel As Boolean)
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The comments above are all correct but I have encountered a problem with the Worksheet_Change and Worksheet_SelectionChange Events over the last couple of days:
1. If you have a custom (user defined) function in that worksheet and the cell value that you have changed is one that is used as an argument in that custom function, note that the custom function will be evaluated prior to the event code being executed. This is OK on the whole.
2. However, if, when changing the cell value, you don't press <Enter> to accept the new value but click on another cell instead, the custom function will start to be evaluated until it reaches a DoEvents statement or another call to one of your procedures. THEN the Events' code will run.
So, the upshot is that, if you change values of other cells in your Events and you use procedure 2 above, the values in your cells will not get updated. This is because, in effect, your Event code is being run from within the Custom function (which never allows you to change values of cells elsewhere in the workbook).
Sorry if it's a bit of a tangent to your request but I think it is a cautionary tale to tell. I hope I haven't scared you at all. Under most circumstances, this would not be a problem. In fact, until now, it hasn't been a problem for me - it only came to light last week.
FYI The way that I got around it is that the cell values I was changing in my Event code were storage areas for settings that I was using elsewhere in my code (I don't like using Static variables). So what I'm doing now is storing these settings in an INI file.
1. If you have a custom (user defined) function in that worksheet and the cell value that you have changed is one that is used as an argument in that custom function, note that the custom function will be evaluated prior to the event code being executed. This is OK on the whole.
2. However, if, when changing the cell value, you don't press <Enter> to accept the new value but click on another cell instead, the custom function will start to be evaluated until it reaches a DoEvents statement or another call to one of your procedures. THEN the Events' code will run.
So, the upshot is that, if you change values of other cells in your Events and you use procedure 2 above, the values in your cells will not get updated. This is because, in effect, your Event code is being run from within the Custom function (which never allows you to change values of cells elsewhere in the workbook).
Sorry if it's a bit of a tangent to your request but I think it is a cautionary tale to tell. I hope I haven't scared you at all. Under most circumstances, this would not be a problem. In fact, until now, it hasn't been a problem for me - it only came to light last week.
FYI The way that I got around it is that the cell values I was changing in my Event code were storage areas for settings that I was using elsewhere in my code (I don't like using Static variables). So what I'm doing now is storing these settings in an INI file.
ASKER
thanks tigerman that is exactly what i needed.
ASKER
tigerman. i am having a problem with the code you gave. at first glance it worked just fine but when i started to develop the application further i ran into some problems.
Worksheet_SelectionChange( ByVal Target As Excel.Range)
according to the documentation the variable 'Target' should contain the cell that was clicked on i.e. "D1" or "A4" or something like that. however what i have fiquered out is that the variable actually contains the value that is in the cell that is clicked on and not the cellname. how do i get the name of the cell i.e. "D1" or "A4"... to be what the variable 'Target' contains rather than the value of the cell that is clicked.
Worksheet_SelectionChange(
according to the documentation the variable 'Target' should contain the cell that was clicked on i.e. "D1" or "A4" or something like that. however what i have fiquered out is that the variable actually contains the value that is in the cell that is clicked on and not the cellname. how do i get the name of the cell i.e. "D1" or "A4"... to be what the variable 'Target' contains rather than the value of the cell that is clicked.
Bangerter,
No quite sure where you are going with this one. The following code does both:
a) pastes the value of the selected cell in C1
b) pastes the reference of the selected cell in D1
Private Sub Worksheet_SelectionChange( ByVal Target As Excel.Range)
If Target = Range("A1") Or Target = Range("A2") Or _
Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
Select Case Target
Case Range("A1")
MyCell = "A1"
Case Range("B1")
MyCell = "B1"
Case Range("C1")
MyCell = "C1"
End Select
Range("D1").Value = MyCell
End Sub
I hope you can solve your problem with that.
Dave
No quite sure where you are going with this one. The following code does both:
a) pastes the value of the selected cell in C1
b) pastes the reference of the selected cell in D1
Private Sub Worksheet_SelectionChange(
If Target = Range("A1") Or Target = Range("A2") Or _
Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
Select Case Target
Case Range("A1")
MyCell = "A1"
Case Range("B1")
MyCell = "B1"
Case Range("C1")
MyCell = "C1"
End Select
Range("D1").Value = MyCell
End Sub
I hope you can solve your problem with that.
Dave
ASKER
here is my code
Private Sub Worksheet_SelectionChange( ByVal Target As Excel.Range)
On Error GoTo handler
Select Case Target
Case Range("c4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color Index = 6
Range("d4").Interior.Color Index = 2
Range("e4").Interior.Color Index = 2
Range("f4").Interior.Color Index = 2
Case Range("d4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color Index = 2
Range("d4").Interior.Color Index = 6
Range("e4").Interior.Color Index = 2
Range("f4").Interior.Color Index = 2
Case Range("e4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color Index = 2
Range("d4").Interior.Color Index = 2
Range("e4").Interior.Color Index = 6
Range("f4").Interior.Color Index = 2
Case Range("f4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color Index = 2
Range("d4").Interior.Color Index = 2
Range("e4").Interior.Color Index = 2
Range("f4").Interior.Color Index = 6
Case Range("c8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color Index = 6
Range("d8").Interior.Color Index = 2
Range("e8").Interior.Color Index = 2
Range("f8").Interior.Color Index = 2
Case Range("d8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color Index = 2
Range("d8").Interior.Color Index = 6
Range("e8").Interior.Color Index = 2
Range("f8").Interior.Color Index = 2
Case Range("e8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color Index = 2
Range("d8").Interior.Color Index = 2
Range("e8").Interior.Color Index = 6
Range("f8").Interior.Color Index = 2
Case Range("f8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color Index = 2
Range("d8").Interior.Color Index = 2
Range("e8").Interior.Color Index = 2
Range("f8").Interior.Color Index = 6
etc.....
end select
lets say that in cell c4 i have a value of $8.00 and in cell c8 i also have a value of $8.00. when i click on c4 it turns yellow (which is colorindex 6) and the other cells on the same row turn white. that is the way it should work for all the rows. however when i click on cell c8 the code treats it as if i clicked on cell c4 and that is because they have the same values in the two cells.
i hope this makes sence. if not i can send you my speadsheet.
Private Sub Worksheet_SelectionChange(
On Error GoTo handler
Select Case Target
Case Range("c4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color
Range("d4").Interior.Color
Range("e4").Interior.Color
Range("f4").Interior.Color
Case Range("d4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color
Range("d4").Interior.Color
Range("e4").Interior.Color
Range("f4").Interior.Color
Case Range("e4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color
Range("d4").Interior.Color
Range("e4").Interior.Color
Range("f4").Interior.Color
Case Range("f4")
Range("h4").Value = Target.Value
Range("c4").Interior.Color
Range("d4").Interior.Color
Range("e4").Interior.Color
Range("f4").Interior.Color
Case Range("c8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color
Range("d8").Interior.Color
Range("e8").Interior.Color
Range("f8").Interior.Color
Case Range("d8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color
Range("d8").Interior.Color
Range("e8").Interior.Color
Range("f8").Interior.Color
Case Range("e8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color
Range("d8").Interior.Color
Range("e8").Interior.Color
Range("f8").Interior.Color
Case Range("f8")
Range("h8").Value = Target.Value
Range("c8").Interior.Color
Range("d8").Interior.Color
Range("e8").Interior.Color
Range("f8").Interior.Color
etc.....
end select
lets say that in cell c4 i have a value of $8.00 and in cell c8 i also have a value of $8.00. when i click on c4 it turns yellow (which is colorindex 6) and the other cells on the same row turn white. that is the way it should work for all the rows. however when i click on cell c8 the code treats it as if i clicked on cell c4 and that is because they have the same values in the two cells.
i hope this makes sence. if not i can send you my speadsheet.
ASKER
i got it figured out. i had to use target.row and target.column to pinpoint the cell that was being selected rather than the value in the cell.
Bangerter,
1. Sorry, I haven't checked the thread for a while - so didn't note your new comment this evening.
2. Glad to see it worked out in the end.
Regards
Dave
1. Sorry, I haven't checked the thread for a while - so didn't note your new comment this evening.
2. Glad to see it worked out in the end.
Regards
Dave
There is no way to do exactly what you want but here are 2 alternatives, the first works by double clicking either A1, A2 or A3 and the 2nd works by right clicking on them.
To put in the code that suits your needs simply right click on the sheet picture with the X on it, top left of toolbar next to "File" and select "View Code" then simply paste the code in.
Private Sub Workbook_SheetBeforeDouble
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End Sub
Private Sub Workbook_SheetBeforeRightC
If Target = Range("A1") _
Or Target = Range("A2") _
Or Target = Range("A3") Then
Range("C1").Value = Target.Value
End If
End If
End Sub
antrat