+ Reply to Thread
Results 1 to 23 of 23

Change(ByVal Target As Range) does not work when Target value changes

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Change(ByVal Target As Range) does not work when Target value changes

    I am trying to hide/show columns based ona calculated value in cell B3.
    When I change the value in B3 manually all goes fine, however if the value in B3 is a formula it doestnt work. What's wrong??


    Please Login or Register  to view this content.
    Last edited by LeonvL; 01-07-2013 at 06:47 PM. Reason: solved

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Hello & Welcome to the Forum,

    You need to look into the Worksheet_Calculate event
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    The worksheet_Chance is triggert by target not by a formula.
    Use:
    Please Login or Register  to view this content.
    Kind regards, Harry.

  4. #4
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Thanks Jeff and Harry,

    I am a new one on this but struggling how to solve this.

    I have created a file where I have one set up page where I define which scenario's I want to show.
    Each scenario has one sheet and that shows or hides depending on a field valua on that page "show"or "noshow". That works.
    Then I have some summary sheets that pulls date from these sheets. If the scenario is not visible, it hides the column. That works fine now as well based on your suggestions. (I am not sure it's been the best way how to program it, but its working...)
    Then I also want to hide some rows in a sheet and it seems that when I combine it with hiding the columns it's not working.

    I have attached the file for reference. I was wondering if you could help me out here...

    Help much appreciated. Leon
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    In sheetmodul "Financial"
    This code does the same.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    That's a lot easier Harry, thanks!!
    But what do I do when I also want to show/hide rows? Like in this sheet rows based on their values in column B range (B5:B14) and (B18:27), etc...

  7. #7
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    First: Save the file on your Pc.
    In module1 is a code named "hsv".
    Please Login or Register  to view this content.
    Connect code "hsv" to the 10 checkboxes in the code sheet "Project Setup" (select checkbox with rigthclick...connect)
    Save file.

    The code in thisWorkbook is marked as text (so you can delete it).
    The code in sheet "Financial" is adjusted.

    Hopes this is what you mean.
    Attached Files Attached Files
    Last edited by HSV; 01-03-2013 at 05:55 AM. Reason: shorter/faster

  8. #8
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Harry

    This is amazing how you do this.... For a newbe like me I can only be amazed by the fact it's working and having no clue how this really works
    There is just one thing that no longer works: the ckeckbox that turns on/off the "show cashflows" ....

    BTW what would you recommend as reference material for learning VBA to learn basics & intermediate level? Any good sources on internet or books?

    Many thanks - this has been very helpful so far!!

    Leon

  9. #9
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    I'm glad to read you're happy.
    Connect this code to the checkbox "Cashflows".
    Please Login or Register  to view this content.
    BTW: Unfortunately I can't help what I you should recommend. I've never read an Excel book. Many forums visits (Dutch) and try, and read the "Excel Help". Perhaps a book much better.

  10. #10
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    I don't know why but when I copy all the code in the original sheet's is shows an error 1004 on this line:

    Please Login or Register  to view this content.
    Last edited by LeonvL; 01-04-2013 at 06:42 PM.

  11. #11
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Harry - The intention was to for exampleonly show "CF01" is "Scenario 01" was shown as well...

    Maybe too may questions for now

  12. #12
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    Why the code gives an error, I can't figure it out.
    Everything works fine in the attached file.

    Maybe you can try:
    Please Login or Register  to view this content.
    I changed the code and it is still faster then the code in ThisWorkbook was.
    Please Login or Register  to view this content.
    Last edited by HSV; 01-03-2013 at 05:59 PM.

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Change(ByVal Target As Range) does not work when Target value changes

    @LeonvL,

    Please update post #10 with code tags.

  14. #14
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Harry,

    When I replace
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    it dos no longer give the runtime error 1004, but neither it does anything that the code was supposed to do...

    Leon

  15. #15
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    You can't upload the original file?

  16. #16
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Harry - I rather not share the full workbook in a public domain.... Any other means I can send you this file?

  17. #17
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    In the original file you have more shapes then only checkboxes.
    Add the red text in the code.
    Hopes this works for you.
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Hi Harry,

    I was not aware that there were any other shapes than the checkboxes in the "Project Setup" sheet... but the Run Time Error no longer shows. That part is solved!

    With showing Cashflows hovever there is just one odd thing....
    Using the ckeck box "show cashflows" is not working properly in combination with the other checkboxes. For example when turning one scenario off and then turning cashflow on and off does sometimes show CF's even when the ckeckbox is false. I think the following works like toggling on/of:

    Please Login or Register  to view this content.
    What it should do is that the "CF*" sheet should be only visible when the checkbox "show cashflows" is true and the related scenario is also true....
    It appears when "show cashflows" is turned on, you then turn one or more scenario's off and then turning cashflows off and on again.

    Ans CF10 allways acts strange - it does not follow normal working like the others....

    Best regards, Leon
    Last edited by LeonvL; 01-06-2013 at 05:34 PM.

  19. #19
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    A validationlist is also a shape.

    Remove code Sub show_cashflows().

    Connect the code hsv() to checkbox "show cashflows".
    Now it must do what you have in your mind I think.
    Last edited by HSV; 01-06-2013 at 06:05 PM. Reason: typo

  20. #20
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Noop - That is what I initially tried but...

    First it showed only the cashflows of the scanario's that were OFF
    When I then manually hide all CF* sheets and put the checkmark on for Show CF's it will show all the CF , but unchecking the show CF* does not hide them anymore...

    One other thing stopped funtioning as well: in each CF* sheet it no longer hided the colums for the outer years (f.e.: when the number of years is set to 10 in the setup sheet, it shows the full 20 years in the cashflow.)
    Last edited by LeonvL; 01-07-2013 at 05:48 AM.

  21. #21
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Leon,

    In the original sheet the "sh.ZOrderPosition" of "Show Cashflows" is 12.
    I changed the whole code.
    Please Login or Register  to view this content.
    In sheetmodul "Project setup".
    Please Login or Register  to view this content.
    If you wish, I send you the link for the file by private message.

  22. #22
    Registered User
    Join Date
    12-30-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Change(ByVal Target As Range) does not work when Target value changes

    Big HURRAY!!!
    Many thanks, Harry!

    By the way I have changed the Range in Financials:
    Please Login or Register  to view this content.

  23. #23
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Change(ByVal Target As Range) does not work when Target value changes

    You're welcome Leon.

    I haven't seen that the range was longer.
    Well done.
    Thanks for the feedback.

+ 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