MrExcel.com - Your One Stop for Excel Tips & Solutions

hiding a column

Posted by steve on March 13, 2001 6:09 PM


I'm using the following code to hide a column, if I manually type in true or false in I1 it works fine, but I'm trying to have a checkbox linked to I1 to toggle between true and false.But it will not work that way.

Can someone please tell me what I'm doing wrong.

Thanks steve

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I1") = True Then
Range("D2").Select
Selection.EntireColumn.Hidden = True
End If
If Range("I1") = False Then
Range("D2").Select
Selection.EntireColumn.Hidden = False
End If
End Sub

Posted by ml on March 13, 2001 6:43 PM

Try this:

If Range("I1").Value then
Columns(4).Hidden = True
Else
Columns(4).Hidden = False
End If

Posted by steve on March 13, 2001 7:12 PM

Posted by as on March 13, 2001 7:20 PM

Hi! I copied this from a worksheet that hilights entire rows if a checkbox is marked. Hope this helps.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Sheets(1).CheckBox1 Then
Target.EntireRow.Interior.ColorIndex = 6
End If

Sheet1 has only a rectangle with checkmark in it. Try this:

Posted by steve on March 13, 2001 8:24 PM

I have a question, how are you telling what range you want to be highlighted.

Thanks steve

Posted by Dave Hawley on March 14, 2001 7:30 AM

Events not firing!

I have a question, how are you telling what range you want to be highlighted.

Steve I don't think the Check bos will trigger the Change event. Use a CheckBox from the Control Toolbox and put this code in it:


Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Columns(2).EntireColumn.Hidden = True
Else
Columns(2).EntireColumn.Hidden = False
End If
End Sub

Dave

: Hi! I copied this from a worksheet that hilights entire rows if a checkbox is marked. Hope this helps. : Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) : If Sheets(1).CheckBox1 Then : Target.EntireRow.Interior.ColorIndex = 6 : End If : Sheet1 has only a rectangle with checkmark in it.

OzGrid Business Applications

Posted by steve on March 14, 2001 8:48 AM

Re: Events not firing!( I have 1 question for you)

Steve I don't think the Check bos will trigger the Change event. Use a CheckBox from the Control Toolbox and put this code in it: Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Columns(2).EntireColumn.Hidden = True Else Columns(2).EntireColumn.Hidden = False

Dave

Hi, dave thanks for the help it works, but how can I make it work if I want to protect the sheet. It just says unable to set hidden property out of range.

Thanks once again for helping me out.

steve

Posted by Dave Hawley on March 14, 2001 9:23 AM

Re: Events not firing!( I have 1 question for you)

: Steve I don't think the Check bos will trigger the Change event. Use a CheckBox from the Control Toolbox and put this code in it


Steve, try this code. Just change "secret" to your password.

Private Sub CheckBox1_Click()
Me.Unprotect Password:="secret"
If CheckBox1.Value = True Then
Columns(2).EntireColumn.Hidden = True
Else
Columns(2).EntireColumn.Hidden = False
End If
Me.Protect Password:="secret"
End Sub


Dave

OzGrid Business Applications