Checkboxes removing columns

  • Hi,

    I have two checkboxes in a worksheet which when ticked, remove certain columns in a table in the same worksheet...let me rephrase...they are supposed to do this. I am trying to use some code that I didn't write (shown below) but to be honest have no idea how it works. The idea is that columns are removed based on the colour of particular cells in the columns.

    Could someone explain to me the idea behind doing this. What is the 'object' and how does this work?

    Any help is appreciated...I'm lost.

    Many thanks,

  • Re: Checkboxes removing columns


    How's the sun down under?

    Can you post the workbook?

    The variable declared as "Object" is receiving all of the "Names" associated with the activesheet.

    Other than that, without a workbook, I'm lost.

  • Re: Checkboxes removing columns


    Here's your code with some comments. Hopefully they make some sense to you!

    Private Sub cbViewCalculatedData_Click()

    Dim aNames As Object
    Dim NumberofUnits As Integer
    Dim x As Integer

    'Check that sub is called from Active sheet. No then Exit sub
    If ActiveSheet.Name <> Me.Name Then Exit Sub '' calling sheet must be the active sheet!

    'Error and screen handling...
    If Not WizardInProgress() Then
    On Error GoTo cbVCD_NBG
    Application.ScreenUpdating = False

    'Makes a list of all named ranges in the active sheet, starts with the 1st name
    For Each aNames In ActiveSheet.Names

    'Checks the value of the 4 characters in the name starting at position 25
    If (Mid(aNames.Name, 25, 4) = "_O_2") Then

    'If the 4 chars are _O_2 then check cbViewCalculatedData value True or False?
    If cbViewCalculatedData.Value = True Then

    'If cbViewCalculatedData value is TRUE get count of "active values" (count of rows)
    NumberofUnits = Sheets("Unit_Flotation_Overview").Range("UnitList").Rows.Count

    'and loop on 1 to number of "active values" (all counted rows)
    For x = 1 To NumberofUnits

    'If the colour of cell "x" in column 1 of the named area is "37", unhide the Column
    If (Range(aNames.Name).Cells(x, 1).Interior.ColorIndex = 15 Or Range(aNames.Name).Cells(x, 1).Interior.ColorIndex = 37) Then
    Range(aNames.Name).Columns.Hidden = False
    End If

    'The colour is not "37", check the next cell in Column
    ' NOTE: x will equal x+1 each time through this loop
    Next x

    'If cbViewCalculatedData value is FALSE just hide the column
    Range(aNames.Name).Columns.Hidden = True
    End If
    'If the 4 chars are NOT _O_2 then get the next name on the list
    End If
    Next aNames

    'Something screwed up, skip everything and come here!

    Application.ScreenUpdating = True

    End If

    End Sub




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!