Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to hide columns (2003)

    Hello Everyone,

    I need some help. I have a file that has a few columns and would like to create a macro and a combobox to hide the columns that are not selected. My purpose for doing this is to have the user only input in the column that was selected by the combobox. This will create less error. I am attaching a small sample of a file for references.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to hide columns (2003)

    I'm not sure I understand your description entirely, but does the attached version do what you want?
    The combo box in cell A1 is the result of Data | Validation.
    There is code in the worksheet module for Sheet1 to show/hide columns.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to hide columns (2003)

    Thanks Hans it does what I need, but how about if I need to display all the columns for printing. Can we put it in the combobox?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to hide columns (2003)

    Does this do what you want? The first item in the list should display all columns.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to hide columns (2003)

    See the attached modified version. I added a new value "All" in B1 (column B stays hidden) and adjusted the code. If "All" is selected, all Test columns are displayed.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to hide columns (2003)

    All doesn't seem to display all Test columns.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to hide columns (2003)

    Oops, you're correct. I didn't take into account that End(xlToLeft) operates on visible columns only.

    Easily repaired by changing

    For i = 3 To Range("IV1").End(xlToLeft).Column
    Columns(i).Hidden = False
    Next i

    to

    For i = 3 To 256
    Columns(i).Hidden = False
    Next i

    Thanks for testing!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to hide columns (2003)

    I didn't really test. I looked at your code to see how similar it was to mine and saw that you made the same error I did on my first try. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to hide columns (2003)

    <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •