1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run code on click of cell

Discussion in 'Business Applications' started by CrazyCoffee, Jan 24, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. CrazyCoffee

    CrazyCoffee Thread Starter

    Joined:
    Jan 24, 2013
    Messages:
    3
    Hello,

    I am new here and to excel. I am trying to make code run by clicking on a specific cell (the same cell being populated from the code). I tried to figure out worksheet_beforedoubleclick, but the code did not run when I would initial double click the cell, only when I then clicked into a different cell. Here is what I am working with:

    Dim szPicFileName As String
    Dim rFirstRow As Long, rLastRow As Long
    Dim cFirstColumn As Integer, cLastColumn As Integer

    ' Get the filename & location of the picture
    szPicFileName = Application.GetOpenFilename()

    On Error Resume Next
    Set pic = ActiveSheet.Pictures.Insert(szPicFileName)
    On Error GoTo 0
    If Not pic Is Nothing Then 'Found it!'
    ' Set the range (change the first & last columns & rows to whatever you need)
    rFirstRow = 1
    rLastRow = 10
    cFirstColumn = 1
    cLastColumn = 10
    Set Rng = Range("C3")
    With pic
    .Height = Rng.Height
    .Width = Rng.Width
    .Left = Rng.Left
    .Top = Rng.Top
    End With
    End If


    Can anyone advise???
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Hi, welcome to the forum.
    Do you have a sample file (with the macro), it's easier to understand than just reading lines of incomplete code and not less important, you forgot to mention the version of Excel you're using.
     
  3. CrazyCoffee

    CrazyCoffee Thread Starter

    Joined:
    Jan 24, 2013
    Messages:
    3
    Ok, sorry about that, to be honest I do not fully understand all of this code, its just remnants that I found online and I miraculously got them to work... I am using Excel 2010. I have this in the Workbook space:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim myRange As Range
    Set myRange = Range("c3:c7")

    If Union(Target, myRange).Address = myRange.Address Then

    Dim szPicFileName As String
    Dim rFirstRow As Long, rLastRow As Long
    Dim cFirstColumn As Integer, cLastColumn As Integer

    ' Get the filename & location of the picture
    szPicFileName = Application.GetOpenFilename()

    On Error Resume Next
    Set pic = ActiveSheet.Pictures.Insert(szPicFileName)
    On Error GoTo 0
    If Not pic Is Nothing Then 'Found it!'
    ' Set the range (change the first & last columns & rows to whatever you need)
    rFirstRow = 1
    rLastRow = 10
    cFirstColumn = 1
    cLastColumn = 10
    Set Rng = Range("C3:C7")
    With pic
    .Height = Rng.Height
    .Width = Rng.Width
    .Left = Rng.Left
    .Top = Rng.Top
    End With
    End If

    End If

    End Sub

    It works, but I have to click in cell C3 and then nothing happens until I then click out of it. Is there any way to have it run when simply clicked?
     
  4. CrazyCoffee

    CrazyCoffee Thread Starter

    Joined:
    Jan 24, 2013
    Messages:
    3
    Oops, sorry, not in the "Workbook" but the "Worksheet"
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Which is the trigger cell?
    And, do you have the file you're usign and could you attach it so that I can see the latout?
    Replace sensitive data with dummy data, it's just to give the macro a run on your sheet.
    I still cannot see waht you want to achieve.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086625

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice