+ Reply to Thread
Results 1 to 22 of 22

VBA or Script to autohide/show columns based on helper cell - See Photo

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    VBA or Script to autohide/show columns based on helper cell - See Photo

    I was trying to figure out was code that will display and auto hide columns based on drop down selection.

    So in the attached image, I would like to hide/display all columns after column AI with the drop box based on the helper value in row 4.

    For example,

    When selecting drop down for particular row "ACS", all columns after AI will automatically update and hide or show based on the helper field.

    So when ACS is seclected all columns containing "A", "B" after column AI would display and columns labeled "L" and "N" would autohide.

    When selecting NVS, all columns after AI containing "N", "B" would display and columns labeled "L" and "A" would autohide.

    And so on, which I'll do once I understand what's going on in the code or formula.

    Thanks.
    Kevin
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Hi kcstier,

    Try this event macro on the sheet in question:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Thanks that works, how do I apply this to work for each row in column AI, that has the drop down box? Say, AI8 through AI200? Thanks again for your help.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Replace this line...

    Please Login or Register  to view this content.
    ...with this:

    Please Login or Register  to view this content.
    Won't it clash though i.e. a change in cell AI8 will hide the columns in a particular way but then a change in cell AI9 will do so too. You won't know which columns are being hidden based of what selection.

    Robert

  5. #5
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Yes, but it should update to show the correct columns based on the row (project) my cursor is on. So if "ACS" is located column AI8 and my cursor is anywhere on row 8, I want it to read the value of the drop down in column AI. If my mouse selects row 9, then reads the value of AI9 and updates the show/hide fields.

    Any way to incorporate this into the code?

    Thanks

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Any way to incorporate this into the code?
    That's what my tweak does after a selection from cell AI8 is made

  7. #7
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Yes, but then say AI8 is "ACS" and AI9 is "NVS". When the mouse is clicked on row 9, the fields don't currently update to reflect AI9 value from drop box. It still only displays the categories from the last selected AI dropbox that was picked. It only updates one time when a new selection is made from column AI drop down in a new row.

  8. #8
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    You need to change the event then like so:

    Please Login or Register  to view this content.
    If there's nothing in the cell (below cell AI8) all columns will be displayed. This also means a change in column AI will no longer trigger the code. If you want to run the code on both I'd put the code in a standard module and call it from both events.
    Last edited by Trebor76; 12-06-2019 at 10:18 PM.

  9. #9
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    I copied your last code and replaced the original code, and the original hide/show based on selected dropdown in say AI8 no longer works. lol,

    Thanks for helping me =)

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    This also means a change in column AI will no longer trigger the code. If you want to run the code on both I'd put the code in a standard module and call it from both events.

  11. #11
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    hmm, how do you do that?

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    The following event macros are needed on the sheet in question:

    Please Login or Register  to view this content.
    Then put this code into a standard module:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Where do you insert the event macro? Under "This Worksheet" and "Open"?

    Thanks.

  14. #14
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Didn't you already copy my earlier event macros in?

    Follow these steps:

    1. Copy the two event macros to the clipboard (Ctrl + C)
    2. Right click on the sheet tab name you want the macros to run on
    3. From the shortcut menu click View Code
    4. Paste (Ctrl + V) the macro from step one
    5. From the File menu click Close and Return to Microsoft Excel

    The HideShowCols macro goes into a standard module (where if you record a macro you will see it) i.e.

    1. Copy the code I wrote for the HideShowCols macro to the clipboard (Ctrl + C)
    2. Open the Visual Basic Editor (Alt + F11)
    3. Click on the file name (or any object)
    4. From the Insert menu select Module
    5. Paste (Ctrl + V) the macro from step one
    6. From the File menu click Close and Return to Microsoft Excel
    Last edited by Trebor76; 12-06-2019 at 11:42 PM.

  15. #15
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    I can't figure out how to attach a photo to this thread, so I'll tell you what I did.

    In visual basic, I followed the above steps. I clicked on the sheet where I wanted to paste this code. The top says "Worksheet" on the left above where you write in the code and "SelectionChange" on the right.

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 35 And Target.Row >= 8 Then
    Call HideShowCols(CVar(Range("AI" & Target.Row)))
    End If

    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Column = 35 And ActiveCell.Row >= 8 Then
    Call HideShowCols(CVar(Range("AI" & ActiveCell.Row)))
    End If

    End Sub


    Then, I copied the 2nd part of the code, right clicked insert, and inserted Module. The top says "General" on the left above where you write in the code and "HideShowCols" on the right.

    Option Explicit
    Sub HideShowCols(varCellVal As Variant)

    Dim lngLastCol As Long
    Dim lngMyCol As Long
    Dim strLastCol As String

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With
    lngLastCol = Cells(4, Columns.Count).End(xlToLeft).Column 'Last column in Row 4
    strLastCol = Split(Cells(4, lngLastCol).Address, "$")(1)
    Columns("AJ:" & strLastCol).EntireColumn.Hidden = False
    For lngMyCol = lngLastCol To 36 Step -1
    Select Case varCellVal
    Case Is = "ACS"
    If StrConv(Cells(4, lngMyCol), vbUpperCase) = "L" Or StrConv(Cells(4, lngMyCol), vbUpperCase) = "N" Then
    Columns(lngMyCol).EntireColumn.Hidden = True
    End If
    Case Is = "NVS"
    If StrConv(Cells(4, lngMyCol), vbUpperCase) = "L" Or StrConv(Cells(4, lngMyCol), vbUpperCase) = "A" Then
    Columns(lngMyCol).EntireColumn.Hidden = True
    End If
    End Select
    Next lngMyCol
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    End Sub

    Then, I clicked file, return to Excel and tried, but didn't do anything. Did I miss something?

  16. #16
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    I can't figure out how to attach a photo to this thread
    Photos are no good. We need a sample of the workbook devoid of sensitive information.

    Please wrap any code you post in appropriate tags as I have done i.e.

    [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]

    If nothing is working I'd say EnableEvents is set to off (FALSE).

    Create the following macro (same module as HideShowCols if you like) and step through it (by pressing F8) and then try again:

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    bump, @Trebor76. I made a test spreadsheet I'd like to upload, so you can show me in the test spreadsheet where the macros/code should be placed to get the hide/show functions to work. I can't find where to add attachment to this thread? Any suggestions?

  18. #18
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Please see the attached test spreadsheet.

    Thank you for the help.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Hi Kevin,

    The attached works fine for me.

    Robert
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Hmm, this is strange. I opened the file and made sure macros were enabled, yet nothing happens when I switch between ACS and NVS, etc in column AI and active row. What do you think could be? I also clicked on developer and looks like there's no macros.
    Attached Images Attached Images

  21. #21
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    yet nothing happens when I switch between ACS and NVS
    If the macros are definitely enabled then I can't explain it as like I said it works for me

    You can't see the HideShowCols macro because it needs a parameter to run. This is standard.

  22. #22
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: VBA or Script to autohide/show columns based on helper cell - See Photo

    Morning Trebor76, It worked for me in Excel 2010 but for some reason it's not working in Excel 2016. Any thoughts?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-16-2016, 10:48 PM
  2. Change reference based on month helper cell.
    By ammartino44 in forum Excel General
    Replies: 5
    Last Post: 03-23-2015, 12:36 AM
  3. Replies: 0
    Last Post: 10-31-2014, 03:58 PM
  4. [SOLVED] 2010VBA AutoHide(&unhide) rows based on dynamic cell value
    By epicurean in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-14-2013, 01:30 PM
  5. [SOLVED] Help Consolidating Multiple Formulas in to 1 cell (No Helper Columns)
    By twiggywales in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-26-2012, 03:38 PM
  6. If (average A1:A10 - 10) = >0, show answer, if not, show blank... Without helper
    By ThomasCarter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2012, 10:53 AM
  7. Autohide row based on certain cell value?
    By ajava in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2011, 02:53 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1