Use a cell value to define a range to hide columns

[Roger1456]

1 down vote favorite

I have a spreadsheet that there is a checkbox the purpose of the checkbox is to hide the name of clients in two adjacent columns. Because the spreadsheet changes from time to time the position of the columns changes thus it is currently P:Q but a year ago it was H:I. I want to store the 'range' in a cell and reference that from my vba and get that to hide the columns. The checkbox is a simple toggle. I have tried various incarnations without success and my latest effort tells me that I have not se up the range properly. The cell I am using for the range is F4. <code>The checkbox is on the same worksheet as the columns and the cell in question it is not in a form of any kind The sheet name if it helps is "Client activity 2016" and it is Sheet 9</code>The code is currently:

<tbody>
</tbody>

<code>Private Sub CheckBox2_Click()

Dim c As Range
Dim Visy As Integer
Dim My_range As String

'My_range is the range of filled rows stored as a range in cell F4
'Visy stores the state of the checkbox

If CheckBox2.Value = True Then
Visy = 1
Else
Visy = 0
End If

'Stop any use of the spread sheet and set variable initial states
Application.EnableEvents = False

My_range = Sheet9.Cells(4, 6).Value

'Hide the columns
Range(My_range).Hidden = Visy

'These lines have been tried but fail to work or compile
'Sheet9.colums(My_range).Hidden = True

'Re enable application
On Error GoTo 0
Application.EnableEvents = True

End Sub

Any help at all greatly recieved.
</code>

 

[JLGWhiz]

Not sure I grasp the whole bit, but you could name your columns that you want to hide or unhide as a range. ie. Select columns P:Q and in the Name Manager, add your preferred name for that range. Then you can just use code like: Range("preferredName").EntireColumn.Hidden = True, with preferredName being a name you used. The refersto range will change automatically if you add or delete columns on either side of P:Q. Won't hurt to try it.

 

[Roger1456]

After a lot of work. I have it working and I post my solution for reference to others here. Context was the main issue and it is important and I was mixing private sub and sub and so had a scope problem when it came to ranges. I also from JLGWhiz the suggestion (thanks!) to use a named range rather than read a cell value since the columns were always adjacent. I have published the code below in case it is of value to anyone in the future.

Private Sub CheckBox2_Click()
'Requires ClientNameCol to be set to the range to be hidden
Dim Visy As Boolean

'Stop any use of the spread sheet and set variable initial states
Application.EnableEvents = False

'Check if sheet is to be hidden or not

If Worksheets("Client 16").CheckBox2.Value = True Then
Visy = True
Else
Visy = False
End If

'Hide/unhide the columns
With ThisWorkbook
.Worksheets("Client 16").Range("ClientNameCol").EntireColumn.Hidden = Visy
End With


On Error GoTo 0
Application.EnableEvents = True


End Sub

 

[JLGWhiz]

After a lot of work. I have it working and I post my solution for reference to others here. Context was the main issue and it is important and I was mixing private sub and sub and so had a scope problem when it came to ranges. I also from JLGWhiz the suggestion (thanks!) to use a named range rather than read a cell value since the columns were always adjacent. I have published the code below in case it is of value to anyone in the future.


End Sub

Happy to help,
regards, JLG