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.

Does Excel have OnClick functionality?

Discussion in 'Business Applications' started by spackler, Jan 16, 2007.

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

    spackler Thread Starter

    Joined:
    Mar 9, 1999
    Messages:
    255
    I would like to be able to add text to a cell simply by clicking on it. I've been trying to see if there is some sort of OnClick function or code I can use but I haven't found anything.

    I have a question with 5 answers so I'd like the user to be able to choose the answer by clicking on a cell and when they clicked on the cell it would insert an X into that cell.

    I do not want to use the pulldown list or validation, I want the X to insert on clicking the cell.

    Thanks.
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    this goes into the "This workbook" section of the workbook code....


    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    ActiveCell.Value = "X"

    End Sub

    this is pretty general, so you would have to build around it as far as how you want it to work with the rest of the code you may have.
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can also use the "Before Double Click" event since using the "Selection Change" event while navigating the worksheet with the arrow keys will also cause the "X" to be displayed. As Ziggy has already indicated, this code needs to be attached to the workbook object and NOT a stand alone module. To add the code to the workbook object, open the Excel workbook and in the top left corner of the workbook (next to the file menu) is the Excel Icon. Right click this icon and choose "View Code." Then just select the Workbook object and the appropriate event which you would like your code attached from the two drop down comboboxes at the top. Unless you a very picky either event should work just fine.

    Code:
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    End Sub
    Regards,
    Rollin
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    good point Rollin, I didn't even touch the keyboard when I tested it out :)
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Using Excel for an Access job? ;)
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    A more robust solution would be to check if only one cell was selected, check the range in question and finally toggle the value depending. I do also like the double click event as opposed to a single click, I've messed up too many times :)

    Code:
    Option Explicit
    
    Const MYRANGE As String = "A1:A10"
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Me.Range(MYRANGE)) Is Nothing Then Exit Sub
        Cancel = True
        If Target.Value = "" Then Target.Value = "X" Else Target.Value = ""
    End Sub
    HTH
     
  7. spackler

    spackler Thread Starter

    Joined:
    Mar 9, 1999
    Messages:
    255
    Rollin, Ziggy, and Firefytr - thank you for the reply. The script worked based on the first ones - but it does work on any cell which is no big deal but fire I see where you were going but the script didn't work for me and I'm not familiar enough with VB to tweak it. Anyway to constrain your script to a range of cells?

    But thanks again you guys are why this is the best board on the net!

    As for OBP - I'm absolutely using Excel for a database job, thank you for noticing. :)
     
  8. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Zack,

    I tried your code (which looked to me like it should work) in a clean workbook and I couldn't get it to work. I think (and believe me, I'm just stumbling in the dark here) that you left out a parameter on SheetBeforeDoubleClick, namely ByVal sh as Object. When I put that in, then the Me.Range produced an error. I did get the following code to work:

    Code:
    Option Explicit
    Const MYRANGE As String = "A1:A10"
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Range(MYRANGE)) Is Nothing Then Exit Sub
        Cancel = True
        If Target.Value = "" Then
            Target.Value = "X"
        Else
            Target.Value = ""
        End If
    End Sub
    It works slick and I'm going to put it into my Todo list. Thanks.
     
  9. CWDENVER

    CWDENVER

    Joined:
    Dec 1, 2006
    Messages:
    38
    If you really wanted to get fancy, you could build a Userform with the OptionButton or ToggleButton. The OptionButton only allows one selection from a range of choices, and the ToggleButton will allow multiple choices from a list. You can then make the worksheet react to the choice(s) made.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    My code works for me, strange it won't work for you. Did you ensure that you copied the Const as well?

    @ddockstader: Thanks for taking another look at my code. I did not leave anything out. What you are referring to is found in the ThisWorkbook module, where the sheet is needed to be passed as a variable as it can work for every sheet in the workbook. This is beneficial if you want to run this code on more than one workbook or if you already have a before double click code in a specific worksheet that you want undisturbed.

    The difference between my code and ddockstader's code is mine goes into the worksheet module (right click the sheet tab and select View Code) and ddockstader's goes into the workbook module (right click the workbook icon directly left of the File menu and select View Code). To use the code in your ThisWorkbook module (workbook level), ddockstader's code would need two minor adjustments...

    Code:
    Option Explicit
    
    Const MYRANGE As String = "A1:A10"
    
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        If Sh.Name <> "Sheet1" Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Sh.Range(MYRANGE)) Is Nothing Then Exit Sub
        Cancel = True
        If Target.Value = "" Then
            Target.Value = "X"
        Else
            Target.Value = ""
        End If
    End Sub
    This is because it would need the sheets object to be referenced, otherwise it would run on any activesheet, which may not necessarily be desired. Note the difference is the references of the Sh object.
     
  11. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Thanks, Zack. I knew there was some reason that your code didn't work for me. Of course, I put it in the wrong place. Thanks again.
     
  12. 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/535860

  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