Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hid Columns Based on a named Cell (Excel 2002)

    Hi

    On activation of a sheet I need a colum to be hidden based on a named cell on another sheet.

    Please see sample sheet attached.

    Many thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Instead of using the Activate event of Lead Prices, you could use the Change event of the Input sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E36")) Is Nothing Then
    ' Worksheets("Lead Prices").Unprotect Password:="wahsdarb"
    Worksheets("Lead Prices").Columns.Hidden = False
    Select Case Range("E36")
    Case "B1"
    Worksheets("Lead Prices").Columns("L").Hidden = True
    Case "C1"
    Worksheets("Lead Prices").Columns("M").Hidden = True
    Case "C2"
    Worksheets("Lead Prices").Columns("N").Hidden = True
    End Select
    ' Worksheets("Lead Prices").Protect Password:="wahsdarb"
    End If
    End Sub

    If you specify that the users can format columns when protecting Lead Prices, the code will work as is. If you don't want users to be able to format columns, you must uncomment the two lines dealing with protection.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Hi Hans

    Works fine

    Thank you very much

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Hans,
    Does the Not Intersect condition prevent the macro from triggering if any other cell (besides E36) is changed?
    Tx
    Regards,
    Rudi

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

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Yes indeed. Changing the visibility of columns each time an arbitrary cell is changed would be very inefficient.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Tx. This Intersect Function is VERY handy...I must really make an effort to understand and use it more!
    Regards,
    Rudi

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hid Columns Based on a named Cell (Excel 2002)

    A minor nit in sematntics:
    I would not say that it prevents the macro from "triggering" since the macro is being triggered. It just does not do anything if the condition is not met.

    They are a little different...

    Steve

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hid Columns Based on a named Cell (Excel 2002)

    Yes, you are correct. The event fires each time the worksheet recieves a change, but the condition ends the procedure almost immediately! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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