+ Reply to Thread
Results 1 to 12 of 12

Hiding columns in different sheets dependent on the input in one cell on one sheet.

  1. 09-23-2011, 10:41 AM #1
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Hiding columns in different sheets dependent on the input in one cell on one sheet.

    Hey All,

    I have the following code which does what I want.: Hide columns dependent on the input in a cell. I want this to work in all the worksheets. I have only just started playing around with VBA and from reading a few posts this could be done with a loop function but I'm not sure my code is in the correct form...

    Please Login or Register  to view this content.
    Any advice on how to get this to work across all the sheets would be greatly appreciated
    Reply With QuoteRegister To Reply
  2. 09-23-2011, 10:54 AM #2
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi acj06

    Place the code in ThisWorkbook module as a Workbook_SheetChange event.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    Reply With QuoteRegister To Reply
  3. 09-23-2011, 10:55 AM #3
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi acj06 and welcome to the forum. If you want that particular procedure to work on other sheets, simply copy it and paste it into other sheet modules.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---
    Reply With QuoteRegister To Reply
  4. 09-23-2011, 11:01 AM #4
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Thanks for the advice, but I want it to run it off the one cell in one sheet, so i enter a multiple of 5 in $g$26 in worksheet("Inputs") and the columns dissapear dependent on that input...Any advice?
    Reply With QuoteRegister To Reply
  5. 09-23-2011, 12:24 PM #5
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi acj06

    Probably something like this 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 09-26-2011, 05:12 AM #6
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    John,

    The code works excellently! Thank you. I was wondering if i wanted to specify which worksheets to hide columns in, how would I tell excel which sheets to hide the columns in?

    Thanks Again

    Alex
    Reply With QuoteRegister To Reply
  7. 09-26-2011, 09:03 AM #7
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi acj06

    Replace these lines of code 
    Please Login or Register  to view this content.
    with this 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-26-2011, 10:46 AM #8
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    John,

    Everything was working a treat, I continued building my model...I went to change the &G&26 input and now i get the following error:

    Run-time error '1004':
    Reply With QuoteRegister To Reply
  9. 09-26-2011, 10:50 AM #9
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    John,

    Thanks for your help it is invaluable. The code was working perfectly, so i continued working in excel. Only now to find when I try and change the value in cell &G&26, i ge the following runtime error:

    Run time error '1004'
    unable to set the Hidden property of the range class

    Do you have any suggestions on how to get around this problem. Thanks for the advice!

    Alex
    Reply With QuoteRegister To Reply
  10. 09-26-2011, 11:03 AM #10
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi Alex

    You probably should have gotten "Object Required". Try this revised code 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 09-26-2011, 11:20 AM #11
    acj06
    acj06 is offline
    Registered User
    Join Date
    08-23-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    John,

    Thanks that works brilliantly!

    Alex
    Reply With QuoteRegister To Reply
  12. 09-26-2011, 11:29 AM #12
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Hiding columns in different sheets dependent on the input in one cell on one shee

    Hi Alex

    You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
    To mark your thread solved do the following:
    - Go to your first post on the thread
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solved
    - Click Save
    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)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1