+ Reply to Thread
Results 1 to 13 of 13

Chart programming help needed to calculate with hidden columns and more

  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Chart programming help needed to calculate with hidden columns and more

    Hello all. This started at

    http://www.excelforum.com/excel-prog...t-works-2.html

    but since there are now specific chart-related code questions I thought it might be better addressed here.

    There are several things I need help with. The attached workbook has a Test sheet with the updated code. Some things I am still looking for are:

    1. Hiding columns D and E while maintaining the calculations performed via the code.
    2. Create new sheets with charts dynamically using InputBox from Header sheet.

    I have done a lot of research and updates as can be seen in the original thread, but any help to perform the above and/or streamline the existing code would be most appreaciated!!

    Thanks,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 10-21-2010 at 09:07 AM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Chart programming help needed to calculate with hidden columns and more

    What's the problem with hiding the columns? If it's that the chart doesn't display them, then right-click the chart, choose Select data, then click the Hidden and Empty Cells button, and choose the 'Show data in hidden rows and columns' option.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    romperstomper, I want to have the columns hidden from the start.

    Thanks,
    Andrew
    Last edited by drewship; 10-20-2010 at 10:01 AM.

  4. #4
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    I tried to record a macro to allow the chart to use hidden cells but nothing was recorded.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Chart programming help needed to calculate with hidden columns and more

    And?

    (to respond to the text you deleted for some reason, the programmatic way is:
    Please Login or Register  to view this content.
    )

  6. #6
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    Sorry about the deleted text...I misread your reply (I was in the middle of my reply when the buildiing fire alarm went off and I had to leave).

    Thanks for the code. Don't know why it is so hard to find some of these things in the Office help and I didn't see this in any of the forum posts that came up in the search.

    Andrew

  7. #7
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Chart programming help needed to calculate with hidden columns and more

    There are quite a few things which are mysteriously hard to find in Excel help, if you aren't sure, try something like type "activechart." (n.b. the .) and have a look through the things that come up, many are self-explanatory
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  8. #8
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    Thanks Cheeky Charlie. I will have to start doing that!!

  9. #9
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    Ok. Trying to create new sheets with charts dynamically using InputBox from Header sheet.

    According to Microsoft Help: This example defines the name "myName" for cell A1 on Sheet1.

    Please Login or Register  to view this content.
    What I need to do is replace the "myName" and Sheet1 with the value input by the user via the InputBox.

    I have tried:

    Please Login or Register  to view this content.


    and sName which is the variable assigned to the InputBox

    Please Login or Register  to view this content.
    but none of these creates the name using the InputBox value.

    Anyone know how to do this?

    Thanks,
    Andrew

  10. #10
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    I tried the following code which does not have any range names and got a blank chart:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Chart programming help needed to calculate with hidden columns and more

    You have to build up the formula string, not literally include the value in the string:
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Chart programming help needed to calculate with hidden columns and more

    Ok. Trying to create new sheets with charts dynamically using InputBox from Header sheet.

    According to Microsoft Help: This example defines the name "myName" for cell A1 on Sheet1.

    Code:

    Please Login or Register  to view this content.
    What I need to do is replace the "myName" and Sheet1 with the value input by the user via the InputBox.

    I have tried:
    Doesn't really match what you've posted.

    My only real point, though, is that it helps to make your code a bit more modular if you can, ideas like:
    Please Login or Register  to view this content.
    (it might be worth adding an error check for a duplicate sheet name)

  13. #13
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Chart programming help needed to calculate with hidden columns and more

    Thanks romperstomper!! I have applied the changes to my code and have it working. I have attached the functional workbook. Click the button the the Utils sheet and a user-named sheet is copied and the initial graph is created. Added tasks will be updated to the chart and the bar will appear when the % completed is entered. I may try to automatically set the default value of % Completed to 0 since that is usually how a task would start.

    Cheeky Charlie, I will look for some code to check for duplicate sheets and will look into reworking the code to see if your example performs better.

    Thanks all!!

    Andrew
    Attached Files Attached Files

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1