?
Solved

Trigger vba from click on cell

Posted on 2012-08-14
7
Medium Priority
?
468 Views
Last Modified: 2012-08-15
I would like to get excel to get some data if i click on a particular cell without disturbing the contents of the cell is that possible?
0
Comment
Question by:PeterBaileyUk
7 Comments
 
LVL 9

Assisted Solution

by:McOz
McOz earned 664 total points
ID: 38292967
Yes, you can trigger VBA from clicking on a certain cell using the Worksheet_SelectionChange event.

Paste the example below into the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        MsgBox "You clicked in A1"
    End If
    
End Sub

Open in new window

0
 
LVL 3

Assisted Solution

by:Hermani
Hermani earned 668 total points
ID: 38292975
not really a vba guy , but this seems to work to trigger vba when selecting cell : E4.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If (Application.Selection.Count = 1 And ActiveCell.Row = 4 And ActiveCell.Column = 5) Then
    MsgBox ("Okido")
 End If

Open in new window


Contents is not changed , and you could always lock the cell
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 668 total points
ID: 38293174
@Hermani: There's no need to use Selection or Activecell, and it's really not preferred, when using a change event like this, because the range is already passed with the Target range object.  You can test for it like Target.Cells.Count, Target.Row, Target.Column, Target.Address, etc.

@Peter:  Just as a side note, the code supplied is as best you're gonna get as far as functionality-wise.  Where this code will not work, just so you're aware, is if the currently selected cell is the cell in question.  In other words if you click the active cell, the code won't fire until the selection actually changes, then changes back.  (Technically it would fire twice there.)

HTH

Regards,
Zack Barresse
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:PeterBaileyUk
ID: 38294982
will I be able to have this in a range of cells:

c16:m26


i have a number of ranges but could create a sub to do this for each range.

dependant on the cell reference a different action will be taken
0
 

Author Comment

by:PeterBaileyUk
ID: 38295070
Ok I got someway towards where I want to be:

Can this be done with a select case rather than ifs?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    If Not Intersect(Target, Range("abimatchrange")) Is Nothing Then
    
        MsgBox Target.Address & " abi."

        'include code here.
    Else
      If Not Intersect(Target, Range("capmatchrange")) Is Nothing Then
    
        MsgBox Target.Address & " Cap."

        'include code here.
    Else
    
'        MsgBox Target.Address & " is NOT in MyDefinedRange."

        'include code here.
    End If
'        MsgBox Target.Address & " is NOT in MyDefinedRange."

        'include code here.
    End If
End Sub

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38295192
I have split the codes for fairness.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 38296967
Yup, that's how I'd do it.  You can put the range as a constant and check against it in your code, like this example...

Option Explicit

Const sRangeCheck As String = "C16:M26"

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    If Intersect(Target, Me.Range(sRangeCheck)) Is Nothing Then
        'selected cell is not in the range specified
        'do stuff here accordingly
    End If
    
    If Not Intersect(Target, Range("abimatchrange")) Is Nothing Then

        MsgBox Target.Address & " abi."

        'include code here.
        
    ElseIf Not Intersect(Target, Range("capmatchrange")) Is Nothing Then

        MsgBox Target.Address & " Cap."

        'include code here.
        
    Else

        '        MsgBox Target.Address & " is NOT in MyDefinedRange."

        'include code here.
    End If
    
End Sub

Open in new window


HTH

Zack
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question