+ Reply to Thread
Results 1 to 6 of 6

Run Public Sub in Worksheet_Change Event

Hybrid View

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    new york city
    MS-Off Ver
    Excel 2010
    Posts
    24

    Run Public Sub in Worksheet_Change Event

    Hello,

    I have a Public Sub that sorts a range of cells in alphabetical order on Worksheet 1. When I execute this sub manually, it works. However, I would like it to automatically execute when a cell value is changed on Worksheet 2, within the same Workbook. Both the Worksheet_Change Event and Public Sub are written in Worksheet 2 where the Change event takes place. Can someone please explain why the sub does not execute when using the following code?

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("Date_Cell")) Is Nothing Then
        Application.EnableEvents = Flase
            Run "Range_Sort"
        Application.EnableEvents = True
    End If
    
    End Sub
    Many thanks!

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run Public Sub in Worksheet_Change Event

    If Range_Sort is the name of your macro, replace Run "Range_sort" with Call Range_Sort() or just Range_Sort()

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    Try this.
    Call Range_Sort
    By the way, your sort sub should probably be in a standard module.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    04-15-2013
    Location
    new york city
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Run Public Sub in Worksheet_Change Event

    Thank you both. You were correct to use:

    Call Range_Sort
    Initially, this still did not work, until I realized that both the Module Name and Sub Name where the same. So to fix this, I either have to specify ModularName.SubName or simply change either name so that VBA knows which feature to call.

    Thanks!

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run Public Sub in Worksheet_Change Event

    Can you post your workbook as an attachment so it can be amended?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    Never use the name of a sub/function for the name of the module the sub/function is in.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1