+ Reply to Thread
Results 1 to 13 of 13

VBA -- DoubleClick on Cell with Value = 1

Hybrid View

  1. #1
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    VBA -- DoubleClick on Cell with Value = 1

    I've follow this tutorial
    http://theclosetentrepreneur.com/how...click-in-excel

    Its pretty much easy to follow. Now.. I am trying to figure out how to code in Value 1 and Null every time I double click on cell. ( i.e. *double click* value = 1 appears on cell; *double click* nothing show in cell)

    I've started this code.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
     If Target Then
        Target = vbNullString
     Else
      Target = 1
     End If
        
    Cancel = True
    End Sub
    I am not get anything... No double click but a blinking crusor. What am I missing here. Please help

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1

    Try this instead

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
        
    Cancel = True
    End Sub
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2508 Win 11
    Posts
    24,937

    Re: VBA -- DoubleClick on Cell with Value = 1

    Try this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Value = 1 Then
        Target.Value = vbNullString
        Else
        Target.Value = 1
    End If
    Cancel = True
    End Sub
    EDIT: Crooza types faster than me. Nice work!
    Last edited by alansidman; 02-01-2015 at 05:34 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1


  5. #5
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: VBA -- DoubleClick on Cell with Value = 1

    Awesome! Thank you Alansidman and Crooza!

    One more thing (If you don'y mind ) I realize this string applies to every cells in worksheet. I suppose there are something I could do to get this string to focus only on target range i.e. cell B3 to B10 or Table column called "Complete"

    I have tried
     With ("B3:B10")
    or
     With("Complete")
    . I am getting No luck! Can you guys help?

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1

    This will limit the action to only cells B3:B10

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Row >= 3 And Target.Row <= 10 And Target.Column = 2 Then
    
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
    End If
    Cancel = True
    End Sub

  7. #7
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: VBA -- DoubleClick on Cell with Value = 1

    How about Tables? using this code

    Table Name: "ChkLst"
    Column Name: "Done"

    Using this code at top before the value. i.e.
    If Target.Address = "Chklst[Done]" Then
    Entire code.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Address = "Chklst[Done]" Then
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
    End If
    Cancel = True
    End Sub
    Reason of this... Table have capability of inserting row by Hitting tab button and automatically write code instead of manually add it myself.

    Let me know

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1

    It isn't working becasue it looks like you haven't set the range name for the column.

    Go to Formulas -> named ranges and create a new range called 'rangeone' and set it to B4 to B17 and it will work on column 1 only

  9. #9
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1

    You can set a range like this

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim rng As Range
    
    Set rng = Range("b3:b10")
    
    If Not Application.Intersect(Target, rng) Is Nothing Then
    
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
    End If
    Cancel = True
    End Sub
    or if you want to used a range already set up do it like this

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim rng As Range
    
    Set rng = Worksheets("sheet1").Range("rangeone")
     
    
    If Not Application.Intersect(Target, rng) Is Nothing Then
    
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
    End If
    Cancel = True
    End Sub
    Last edited by Crooza; 02-01-2015 at 07:39 PM.

  10. #10
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: VBA -- DoubleClick on Cell with Value = 1

    Yes....Yes almost getting there with this code at below. But this one applies for entire columns on table itself. Let Say if I need to only have one column such as "Column1" that allows double click with value of 1 showing up in cells. And rest of "column2", "column3" will not have double click feature, but use normal input instead. Is it possible?

    Let me guess. will this code string
    .Column("column1")
    after the
    .Range("rangeone")
    work ?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim rng As Range
    
    Set rng = Worksheets("sheet1").Range("rangeone")
     
    
    If Not Application.Intersect(Target, rng) Is Nothing Then
    
     If Target.Value = vbNullString Then
        Target.Value = 1
     Else
      Target.Value = vbNullString
     End If
    End If
    Cancel = True
    End Sub

  11. #11
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA -- DoubleClick on Cell with Value = 1

    OK I'm a bit lost now. You can either set the range that it will work in by hardcoding in the macro like I did above like this

    Set rng = Range("b3:b10")
    or

    You can have the flexibility of setting a range in your worksheet say "Rangeone" and then referencing that in your code like this

    Set rng = Worksheets("sheet1").Range("rangeone")
    so if you want to set a particular column you can either set it in the hardcode like this

    Set rng = Range("C:C")
    which will be column C

    or you can set the give the entire column a named range like "rangeone"

  12. #12
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: VBA -- DoubleClick on Cell with Value = 1

    Code like this...
    Set rng = Worksheets("sheet1").Range("rangeone")
    Only work when I set table called "rangeone" and it applies entire columns.

    When I changed Table name into Table, and named single column to "rangeone" I get an error. Just like this image below
    Error.JPGAttachment 374380

    Perhaps, to make this lot easier, there are attached .xlsm file for you to see it.

    Thanks in advance.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by HighOctane; 02-01-2015 at 10:44 PM. Reason: Forgot add attachment file.

  13. #13
    Registered User
    Join Date
    01-25-2015
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    7

    Re: VBA -- DoubleClick on Cell with Value = 1

    Oh my goodness! *face palm* How could I forget that? I am so embarrassed. Sometimes we focus on one thing and forgot the important part. Its now working I can't thank you enough for your time and patience, YOU... ARE... AWESOME.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Doubleclick cell to enter value in range
    By Templemind in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2014, 07:18 AM
  2. VBA Codes to DoubleClick a cell
    By JYY in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2013, 01:21 AM
  3. When doubleclick a cell take action
    By vin1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2011, 05:00 PM
  4. change cell colour on doubleclick
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2009, 09:29 AM
  5. What does DoubleClick on a cell do?
    By surotkin in forum Excel General
    Replies: 2
    Last Post: 06-15-2005, 08:51 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1