PDA

View Full Version : Hide Checkboxes based on last column



Djblois
08-20-2008, 12:35 PM
I have created a form that will test what columns are hidden in a workbook and give the user some more information about the worksheet and workbook. One thing I would I am changing in it is hide all the checkboxes beyond the last column.

ex: if C is the last column then hide the D-AZ checkboxes. Unfortunately, the only way I could think to do this is very long:

Me.ColumnBform.Visible = False
Me.ColumnCform.Visible = False
Me.ColumnDform.Visible = False
Me.ColumnEform.Visible = False
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
If FinalColumn(ActiveSheet) = 1 Then
Me.ColumnBform.Visible = False
Me.ColumnCform.Visible = False
Me.ColumnDform.Visible = False
Me.ColumnEform.Visible = False
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 2 Then
Me.ColumnCform.Visible = False
Me.ColumnDform.Visible = False
Me.ColumnEform.Visible = False
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 3 Then
Me.ColumnDform.Visible = False
Me.ColumnEform.Visible = False
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 4 Then
Me.ColumnEform.Visible = False
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 5 Then
Me.ColumnFform.Visible = False
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 6 Then
Me.ColumnGform.Visible = False
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
ElseIf FinalColumn(ActiveSheet) = 7 Then
Me.ColumnHform.Visible = False
Me.ColumnIform.Visible = False
Me.ColumnJform.Visible = False
Me.ColumnKform.Visible = False
Me.ColumnLform.Visible = False
Me.ColumnMform.Visible = False
Me.ColumnNform.Visible = False
Me.ColumnOform.Visible = False
Me.ColumnPform.Visible = False
Me.ColumnQform.Visible = False
Me.ColumnRform.Visible = False
Me.ColumnSform.Visible = False
Me.ColumnTform.Visible = False
Me.ColumnUform.Visible = False
Me.ColumnVform.Visible = False
Me.ColumnWform.Visible = False
Me.ColumnXform.Visible = False
Me.ColumnYform.Visible = False
Me.ColumnZform.Visible = False
Me.ColumnAAform.Visible = False
Me.ColumnABform.Visible = False
Me.ColumnACform.Visible = False
Me.ColumnADform.Visible = False
Me.ColumnAEform.Visible = False
Me.ColumnAFform.Visible = False
Me.ColumnAGform.Visible = False
Me.ColumnAHform.Visible = False
Me.ColumnAIform.Visible = False
Me.ColumnAJform.Visible = False
Me.ColumnAKform.Visible = False
Me.ColumnALform.Visible = False
Me.ColumnAMform.Visible = False
Me.ColumnANform.Visible = False
Me.ColumnAOform.Visible = False
Me.ColumnAPform.Visible = False
Me.ColumnAQform.Visible = False
Me.ColumnARform.Visible = False
Me.ColumnASform.Visible = False
Me.ColumnATform.Visible = False
Me.ColumnAUform.Visible = False
Me.ColumnAVform.Visible = False
and that isn't all of it. I was wondering if somehow I could loop or something? the code works perfectly. All I want to do is shorten it.

Bob Phillips
08-20-2008, 01:57 PM
I got confused between columns and checkboxes here. Which should it be, and what are ColumnCForm and so on?

TomSchreiner
08-20-2008, 02:58 PM
Just guessing that you are using activeX checkboxes. This code will hide all activeX checkboxes who's top left cell is located in a column greater than lastcolumn.

Sub ExampleCall()
Dim LastColumn As Integer

'code to determine last column

LastColumn = 6
Application.ScreenUpdating = False
Call HideCtrls(LastColumn)
End Sub

Sub HideCtrls(LastColumn As Integer)
Dim c As OLEObject

For Each c In ActiveSheet.OLEObjects
If TypeOf c.Object Is MSForms.CheckBox Then
If c.TopLeftCell.Column > LastColumn Then c.Visible = False
End If
Next
End Sub

Djblois
08-20-2008, 07:27 PM
I got cofused between columns and checkboxes here. Which should it be, and what are ColumnCForm and so on?

lol I created a form (the actual use of the form is for showing and hiding columns but that isn't what this code is for) What I have in the form is 58 checkboxes (each relates to a column - ColumnCForm is one checkbox that refers to the C column) Well, I want to hide all the checkboxes that refer to columns beyond the last column. So if the last column is H, I want to hide all the checkboxes that refer to I and on are hidden. THe code I have created works perfectly, however I think it is way too long - there must be a shorter way using a loop. I can think of hiding all them in code and then looping to unhide the checkboxes that I want visible but I don't know how to tell unhide the first checkbox then on the second loop unhide the second checkbox, and so on until the final column.

mikerickson
08-20-2008, 09:13 PM
This should hide the checkboxes for columns 3 through 8

Dim colStart As Long, colStop As Long
Dim colNum As Long
colStart = 3
colStop = 8

For colNum = colStart To colStop
ActiveSheet.Shapes("Column" & Left(Cells(1, colNum).Address(False, False), Len(Cells(1, colNum).Address(False, False)) - 1) & "form").Visible = False
Next colNum