Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Run different code for different ranges using BeforeDoubleClick event

  1. #1
    Join Date
    10th July 2010
    Posts
    2

    Run different code for different ranges using BeforeDoubleClick event

    I would like to have the doubleclick code do two separate things in different cell ranges. The code that I am currently using works great but I would like to be able to doubleclick in a different cell and have excel insert "$35" instead of the check mark. Any help on this would be great. Here is the current code that I am using:


    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
         
         'Change this address to the area where you want the click to produce a checkmark
        Const sCheckAddress As String = "l2:l500" 
         
        Dim rngIntersect As Range 
         
        On Error Resume Next 
        Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target) 
        On Error Goto 0 
         
        If Not (rngIntersect Is Nothing) Then 
            Target.Font.Name = "Marlett" 
            Target.Value = "a" 
        End If 
         
    End Sub 
    
    
    Last edited by AAE; July 10th, 2010 at 02:32. Reason: Amend thread title, add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Run different code for different ranges using BeforeDoubleClick event

    dhuston99,

    Welcome to the Ozgrid community.

    Please take the time to read and understand the Forum Rules, especially about the use of code tags. All VBA code must be wrapped in code tags, which you omitted. I've added the tags for you.

    I would like to be able to doubleclick in a different cell and have excel insert "$35" instead of the check mark
    Do you want us to take a guess at which "different cell" or do you have a specific cell/range in mind?
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    10th July 2010
    Posts
    2

    Re: Run different code for different ranges using BeforeDoubleClick event

    the new cell range would be e2:e500

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,413

    Re: Run different code for different ranges using BeforeDoubleClick event

    Something like this should work.

    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) 
        Dim rng1 As Range, rng2 As Range 
        Dim Value1 As Variant, Value2 As Variant 
        Dim Font1 As Variant, Font2 As Variant 
        Set rng1 = Range("12:1500"): Value1 = "a": Font1 = "Marlett" 
        Set rng2 = Range("E2:E500"): Value2 = "$12": Font2 = Target.Font.Name 
         
        If Not Application.Intersect(Target, rng1) Is Nothing Then 
            With Target 
                .Font.Name = Font1 
                .Value = Value1 
            End With 
            Cancel = True 
        End If 
         
        If Not Application.Intersect(Target, rng2) Is Nothing Then 
            With Target 
                .Font.Name = Font2 
                .Value = Value2 
            End With 
            Cancel = True 
        End If 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Msgbox Doubleclick Resume Error Handling
    By Arthur494 in forum EXCEL HELP
    Replies: 9
    Last Post: February 1st, 2007, 05:56
  2. Copying Rows Using Target in DoubleClick Macros
    By jamesparker_1 in forum EXCEL HELP
    Replies: 2
    Last Post: September 13th, 2006, 21:35
  3. Replies: 2
    Last Post: June 30th, 2005, 18:12
  4. DoubleClick Event Error: Type Mismatch
    By socha in forum EXCEL HELP
    Replies: 2
    Last Post: January 25th, 2005, 13:02
  5. [Solved] VBA: cell doubleclick
    By hvgjr in forum EXCEL HELP
    Replies: 2
    Last Post: June 10th, 2003, 04:42

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno