+ Reply to Thread
Results 1 to 2 of 2

hide columns on condition

  1. #1
    Registered User
    Join Date
    08-05-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    hide columns on condition

    Hello,

    I tried to find an answer to my question, but I wasn’t able to find something that was exactly like my problem. Also ,considering I have no idea about VBA and code, it’s impossible for me to try and decipher other codes and try to make changes for what I need mine to be doing. If it is hiding out there somewhere please direct me to it!

    I need to hide columns on several worksheets (unfortunately not the same columns on each worksheet) based on one number contained in a certain cell in a different sheet.

    The cell is sheet2!B9. The input number can range between 1 and 20 and based on the number input, I need it to hide certain columns.

    For example, if sheet2!B9=1, then I need it to hide columns C through U on sheet 3 and 4, but to hide columns D through V on sheet 5.
    Next, if sheet2!B9=2, then I need to hide columns D through U on sheet 3 and 4, and columns E through V on sheet 5.
    Next, if sheet2!B9=3, then I need to hide columns E through U on sheet 3 and 4, and columns F through V on sheet 5.
    And so on and so on, up to B9=20 and then all columns will be showing.

    I would like this macro to only run if B9 changes (I don’t want it to be running all the time).
    If someone could kindly start me in the right direction. If I need to copy and paste the code and then make changes for the 20 conditions, I’m ok with doing that as long as I know which parts to change. Also, if you could help with telling me which part to put the code (worksheet, workbook etc)

    tia

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: hide columns on condition

    Hi

    To do this you need to use the worksheet change event. Put this code into your Sheet2 code:

    Please Login or Register  to view this content.
    Make sure the left combobox above the code says "Worksheet" and the right one says "Change".

    Then put the following in a standard module:

    Please Login or Register  to view this content.
    That should do it. Let me know if it doesn't work as you expected it.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

+ 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