MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Group naming Check boxes


Posted by STEVE on March 24, 2001 11:29 PM

I tryed group naming option buttons to and they work fine letting there only be one active at a time. I then decided to try check boxes, it didn't work. Does anyone know if you can group them like you do the option.

Thanks steve


Posted by Dave Hawley on March 25, 2001 3:19 AM

Hi Steve

Basically No! At least not without some VBA coding. CheckBoxes are used to allow a user more than one choice, so if you want the user to have multiple choices (of Sheet names ?) use a ListBox with the "MultiSelect" property set to "1-FmMultiSelectMulti" and use this code to fill the list box.

Private Sub ListBox1_GotFocus()
Dim Sht As Worksheet
On Error Resume Next
ListBox1.Clear
On Error GoTo 0
For Each Sht In ThisWorkbook.Worksheets
ListBox1.AddItem (Sht.Name)
Next Sht
End Sub

Dave

OzGrid Business Applications

Posted by steve on March 25, 2001 9:40 AM

Dave
heres what I did I first placed option buttons on screen, then I placed the check boxs used from the forms tool bar on the screen, you then take the first option and checkbox and link them to the same cell(A1), then seconds to(A2), thirds to (A3).... I then took the part of the code you gave me and placed it into the worksheet code. First problem was that it did not reference each option buttons group name untel each were clicked. I'm trying to fire it off the worksheet change, but sometimes it will place a space in front of the group name when its not there, then they don't work. The cells in range A1-A3 have formulas in them I'm thinking this might be the problem.

Thanks for any input you may have
steve

Here my code

Private Sub Worksheet_change(ByVal Target As Range)
Dim GrpNme0 As String
Dim GrpNme1 As String
Dim GrpNme2 As String
Dim GrpNme3 As String
GrpNme0 = Range("A1")
GrpNme1 = Range("A2")
GrpNme2 = Range("A3")
GrpNme3 = Range("A4")
OptionButton1.GroupName = GrpNme0
OptionButton2.GroupName = GrpNme1
OptionButton3.GroupName = GrpNme2
OptionButton4.GroupName = GrpNme3

End Sub