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

Checkbox value = false to activate macro or code????

Posted by Rick M on July 09, 2001 12:14 PM

I am setting up a form and would like the user to indicate which columns they need to see by clicking check boxes on the opening page. If certain checkboxes have a value of "true" when the user hits the "submit macro button" the macro hides all the columns EXCEPT the ones where the chechbox has a value of "true".
I tried this code to no avail:

If checkbox1.value = false then
columns("E:E").select
Selection.entireColumn.Hidden = True
Range ("B8").select
End if
End Sub

I assigned this code to my "submit" button.

Any help is appreciated!

Posted by Ben O. on July 09, 2001 12:27 PM

Try this code, Rick. It first hides all columns, then unhides column E (5) if the checkbox is checked:

Private Sub Submit_Click()
Columns().Hidden = True
If CheckBox1.Value = True Then
Columns(5).Hidden = False
End If
Unload UserForm1
End Sub


-Ben

Posted by RIck M on July 09, 2001 12:38 PM

I am not using a "user form". The check boxes are in sheet1 and the columns I am hiding are on sheet2. By the way there are multiple check boxes on sheet1 for the user to select. Would the same principle work and just keep writing the same code but substitute check1 for 2 for 3 etc...
Once the user has selected all the columns they need and hit submit then sheet2 is revealed only showing the needed(selected) columns.

Posted by Ben O. on July 09, 2001 12:54 PM

Rick,

Yes, you can use the repeat the same code for each column, just changing the number for each one. Your code could be something like this:

If CheckBox1.Value = True Then
Sheets(2).Columns(1).Hidden = False
End If

I tried to make a For To look using CheckBox(x), but that didn't work.

-Ben

Posted by Rick M on July 09, 2001 2:14 PM

As soon as the code find a "false" checkbox it ends if.
I have 6 checkboxes.
Lets say
check1= true
check2=false
check3=true
check4=true
check5= false
check6=true
The code will only "unhide" the column associated with check1..... it ignores check3,check4 and check6.....

Posted by Ben O. on July 09, 2001 2:41 PM

I had some trouble getting to to work too. Then I realized I didn't change all of the numbers I had to change. Finally, I was able to fix whatever problems there were. See if this code works for you. It works for me.

Private Sub CommandButton1_Click()

If CheckBox1.Value = True Then
Sheets(2).Columns("A").Hidden = False
End If

If CheckBox2.Value = True Then
Sheets(2).Columns("B").Hidden = False
End If

If CheckBox3.Value = True Then
Sheets(2).Columns("C").Hidden = False
End If

If CheckBox4.Value = True Then
Sheets(2).Columns("D").Hidden = False
End If

If CheckBox5.Value = True Then
Sheets(2).Columns("E").Hidden = False
End If

If CheckBox6.Value = True Then
Sheets(2).Columns("F").Hidden = False
End If

End Sub


-Ben

Posted by Rick M on July 09, 2001 3:15 PM

Thanks - that worked

Private Sub CommandButton1_Click() Sheets(2).Columns("A").Hidden = False If CheckBox2.Value = True Then Sheets(2).Columns("B").Hidden = False If CheckBox3.Value = True Then Sheets(2).Columns("C").Hidden = False If CheckBox4.Value = True Then Sheets(2).Columns("D").Hidden = False If CheckBox5.Value = True Then Sheets(2).Columns("E").Hidden = False If CheckBox6.Value = True Then Sheets(2).Columns("F").Hidden = False End Sub