Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Cell click Event

Is there a way that when a cell is clicked in a worksheet. that I can get a user form to open?


Post your answer or comment

comments powered by Disqus
Hello folks.
Is there any way to call a macro with a cell click event? I would like my macro to run every time a specified cell is clicked, even if the same cell is clicked over and over.
The worksheet selection change event only fires the macro the first time a specified cell is clicked, but not on subsequent clicks to that same cell.

Thanks in advance for any help.
PS...fantastic forum! I had a responce to my last question in a matter of minutes...awesome!

Hi,
how can i capture the right mouse button click event on a cell. I'd like to
enable/disable commands of the context menu in dependence to the selected
column.

TIA
Reinhard

Hello. I am working with a large legacy file/program which has a lot of issues. Foremost, and unfortunately this cannot be changed, is that all of the controls were placed directly on the worksheets instead of on Userforms.

The actual code from the legacy file is excessively complicated, so I created this simple example. Sheet1 has one textbox and one command button. Sheet 2 is blank. I want to be able to click the command button or use the Enter key on the command button, to trigger the Click Event. Clicking works fine. When using Enter, if Sheet2.Activate is commented, everything works fine. If Sheet2.Activate is executed, then Excel crashes. Any ideas are greatly appreciated!

Thanks, Tom


	VB:
	
) 
    Dim bBackwards As Boolean 
    Select Case KeyCode 
         'Only look for tab, return, down arrow, up arrow
    Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp 
        Application.ScreenUpdating = False 
         'Do we need to go back to previous control
        bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp) 
        If bBackwards Then TextBox1.Activate Else CommandButton1.Activate 
        Application.ScreenUpdating = True 
    End Select 
End Sub 
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
    Dim bBackwards, bForwards As Boolean 
    Select Case KeyCode 
    Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp 
        Application.ScreenUpdating = False 
        bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp) 
        bForwards = (KeyCode = vbKeyTab) Or (KeyCode = vbKeyDown) 
        If bBackwards Then 
            TextBox1.Activate 
             'Since this is the last control, stay on the button
        ElseIf bForwards Then 
            CommandButton1.Activate 
        Else 
             'Only remaining possibility is Return key
            Call CommandButton1_Click 
        End If 
        Application.ScreenUpdating = True 
    End Select 
End Sub 
Private Sub CommandButton1_Click() 
    Sheet2.Cells(1, 1).Value = "test" 
    Sheet2.Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello i am strugling to find event that is triggered when cell is selected.
Can you help me?

Or maybe there is a better way to do this?
This is what i want:
When user clicks given cell form will pop up letting user choose cell value from Combo box.
I wanted to use Cell Selected event and just pop up Form but maybe i can do it withought VB.

Is there a way to limit possible cell values to few predefined values?
So user cant put there whatever he/she wants?

Thanks.

Hi All,

I was using the following code in a Worksheet Before Double-Click Event and it worked fine for the first few attempts. All it had to do was change the offset cell from 0 to -1 or -1 to 0. Now it won't function at all. Can anyone tell me what is wrong in the code please?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column 1 Then Exit Sub
Target.Offset(0, 55).Value = Not Target.Offset(0, 55).Value
End Sub#

Thank in advance for any help.

Regards,

Bill

Hello,

I am using the Microsoft Office Spreadsheet 11.0 Control in a user form. I am trying to have code run when the user clicks on a cell in the control, or whenever the user selects a different cell. I can't seem to get it to work and i'm not sure if it's because i'm using the wrong event or what. I tried using the click event, the selectionchange event, and the selectionchanging event, and none seem to work. Here is the code I am using... does anyone have any idea why this isn't working?


	VB:
	
 Spreadsheet1_Click() 
     
    MsgBox "test" 
     
    If Me.FinishBtn.Enabled = True Then 
         
        Dim varcolor 
        varcolor = Image8.BackColor 
         
        If CheckBox1 = True Then 
             
            Dim i As Long, r As Range, txt As String, CopyTo As Range 
             
            If Me.ListBox1.ListIndex = -1 Then Exit Sub 
             
            With Me.ListBox1 
                For i = 0 To .ListCount - 1 
                    If .Selected(i) Then 
                        txt = txt & .List(i, 0) 
                    End If 
                Next 
            End With 
            On Error Resume Next 
            Set CopyTo = Me.Spreadsheet1.Selection 
            If Not CopyTo Is Nothing Then 
                CopyTo.Value = txt 
            End If 
             
             
        Else 
             
            If Me.ListBox1.ListIndex = -1 Then Exit Sub 
             
            With Me.ListBox1 
                For i = 0 To .ListCount - 1 
                    If .Selected(i) Then 
                        txt = txt & .List(i, 0) 
                    End If 
                Next 
            End With 
            On Error Resume Next 
            Set CopyTo = Me.Spreadsheet1.Selection 
            If Not CopyTo Is Nothing Then 
                CopyTo.Value = txt 
                CopyTo.Interior.Color = varcolor 
            End If 
             
        End If 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all

My first post - although I have used this forum many times before to find answers.

I need to add labels to userforms programmatically, and then add '_Click()' event code to the form to fire actions when the user clicks the labels. The action is always to close the form, and select a specific cell in the excel workbook. The form and labels are used to help users identify issues with the data they have entered, and quickly go to the problem via a 'hyperlink' that is actually a label with '_Click()' event code behind it.

All is/was going well, however I am unable to get the '_Click()' event code to fire. The code is successfully written within the form, but clicking the labels does not fire it. I have tried CommandButtons and these don't work either. While I am working within frames, I have also tried adding the label directly to the userform (i.e. not in a frame) and this doesn't work either.

I have seen plenty of code online that works (have tested it, and it works) - however when I try to adapt mine I get runtime errors. The code below at least runs - it just doesn't create a 'hyperlink'.

Below is the code I have written, that is included in the attached file. It runs without errors, but clicking the created label does not fire the '_Click()' code that is added behind the UserForm:


	VB:
	
 AddHyperlink() 
     
    Dim hyperlinkButton As MSForms.Label 
    Dim ErrForm As MSForms.UserForm 
    Dim FormCodeMod As Object 
     'Frame1 is a frame within the userform
    Dim ButtonName As String 
     
     'set the form object
    Set ErrForm = UserForm1 
     
     'create the button
    Set hyperlinkButton = ErrForm.Frame1.Controls.Add("Forms.Label.1", , True) 
    With hyperlinkButton 
        .Caption = "click" 
        .Left = 10 
        .Top = 10 
        .Height = 25 
        .Width = 50 
        .Font.Underline = True 
        .ForeColor = &HFF0000 
    End With 
     
     'set the code module object
    Set FormCodeMod = ActiveWorkbook.VBProject.VBComponents("UserForm1").CodeModule 
     
     'write click event code into the code module
    ButtonName = hyperlinkButton.Name 
    With FormCodeMod 
        .InsertLines 2, "Private Sub " & ButtonName & "_Click()" 
        .InsertLines 3, "   MsgBox ""Dynamic button " & ButtonName & " clicked""" 
        .InsertLines 4, "End Sub" 
    End With 
     
     'show the form
    UserForm1.Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


...and the code below is some code I got off tinternet, which works, but it doesn't manage frames, and when I try to emulate it I get runtime errors...

I am not so hot when it comes to Objects and when/how to define them, and I suspect that this is where I have gone wrong. Can anyone help?


	VB:
	
 
Sub AddButtonAndShow() 
     
    Dim Butn As CommandButton 
    Dim Line As Long 
    Dim objForm As Object 
     
    Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1") 
     
    Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1") 
     
    With Butn 
        .Name = "CommandButton1" 
        .Caption = "Click me to get the Hello Message" 
        .Width = 100 
        .Top = 10 
    End With 
     
    With objForm.CodeModule 
        Line = .CountOfLines 
        .InsertLines Line + 1, "Sub CommandButton1_Click()" 
        .InsertLines Line + 2, "MsgBox ""Hello!""" 
        .InsertLines Line + 3, "End Sub" 
    End With 
     
    VBA.UserForms.Add(objForm.Name).Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a BeforeDoubleClick event that depending on the value of the cell clicked in column "M" will give the resulting form the correct name

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    LastRow = Range("M" & Rows.Count).End(xlUp).Row
    
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        
        myCaption = Target.Value
        If myCaption = "" Then
            myCaption = "Create"
        Else
            myCaption = "Update"
        End If
        
        frmMain.Caption Target.Value
        frmMain.Show
    End If
    
End Sub
...however column "M" is the first cell of a collection of single-row merged-cells extending from M to X. When I attempt to run the event I receive a compilation error "Invalid Use of Property" on "frmMain.Caption Target.Value" which kind of makes sense, but I have no idea on how to fix it...

Any ideas muchly appreciated.

dp

Hi,

I have two userforms. Userform1 is loaded first, there is a checkbox1 in the Userform1. When Userform1 is opened under the activate event I have the code that sets the checkbox1 = true if a cell in the spreadsheet is equal to 1 and false if it is equal to zero. So the purpose was to remember what the user input so they would not have to recheck the checkboxes.

But here is the problem. When the Userform1 is activated and the "checkbox1" is set equal to true, for some reason the click event (for the checkbox) is activated. This loads the Userform2 (because clicking on the checkbox is supposed to start Userform2).

I dont understand why setting checkbox1.value = true starts the "click" event for checkbox1. I also noticed that setting checkbox1.value = false does not start the click event for checkbox1. I guess I need to change the value of the checkbox but I dont want to start the Click event of the checkbox.

Windows XP, Excel 2003

Thanks for your help

hi, i m looking for a way to trigger certain code when a CERTAIN cell is clicked

is that even possible?

thanks.

Hello,

Can i get a mouse click Event in a Worksheet?. I want to find a single click
inside a cell is this possible?

Regards,
Srinath S

Explanation:
The source data the listbox contains is in columns A, B and C of Sheet1. I
use the rowsource property so the listbox list can have headers.
"WorkList" is a dynamically updating named range. When a selection is made
in Listbox1, the selection is moved to the top of the list on Sheet1 and the
rest of the list is sorted. Everything works great to that point, the
problem is that when I reset the RowSource property, the Listbox1 list
doesn't get updated. I assume this is because it is from Listbox1's click
event, as it works if I do the same thing from another procedure. Am I
doing something wrong, or is there possibly another way to accomplish this?

Thanks!
-Jeremy

----------------------------------------------------------------------------
Private Sub Listbox1_Click()
Dim rng As Range

If DisableFormEvents Then Exit Sub

With Worksheets("Sheet1")
Set rng = .Columns(1).Find(What:=Me.Listbox1, LookIn:=xlValues)
Range(rng, rng(1, 3)).Cut
.Range("A2").Insert Shift:=xlDown
With Range(.Range("A3"), .Cells(.Rows.Count, 26).End(xlUp))
.Sort Key1:=Range("A3"), Order1:=xlAscending,
End With
DisableFormEvents = True
Me.Listbox1.RowSource = _
.Names("WorkList").RefersToRange.Address(External: =True)
DisableFormEvents = False
End With

End Sub
----------------------------------------------------------------------------

Why can't I get this? I create a checkbox from the Forms toolbar. I want to
set up a macro that will change specified worksheet cell values when the
checkbox is clicked. Really, I want to test the checkbox value and react
accordingly. So I should be able to set up a click event macro. Doesn't sound
too hard--but I cannot get the darned thing to work.

I tried the code below from Scott's reply to the thread 8/16/2006
"***Important*** Question about check boxes". Where do I paste the code in
the VB window? I've tried within "sheet 1" and got an error "the macro cannot
be found". So I tried adding a module and putting the code there, but got a
runtime error "object required".

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = True
CheckBox4.Enabled = True
Else
CheckBox2.Enabled = True
CheckBox3.Enabled = False
CheckBox4.Enabled = False
endif
end sub

Appreciate any assistance.
Carolyn

Hello,

I would like to run a procedure whenever a cell is clicked/selected on a
spreadsheet. The standard event procedures do not seem to have one of these.
I would also like this procedure to run, even if another procedure is
currently running --- therefore stopping the first procedure and then running
the click event procedure.

Thank you for your help.

Hello All,

I have an information system that uses multiple Excel files with macros.
On many of the sheets, we have a combo box that enables the user to hide and
show certain informations.
Here is an exemple of the code behind on of the sheet and it's combo Epargne :

Private Sub Epargnes_Click()
CallByName ActiveSheet, ActiveSheet.Cells(1, 10).Value, VbMethod
End Sub

Sub ├ępargnes_tout()
Unprotect ThisWorkbook.SIGPWD
Rows("1:100").Hidden = False ' Affiche toutes les lignes
Rows("15").Hidden = True ' Masque les lignes T12
Rows("21").Hidden = True ' Masque les lignes T12
Rows("27").Hidden = True ' Masque les lignes T12
Rows("33").Hidden = True ' Masque les lignes T12
Rows("39").Hidden = True ' Masque les lignes T12
Rows("45").Hidden = True ' Masque les lignes T12
Rows("57").Hidden = True ' Masque les lignes T12
Rows("41:46").Hidden = True ' Masque les ├* d├ęterminer
Protect ThisWorkbook.SIGPWD
End Sub

Sub ├ępargnes_seul()
Unprotect ThisWorkbook.SIGPWD
Rows("1:100").Hidden = False ' Affiche toutes les lignes
Rows("15").Hidden = True ' Masque les lignes T12
Rows("21").Hidden = True ' Masque les lignes T12
Rows("27").Hidden = True ' Masque les lignes T12
Rows("33").Hidden = True ' Masque les lignes T12
Rows("39").Hidden = True ' Masque les lignes T12
Rows("45").Hidden = True ' Masque les lignes T12
Rows("57").Hidden = True ' Masque les lignes T12
Rows("47:51").Hidden = True ' Masque les comptes inactif
Rows("41:46").Hidden = True ' Masque les ├* d├ęterminer
Protect ThisWorkbook.SIGPWD
End Sub

Sub ├ępargnes_compte()
Unprotect ThisWorkbook.SIGPWD
Rows("1:100").Hidden = False ' Affiche toutes les lignes
Rows("11:46").Hidden = True ' Masque toutes les lignes ├ępargnes
Rows("52:63").Hidden = True ' Masque la fin du rapport
Rows("29:46").Hidden = True ' Masque les ├* d├ęterminer
Protect ThisWorkbook.SIGPWD
End Sub

On excel's version prior to 2003, everything works find. The click event is
fired once when I chose an option of the combo and it then calls the
appropriate function.

On Excel 2003, the following error message occurs when I make a combo choice :

Run-Time error '1004' : Application-defined or object-defined error

and if I check the err.description, I get : Unable to set the Hidden
property of the range class.

The subs works well indepedently if I comment the event. On the debugger,
the difference between Excel 2003 and the other versions is that the event is
triggered multiple times in excel 2003 and only one time in the other
versions!

A simple solution is to use a boolean to know when to call the function
(only once) but we have over a 100 sheets similar at this one so that would
imply a lot of work. I'm currently trying to help someone who's installing
this in Madagascar... Is there anything else to do?

Thanks for your help

Richard

I'm trying to set up a bit of code using target.value using the double click event, so when I double click on a cell on sheet 1, the contents of the cell offsets one cell to the right.

Do you know how I would do this?

Thanks
Harry

Why can't I get this? I create a checkbox from the Forms toolbar. I want to
set up a macro that will change specified worksheet cell values when the
checkbox is clicked. Really, I want to test the checkbox value and react
accordingly. So I should be able to set up a click event macro. Doesn't sound
too hard--but I cannot get the darned thing to work.

I tried the code below from Scott's reply to the thread 8/16/2006
"***Important*** Question about check boxes". Where do I paste the code in
the VB window? I've tried within "sheet 1" and got an error "the macro cannot
be found". So I tried adding a module and putting the code there, but got a
runtime error "object required".

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = True
CheckBox4.Enabled = True
Else
CheckBox2.Enabled = True
CheckBox3.Enabled = False
CheckBox4.Enabled = False
endif
end sub

Appreciate any assistance.
Carolyn

I have a userform on which there is a list box. When the user clicks an item in a multiselect list box I want a total to appear in a textbox on the same form. The problem is that the listbox click event isn't firing. I have a breakpoint set at the click even and it never breaks there.

How is the click event supposed to work?

'The user form
is frmEntry.  There are several fields on the form, 1 listbox
'a textbox for totals and a couple other controls.  The listbox is programatically
'filled when the user clicks a choice in a combobox.

Private Sub UserForm_Initialize()

    With cboAgeGroup
        .AddItem "SJ"
        .AddItem "Jr."
        .AddItem "Sr."
        .AddItem "Adult"
    End With
    cboAgeGroup.Value = ""
End Sub

Private Sub cboAgeGroup_Change()
Dim rng As Range
    Application.ScreenUpdating = False
    Worksheets("Classes").Activate
    Range("$A$1:$B$32").AutoFilter Field:=2, Criteria1:="=All", _
        Operator:=xlOr, Criteria2:="=" & cboAgeGroup.Value
    Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    For Each c In rng.Rows
        Me.lstClasses.AddItem c.Cells(1, rng.Row).Value
    Next
    Application.ScreenUpdating = True
End Sub

Private Sub lstClasses_Click()
    Static clsCount As Integer
    clsCount = clsCount + 1
    txtClassChg.Value = clsCount * 10
End Sub


Hi,

I'm looking for a little help to do the following. Any info is greatly appreciated.

I'm trying to write a macro in XL that when run, will first pop up a combo box. The combo box needs to be filled by a range of cell values on the fly. Secondly, once the combo box is visible, I need to fire the click event of this combo box. Lastly, the combo box will be removed.

So, I need to:
--Have the user run a macro.
--Macro adds a combo box dialogue and fills with a range of cells on the active sheet.
--Once a selection is made in the combo box an event will be fired.
--Delete the combo box.

Thanks for the help.

Cheers,
Ed

I have a combo box that I have created.

When one of my users pulls the drop down to place an item into a cell, how
do I tell the Click Event to move the person's cursor to column W (or C23)
before placing the item they chose?

Here is my current vb code:

Private Sub ComboBoxForemen_Click()
ActiveCell.Value = ComboBoxForemen.Value
ComboBoxForemen.Value = "Foremen List"
ActiveCell.Select
ActiveCell.Offset(1, 0).Select
End Sub

Thanks so much. Tofer

Hi...

I have been given some advice to use a double click event to change my cells
contents which works brilliantly.

Is there a way to get another double click event to work for some specific
cells in the same sheet's code? Such as the inserting the day's date into a
cell in the top row when I double click? ... Whilst ensuring the remaining
cells do another double click event??

Thanks.

Hello. Is there a way to essentially do a cell activate event? WhatI'm
trying to do is have a Sub fire if and when a user clicks on any cell within
column F. So for instance, if the user clicks on F5, then Sub Run_Main
executes. Thanks!

Hello,

Hopefully someone can help me out here.

I am looking to use several different double click events on my spreadsheet, but I cant seem to pin it to one column.

What I need to do is, if I double click in column A, then x code, if in B then Y code and so on.

Here is how far I have gotton. (which is not far at all seeming as it is not working)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If Target.Column = ("B:B") Then
    
    ActiveCell.Copy
    
    With Sheets("SQL_Pull")
        .Range("A10").PasteSpecial
    End With

    Sheets("Data").Select
    ActiveSheet.ListObjects("Table_Query_from_RPWLIVE").Range.AutoFilter Field:= _
        11, Criteria1:="=*" & Sheets("SQL_Pull").Range("A10") & "*",
Operator:=xlAnd

End If
End Sub

If I swapp out the first line of the above code for

I can get it to work for that cell, but Im lost as to how to get it to apply to a range of cells or a whole column.

In reality I dont need the whole column, just the first 25 rows and that data is fixed.

Any help would be greatly appreciated as Im running round in circles and Its actually given me a headache lol

I am having problems in making a command button invisible (worksheet, not form) after it has been clicked (Excel 2007). I tried this :

	VB:
	
 ShowAsGraph_Click() 
     
     'This is what the button should, and does, do.
    ActiveSheet.ChartObjects("Dashboard_Chart").Visible = True 
     
     'Now I want to hide the button to ensure it does'nt get re-clicked
    ActiveSheet.Shapes("ShowAsGraph").Visible = False 
     '.... but it stays visible.
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
without effect. I imagine the click-event is refusing to hide its own object because it is still executing.


No luck finding an answer? You could always try Google.