Enable and disable checkbox

[ZakeerSharief]

Hi,I have say 10 checkbox in my form. In which i should be able to select only 2 checkboxes. Once 2 checkbox are selected then the remaining checkboxes should be disabled. And which ever checkboxes are selected, those checkboxes should fetch the data from the different excel sheets and place it in the 2 frames which are designed below.(Each frames consists of some more checkbox. But i am facing the issue when the checkbox are checked.

Please help me developing this.


Thanks

[Guido Geurs]

This code will disable the checkboxes when 2 of them are selected.
When one of the selected is unchecked: all the checkboxes are again available.

Private Sub CheckBox1_Change()
    If CheckBox1.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox2_Change()
    If CheckBox2.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox3_Change()
    If CheckBox3.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox4_Change()
    If CheckBox4.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub


Private Sub CheckBox5_Change()
    If CheckBox5.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox6_Change()
    If CheckBox6.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox7_Change()
    If CheckBox7.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox8_Change()
    If CheckBox8.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox9_Change()
    If CheckBox9.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub

Private Sub CheckBox10_Change()
    If CheckBox10.Value = False Then
        Call Enable_All
    Else
        Call Disable_All
    End If
End Sub
Private Sub Disable_All()
Dim CHECKEDcount As Integer
    If CheckBox1.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox2.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox3.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox4.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox5.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox6.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox7.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox8.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox9.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CheckBox10.Value = True Then CHECKEDcount = CHECKEDcount + 1
    If CHECKEDcount > 1 Then
        If CheckBox1.Value = False Then CheckBox1.Enabled = False
        If CheckBox2.Value = False Then CheckBox2.Enabled = False
        If CheckBox3.Value = False Then CheckBox3.Enabled = False
        If CheckBox4.Value = False Then CheckBox4.Enabled = False
        If CheckBox5.Value = False Then CheckBox5.Enabled = False
        If CheckBox6.Value = False Then CheckBox6.Enabled = False
        If CheckBox7.Value = False Then CheckBox7.Enabled = False
        If CheckBox8.Value = False Then CheckBox8.Enabled = False
        If CheckBox9.Value = False Then CheckBox9.Enabled = False
        If CheckBox10.Value = False Then CheckBox10.Enabled = False
    End If
End Sub
Private Sub Enable_All()
        CheckBox1.Enabled = True
        CheckBox2.Enabled = True
        CheckBox3.Enabled = True
        CheckBox4.Enabled = True
        CheckBox5.Enabled = True
        CheckBox6.Enabled = True
        CheckBox7.Enabled = True
        CheckBox8.Enabled = True
        CheckBox9.Enabled = True
        CheckBox10.Enabled = True
End Sub

[Killer42]

Here's a simpler version. To try it out, create a new form and put an array of checkboxes on it. Then paste this code into the code area of the form. (Control arrays make your code much shorter and simpler.)

Option Explicit
DefLng A-Z

' How many are currently checked.
Private NumChecked As Long

' Maximum number that may be checked before locking.
Private Const CheckLimit As Long = 2

Private Sub Check1_Click(Index As Integer)
  Dim I As Long
  If Check1(Index).Value Then
    NumChecked = NumChecked + 1
    If NumChecked >= CheckLimit Then
      For I = Check1.LBound To Check1.UBound
        Check1(I).Enabled = False
      Next
    End If
  Else
    NumChecked = NumChecked - 1
  End If
End Sub

[ZakeerSharief]

Thanks alot. This helped me solving the checkbox issue. But my other issue is that, Once 2 checkboxes are select i should get only data of the checkboxes selected in the frames below those 10 checkboxes.

For Example:Say 10 checkboxes are the name of the fruits, once i select 2 checkboxes i should get the data present in different sheet.

Please help me with this. And i have 2 frames in which there are checkboxes.

[Guido Geurs]

First a question: in which version are you working?
Because my solution was for Office 2003 (no indexes can be used in VBA)
or Office 2008, 2010, ...?

To get the data in the form: put the data from the sheets (columns) in an array and paste the array in the form.

Is it possible to attach your workbook in Bytes?
If it's to large, just a part of the data so we can see what your layout is and we can work on the same sheets with data.
If the information on the sheets is confidential, please enter fictive data!

[ZakeerSharief]

Hi. I am working on Office 2010. I didnt understand how to make use of array in this case. Yes the data confidential. I will try giving you an example, you can help me to solve this issue. I will attach the file after this mail.

[ZakeerSharief]

I have designed a simple form, which has 5 different branded clothes. Once i select 2 brands and click "Next" button, it will show the frame bellow and gets the data from the excel sheet. But i have only written the code for the first 2 check boxes. I can select any number of options bellow and click on "Go" button, in the next sheet i should to that particular and get the data. May this would help you to understand my issue. I know its a very simple code. Please help me doing this.

I am attaching a file with this reply

[Guido Geurs]

The checkboxes in the frames, is this for the columns to select?
Attached is a first solution.
Is this OK so far for this call?
If so, and you want more help on this project, please open an other call with an other name like "transfer data from sheets" or something like that.
This to prevent that a whole project with different problems is classified under one call with name "Enable and disable checkbox"

[ZakeerSharief]

Hi. Thanks alot, it really helped me. But i have one more issue now. You remember i had sent a file from where the data is coming from different sheets. I am unable to do that. Because once the data in the first sheet changes, it should also change in the next sheet. I mean once i click on button "Go" it should get the data in the next form. Where in the selection in Frame_1_1 and Frame_1_2 can be multiple or can be individually selected. Please provide me some soultion to it.

Thanks

[ZakeerSharief]

Hi. I have to another doubt. I have a file where the data is scattered. I have to map that data into flat format, so that it could be uploaded in the server. Doing that manually will take much time. Is it possible for you to provide any solution for this.

Thanks and Regards,
Zakeer

[ZakeerSharief]

I am sorry i didnt read your mail properly. As i was in a hurry. And i am really very sorry for replying late. And thank you very for support.

[ZakeerSharief]

Hi, My other doubt is i have a invoice where the data is from different sheets. In the first sheet i have total price in each module. And going forward i have different sheets where each module is break down and providing the amount for each module in detail. I have collect the data from all the sheets and populate in a single sheet, which would be flat file with all the details.

Please help me doing this.

If required i will send a example file.

[Guido Geurs]

Please open an other call with an other name like "transfer data from sheets" or something like that.
This to prevent that a whole project with different problems is classified under one call with name "Enable and disable checkbox".

In the meantime I will see what I can do.

Is it possible to attach an example of the sheets with data (e.v. with fictive values) and an example of the result you want?

[ZakeerSharief]

Tranfer data between Sheets

Hi. Thanks alot, it really helped me. But i have one more issue now. You remember i had sent a file from where the data is coming from different sheets. I am unable to do that. Because once the data in the first sheet changes, it should also change in the next sheet. I mean once i click on button "Go" it should get the data in the next form. Where in the selection in Frame_1_1 and Frame_1_2 can be multiple or can be individually selected. Please provide me some soultion to it.

[ZakeerSharief]

Yes sure. I will provide the example file. I shall attach that with the next mail, which i will send you.