Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Multiple CheckBoxes can be selected require additional info if sel
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,117
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating multiple checkboxes in a cell Pat Setting up and Configuration of Excel 4 January 26th 07 06:36 PM
Adding multiple checkboxes timmeah4 Excel Discussion (Misc queries) 7 December 6th 06 02:48 PM
Add additional info to multiple fields autobiz Excel Worksheet Functions 2 January 27th 06 07:10 PM
Multiple Checkboxes Shortcut? Jason Excel Discussion (Misc queries) 1 October 18th 05 08:08 PM
Repost with Additional Info Sandy Excel Discussion (Misc queries) 1 September 15th 05 02:06 PM


All times are GMT +1. The time now is 06:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement