Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell value change triggers event (Excel 2000)

    Hello, was wondering if someone could help me understand how to have a cell run a peice of code each time its clicked on in a worksheet.

    I have a cell Named Plug it holds numbers.

    below it I have a combobox with 5 options in its list (useless information) that controls options in the code behind the combobox). this combo box runs code and works fine if the plug value is entered in first and then the options in the combo box are picked. But if the combobox options are picked first because the user forgot to enter a value in Plug then the code in the combobox misses the entry of plug which is a varible in the decision making process of True or False.

    My question is, Is there a way to make this work If cell "plug" is clicked on it runs my macro behind the combobox?

    if so how do I make this work

    thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell value change triggers event (Excel 2000)

    Edited by HansV to correct typo

    Search this forum for Worksheet_Change and Worksheet_SelectionChange.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rngPrev As Range
    If Not rngPrev Is Nothing Then
    If Not Intersect(rngPrev, Range("plug")) Is Nothing Then
    Application.EnableEvents = False
    MsgBox "Do your thing here."
    Application.EnableEvents = True
    End If
    End If
    Set rngPrev = Target
    End Sub

    Will this macro chucnk do what I need, bacically if cell "Plug" changes run treat macro?
    Im sorry this code has alot of new commands for me

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell value change triggers event (Excel 2000)

    The Worksheet_SelectionChange code will fire when the user selects the cell named Plug.
    If you want it to run when the user changes the value of the cell named Plug, change the code to

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("plug")) Is Nothing Then
    Application.EnableEvents = False
    MsgBox "Do your thing here."
    Application.EnableEvents = True
    End If
    End Sub

    First test that the message box is displayed when you change the value of the cell, then replace the MsgBox line with a call to the macro you want to run.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Sheets("sheet1").Range("plug")) Is Nothing Then
    Application.EnableEvents = False
    MsgBox "Do your thing here."
    Application.EnableEvents = True
    End If
    End Sub

    So I have added this peice of code behind worksheet "Sheet1" and when I save the workbook and goto the plug cell and select is nothing appens no message.
    Do I need to set anything?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell value change triggers event (Excel 2000)

    Are you sure that plug is on Sheet1? The code runs as expected when I name a cell "plug".
    Also, make sure that EnableEvents is on - if it has been turned off previously, Excel will not react to events.
    - Activate the Visual Basic Editor.
    - Activate the Immediate window (Ctrl+G).
    - Type Application.EnableEvents = True and press Enter.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    Yes this fixed it, what does Application.EnableEvents = True do? can it have any harmful effects?

    how do I call a macro to trigger in VBA?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    The Application.EnableEvents = True is the normal condition, events would only be disabled if you turned them off somehow, like interrupting a bit of VBA code which had set it to False before the code set it back to True. Having it set to False would have harmful effects, like the one you noticed where your code did not run.

    Hans said earlier in this thread that he thought you should be using the Worksheet_Change event rather than the SelectionChange. From the code you posted, we can't tell for sure, but I want to emphasize what Hans said. From what I can see, it sure looks like you need to be using the Worksheet_Change event.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell value change triggers event (Excel 2000)

    Simply use a line

    Call macroname

    or even

    macroname

    (of course, substituting the name of the macro).

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    Yes, I think Hans is right, I am using the Worksheet change now, and it takes care of every thing i need it to do.

    One last question, how do I run a macro I already wrote above this macro so that this macro will move into the other code run it and complete?

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    What exactly do you want to do, call the Worksheet_Change event routine? That could get tricky, so we would need a description of exactly what you are trying to accomplish.
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    thank you
    !!!!!!!!!!!!!

  13. #13
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    I'm working on something similar. I can't get the cell change event to work so it will trigger my macro "MySort". I made sure EnableEvents is turned on first. Any thoughts? I've tried using different values for the Range with no luck. Realistically, I would like any change in column 'I' to trigger my sort macro.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("I:I")) Is Nothing Then
    Application.EnableEvents = False
    Call MySort
    Application.EnableEvents = True
    End If
    End Sub

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    Where are you putting this code? It goes in the event module behind the worksheet you want to trigger the event, not in a normal module like the one where you probably have your MySort routine. To find the module where this code goes, either right click on the tab for the sheet that should trigger the event and then click on View Code in the pop up menu, or in the Project Explorer in VBE, right click on the sheet object and the click on View Code in the pop up menu. If you still can't find it, then please upload a test workbook that shows where you are putting the code.
    Legare Coleman

  15. #15
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell value change triggers event (Excel 2000)

    I've attached a sample. The spreadsheet tracks our main account. However, entries are not always placed in date order. So, instead of maunally having to sort everytime, I would like the file to automatically sort every time the balance (column I) is updated.

    As you'll see by the brief code, I have no idea what I'm doing. I'm working my company to pay for some VB education...

    Thanks always!!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •