+ Reply to Thread
Results 1 to 8 of 8

Private sub worksheet_change not working

  1. 03-19-2013, 07:34 AM #1
    DavidBW
    DavidBW is offline
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    Private sub worksheet_change not working

    Hello
    I'm having trouble with a worksheet change code. No problem with this type of code elsewhere in the workbook but on this particular sheet it is not playing ball.

    Code as follows:

    Please Login or Register  to view this content.
    Nothing happens if I change cell C4. If I disable that line then the Auth macro is called if I change any cell (including c4 which is a drop-down list) in the offending worksheet. Have run 
    Please Login or Register  to view this content.
    in a separate macro but still no joy.

    Any help, as always, much appreciated
    Last edited by arlu1201; 03-19-2013 at 07:44 AM. Reason: Replaced php tags with code tags.
    Reply With QuoteRegister To Reply
  2. 03-19-2013, 07:54 AM #2
    Wormsek
    Wormsek is offline
    Registered User
    Join Date
    03-18-2013
    Location
    Stryków, Poland
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    21

    Re: Private sub worksheet_change not working 

    Please Login or Register  to view this content.
    Address returns capital letters.
    Reply With QuoteRegister To Reply
  3. 03-19-2013, 07:58 AM #3
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Private sub worksheet_change not working

    If its a dropdown, then try worksheet_selection change event instead of worksheet change event.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  4. 03-19-2013, 07:59 AM #4
    MickG
    MickG is offline
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Private sub worksheet_change not working

    Try an Uppercase "C" := "$C$4"
    Reply With QuoteRegister To Reply
  5. 03-19-2013, 08:14 AM #5
    Wormsek
    Wormsek is offline
    Registered User
    Join Date
    03-18-2013
    Location
    Stryków, Poland
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    21

    Re: Private sub worksheet_change not working

    Quote Originally Posted by arlu1201 View Post
    If its a dropdown, then try worksheet_selection change event instead of worksheet change event.
    If it's data validation, then no need to change event.
    Reply With QuoteRegister To Reply
  6. 03-19-2013, 08:20 AM #6
    AntrobusJ
    AntrobusJ is offline
    Registered User
    Join Date
    03-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Private sub worksheet_change not working

    Why not do in Worksheet_change:

    msgbox (target.address)

    I have a feeling when your typing it in, your hitting Enter/Return and its selecting a new cell so it becomes C5 maybe.
    This will confirm if your getting the correct data back that your looking for in your if check.
    Reply With QuoteRegister To Reply
  7. 03-21-2013, 10:05 AM #7
    DavidBW
    DavidBW is offline
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    Re: Private sub worksheet_change not working

    Thank you all for your various inputs. It was simply changing c to C that fixed the problem.

    Regarding Wormsek's answer that a change event is not required then how is that recorded in the worksheet code?
    Reply With QuoteRegister To Reply
  8. 03-21-2013, 06:06 PM #8
    Wormsek
    Wormsek is offline
    Registered User
    Join Date
    03-18-2013
    Location
    Stryków, Poland
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    21

    Re: Private sub worksheet_change not working

    Quote Originally Posted by DavidBW View Post
    Regarding Wormsek's answer that a change event is not required...
    I was talking about SelectionChange Event, not Change Event . If you use SelectionChange, code will start after selection of cell with list, not after cell's change.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1