Multiple CheckBoxes can be selected require additional info if sel
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 |
Multiple CheckBoxes can be selected require additional info if sel
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 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 On Apr 2, 4:50 pm, Goldenfoot wrote: 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 |
All times are GMT +1. The time now is 07:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com