Checkboxes removing columns

Jaclyn · Jan 18th 2006

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,
Jaclyn.

Code

Private Sub cbViewCalculatedData_Click()


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


If ActiveSheet.Name <> Me.Name Then Exit Sub        '' calling sheet must be the active sheet!


If Not WizardInProgress() Then


On Error GoTo cbVCD_NBG




Application.ScreenUpdating = False


For Each aNames In ActiveSheet.Names
    
    If (Mid(aNames.Name, 25, 4) = "_O_2") Then
        If cbViewCalculatedData.Value = True Then
            ' Check for active values
            NumberofUnits = Sheets("Unit_Flotation_Overview").Range("UnitList").Rows.Count
            For x = 1 To NumberofUnits
                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
            Next x
        Else
            Range(aNames.Name).Columns.Hidden = True
        End If
    End If
  
Next aNames


   
cbVCD_NBG:


Application.ScreenUpdating = True


End If


End Sub

Display More

**iwrk4dedpr** · Jan 18th 2006

Re: Checkboxes removing columns

Jaclyn,

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.

rbrhodes · Jan 19th 2006

Re: Checkboxes removing columns

Hi,

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

[vba]
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
Else
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!
cbVCD_NBG:

Application.ScreenUpdating = True

End If

End Sub

[/vba]

Cheers,

dr

Checkboxes removing columns

Participate now!

Share

Users Viewing This Thread