Does Excel have OnClick functionality?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Jun 17, 2002
Messages
2,556
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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Joined
Jun 17, 2002
Messages
2,556
good point Rollin, I didn't even touch the keyboard when I tested it out :)
 
Joined
Jul 25, 2004
Messages
5,458
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
 

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. :)
 
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.
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top