[spackler]

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.

 

[Ziggy1]

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.

 

[Rollin_Again]

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.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub

Regards,
Rollin

 

[Ziggy1]

good point Rollin, I didn't even touch the keyboard when I tested it out

 

[OBP]

Using Excel for an Access job?

 

[Zack Barresse]

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

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]

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.

 

[ddockstader]

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:

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.

 

[CWDENVER]

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.

 

[Zack Barresse]

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...

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.

 

[ddockstader]

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.