+ Reply to Thread
Results 1 to 5 of 5

Hide/Unhide Columns based upon Active X Combobox value

Hybrid View

  1. #1
    Registered User
    Join Date
    11-19-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    7

    Hide/Unhide Columns based upon Active X Combobox value

    I am fairly new to VBA and coding but I have been really wanting to learn so I have taken on some projects that have challenged me to learn some new tricks. However, I have hit a stumbling block and I have a need to hide a particular set of rows based upon the selected value from an Active X ComboBox. I am using ComboBox2 and it is linked to cell V75. The values in the drop-down are the following: (January, February, March, April, May, June, July, August, September, October, November, December, Full Year). When the option is selected from the drop-down list I need the columns to be hidden as follows:

    January = D:N
    February = C, E:N
    March = C:D, F:N
    April = C:E, G:N
    May = C:F, H:N
    June = C:G, I:N
    July = C:H, J:N
    August = C:I, K:N
    September = C:J, L:N
    October = C:K, M:N
    November = C:L, N
    December = C:M
    Full Year = all columns visible

    Any help is greatly appreciated, thanks!!

    Stephen

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Hide/Unhide Columns based upon Active X Combobox value

    Here is January and February. Follow the template to code the rest of the months

    Private Sub ComboBox2_Change()
    
    Columns("C:M").Hidden = False
    
    Select Case ComboBox2.Value
        Case Is = "January"
            Columns("C:N").Hidden = True
        Case Is = "February"
            Columns("C:C").Hidden = True
            Columns("E:N").Hidden = True
        'etc
        'etc
    End Select
    
    End Sub
    If you are happy with my response please click the * in the lower left of my post.

  3. #3
    Registered User
    Join Date
    01-28-2016
    Location
    Kraków
    MS-Off Ver
    Office 2010
    Posts
    14

    Re: Hide/Unhide Columns based upon Active X Combobox value

    This might help:

    Private Sub UserForm_Activate()

    Worksheets("Sheet3").Columns("A:Z").EntireColumn.Hidden = False

    With ComboBox1
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
    .AddItem "April"
    .AddItem "May"
    End With

    End Sub

    Private Sub CommandButton1_Click()

    combo_value = ComboBox1.Value

    month_ref1 = Array("January", "February", "March", "April", "May")
    month_ref2 = Array("", "C:C", "C:D", "C:E", "C:F")
    month_ref3 = Array("D:N", "E:N", "F:N", "G:N", "H:N")

    For i = LBound(month_ref1) To UBound(month_ref1)
    If month_ref1(i) = combo_value Then Exit For
    Next i

    If month_ref2(i) <> "" Then
    Worksheets("Sheet3").Columns(month_ref2(i)).EntireColumn.Hidden = True
    End If
    Worksheets("Sheet3").Columns(month_ref3(i)).EntireColumn.Hidden = True

    End Sub

  4. #4
    Registered User
    Join Date
    11-19-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    7

    Re: Hide/Unhide Columns based upon Active X Combobox value

    Thank you both for your prompt replies. I went with stnkynts's solution and it worked well. The only thing I noticed is that the columns seemed to have some lag time when hiding multiple rows. Is there any remedy to this lag?

  5. #5
    Registered User
    Join Date
    01-28-2016
    Location
    Kraków
    MS-Off Ver
    Office 2010
    Posts
    14

    Re: Hide/Unhide Columns based upon Active X Combobox value

    Maybe this one, but I am not sure:

    Application.ScreenUpdating = False

+ 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. hide and unhide columns based on cell value
    By darijokesar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-29-2014, 03:51 AM
  2. Macro to Hide/Unhide columns depending upon the active cell
    By kets0985 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2013, 07:14 AM
  3. [SOLVED] HIde/Unhide rows not equal to a value from a combobox (Active X)
    By marygem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-17-2012, 05:38 AM
  4. Hide and unhide columns based on value of 0 or 1 (respectively) in cells G1:LG1
    By cplawren in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2012, 01:58 AM
  5. [SOLVED] How VB code can unhide/hide rows based on combobox selection?
    By jgomez in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2012, 01:25 PM
  6. Hide and unhide rows based on combobox
    By pkling in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2010, 03:25 AM
  7. Hide or Unhide certain columns based on a cell value
    By gonecrazybacksoon@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 01:04 PM

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