Multiple CheckBoxes can be selected require additional info if sel

G

Goldenfoot

I have a spreadsheet to collect address information. Below the address I
have 4 checkboxes to describe the address. User can only select a max of 3
from the list.
CKB#6-Payments
CKB#7-POs (only if not Standard Override)
CKB#8-Standard Override PO (only if not Pos)
CKB#9-Contracts
I have rows below the checkboxes which hide and unhide depending on what the
address is used for.
Below is the Code for CKB#7:
Private Sub CheckBox7_Click() 'To Send Purchase
If CheckBox7.Value = True Then
Select Case CheckBox7.Value
Case True
If CheckBox7.Value = True Then
CheckBox8.Value = False
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = True
CheckBox16.Visible = True
CheckBox17.Visible = True
CheckBox18.Visible = True
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Purchase Orders. Please complete the following:"
ActiveSheet.Range("M21") = Range("C24")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox7.Value = False Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = True Then
CheckBox11.Visible = True
CheckBox12.Visible = True
CheckBox13.Visible = True
CheckBox14.Visible = True
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Contracts. Please complete the following:"
ActiveSheet.Range("M21") = Range("C25")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = False Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If

Case False
If CheckBox7.Value = False Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox6.Value = True Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("32:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Range("B30") = "What do you want to do next?"
ActiveSheet.Range("M21") = Range("C24")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox6.Value = False Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = True
CheckBox16.Visible = True
CheckBox17.Visible = True
CheckBox18.Visible = True
ActiveSheet.Unprotect "Test"
Rows("32:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = True Then
CheckBox11.Visible = True
CheckBox12.Visible = True
CheckBox13.Visible = True
CheckBox14.Visible = True
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Contracts. Please complete the following:"
ActiveSheet.Range("M21") = Range("C25")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = False Then
CheckBox11.Visible = False
CheckBox12.Visible = False
CheckBox13.Visible = False
CheckBox14.Visible = False
CheckBox15.Visible = False
CheckBox16.Visible = False
CheckBox17.Visible = False
CheckBox18.Visible = False
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
End Select
End If
End Sub

Any help is greatly appreciated.
Goldenfoot
 
S

Susan

here's how i would do it (not a guru!).

set all your textboxes as visible=false to begin with in an auto_open
sub, but make sure the checkboxes 6-9 that you need the user to choose
from are visible. then you only have to qualify the ones that you
want to change to visible........ (it would shorten your code quite a
bit):
If CheckBox7.Value = True Then
CheckBox15.Visible = True
CheckBox16.Visible = True
CheckBox17.Visible = True
CheckBox18.Visible = True
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Purchase Orders. Please complete the following:"
ActiveSheet.Range("M21") = Range("C24")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox7.Value = False Then
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = True Then
CheckBox11.Visible = True
CheckBox12.Visible = True
CheckBox13.Visible = True
CheckBox14.Visible = True
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Contracts. Please complete the following:"
ActiveSheet.Range("M21") = Range("C25")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = False Then
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If

Case False
If CheckBox7.Value = False Then
ActiveSheet.Unprotect "Test"
Rows("32:46").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox6.Value = True Then
ActiveSheet.Unprotect "Test"
Rows("32:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Range("B30") = "What do you want to do next?"
ActiveSheet.Range("M21") = Range("C24")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox6.Value = False Then
CheckBox15.Visible = True
CheckBox16.Visible = True
CheckBox17.Visible = True
CheckBox18.Visible = True
ActiveSheet.Unprotect "Test"
Rows("32:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = True Then
CheckBox11.Visible = True
CheckBox12.Visible = True
CheckBox13.Visible = True
CheckBox14.Visible = True
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Range("B30") = "Additional Information is needed
for Contracts. Please complete the following:"
ActiveSheet.Range("M21") = Range("C25")
ActiveSheet.Protect "Test"
Range("e19").Select
End If
If CheckBox9.Value = False Then
ActiveSheet.Unprotect "Test"
Rows("47:61").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect "Test"
Range("e19").Select
End If
End Select
End If
End Sub
Click to expand...

you might also be able to use the tag property of the checkbox to
shorten it up more, but my brain can't get around that at the
moment.........
:)
hth!
susan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Togglebutton to change Checkboxes 3
Unhiding Check Boxes in Excel 2002 1
Is there an easier way of doing this?? 0
End without Next Error 12
Refering to new sheet name 3
Looping 6
Slow Macro Issue 1
Print selection using check boxes 17

Top