Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Run different code for different ranges using BeforeDoubleClick event

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:


    Code:
    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, 02:32. Reason: Amend thread title, add code tags

  • #2
    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

    Comment


    • #3
      Re: Run different code for different ranges using BeforeDoubleClick event

      the new cell range would be e2:e500

      Comment


      • #4
        Re: Run different code for different ranges using BeforeDoubleClick event

        Something like this should work.

        Code:
        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

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X