VBA code with checkbox and Combobox needed

[Ashwinghan]

Hello,

I have three excel sheets in a workbook. The first sheet is kind of a form with Active X controls(ComboBox and CheckBoxes). The second sheet is the main data sheet which can be viewed in the attached file.


I need a code where in If I select Room name b or any room in the ComboBox and turn on the checkBoxes for Furniture or Dimension or Gases, The corresponding values for that room should be copied to 3rd row of Sheet 3 as in the attached file. No matter what selection I make in the Room name, the room name and the checked boxes values should always be copied to row 3 on Sheet 3. I have started with a basic code but I need to improvise this. (i.e instead of having an if statement for every value, How can I have a single if statement so that It would work for more rows and columns too).

Here is the code which has to be optimized. You can also look it up in the attached file.

Private Sub CheckBox1_Click()
  If ComboBox1.Value = "a" And CheckBox1.Value = True Then
  Sheets("Sheet1").Range("D3").Copy Sheets("Sheet3").Range("D3")
    End If
    If ComboBox1.Value = "b" And CheckBox1.Value = True Then
  Sheets("Sheet1").Range("D4").Copy Sheets("Sheet3").Range("D3")
    End If
    If ComboBox1.Value = "c" And CheckBox1.Value = True Then
  Sheets("Sheet1").Range("D5").Copy Sheets("Sheet3").Range("D3")
    End If
    If ComboBox1.Value = "d" And CheckBox1.Value = True Then
  Sheets("Sheet1").Range("D6").Copy Sheets("Sheet3").Range("D3")
    End If
    If ComboBox1.Value = "e" And CheckBox1.Value = True Then
  Sheets("Sheet1").Range("D7").Copy Sheets("Sheet3").Range("D3")
    End If
   
    If CheckBox1.Value = False Then
        Sheets("Sheet3").Range("D3").Delete
    End If
End Sub

Looking forward for your help


Thanks
Ashwin

 

[NARAYANK991]

Hi Ashwin ,

Can you explain what logic you wish to implement ?

1. What should happen when a room selection is done ? What should happen if the selected room is a , if it is b , if it is c , and so on ?

2. There are 16 checkboxes in the worksheet ; what should happen if each of them is checked ? What should happen if any of them is checked ?

Narayan

 

[Ashwinghan]

Hi Narayan,

1. Sheet2 is completely based on Sheet1. If I select Room Name a or b or c or so on and select any checkbox. The corresponding values(checked boxes) from that Row of the Roomname a or b or c etc in Sheet 1 should be copied to Sheet number 3 as it does in the worksheet.

2. If you check on each or any checkboxes and click on submit you can observe that those values of the checkboxes(which are the Values of Sheet1) are copied to sheet3.

The same should happen, but the problem is I need a logic for the code instead of having the If condition for 5 times. This is because I will have more number of Room names and more number of columns in my actual sheet. So there will be more checkboxes.

I hope you got my point.

Thanks
Ashwin