I have two sheets sheet1 = "output" and sheet2= "input". In sheet2 there are multiple checkbox assign to some certain variable. for example:

Now what I want is if any checkbox is On then write its variable name to Sheet1 at specific cell reference else leave the cell blank

Let us say that if checkbox1, checkbox3 and checkbox4 is On then write "flow", "size" and "current" in cell B2, B4, B5 of sheet1 and write "XYZ" in cell B1. The sheet1 = output should look like:

So far what i defined:

Sub chart()

Dim s1 As Worksheet, s2 As Worksheet
Dim check1 As Boolean, check2 As Boolean, check3 As Boolean, check4 As Boolean

Set s1 = ThisWorkbook.Worksheets(1)
Set s2 = ThisWorkbook.Worksheets(2)

check1 = s2.CHECKBOXES("Check Box 1").Value = xlOn
check2 = s2.CHECKBOXES("Check Box 2").Value = xlOn
check3 = s2.CHECKBOXES("Check Box 3").Value = xlOn
check4 = s2.CHECKBOXES("Check Box 4").Value = xlOn

End Sub`

Now i want to write if condition.

CodePudding user response：

You can do something like this (assuming checkboxes are in ws2):

sub code()
Dim ws as worksheet: set ws = Thisworkbook.Worksheets("input")
Dim ws2 as worksheet: set ws2 = Thisworkbook.Worksheets("output")

   With ws2
      If .OLEObjects("checkbox1").Object.Value = True And _
         .OLEObjects("checkbox2").Object.Value = True And _
         .OLEObjects("checkbox3").Object.Value = True Then

          ws.Range("B1").Value = "current"
          ws.Range("B2").Value = "flow"
          ws.Range("B2").Value = "size"
          ws.Range("B2").Value = "current"
      end if
   end with      
end sub

CodePudding user response：

Please, test the next code. It works for Form check boxes type and needs a correspondence between the text box name and the word in B:B to be returned. I am talking about their name (in the left address text box), not their caption (obtained by editing of the automated allocated one). I this way, you can use as many check boxes you want. Only to have their name correlate with the row where the string to be returned exists:

Sub WriteOnChckBoxFVal()
   Dim sh1 As Worksheet, sh2 As Worksheet, lastR As Long, arr, arrFin, i As Long
   
   Set sh1 = ActiveSheet 'ThisWorkbook.Worksheets(1)
   Set sh2 = sh1.Next     'ThisWorkbook.Worksheets(2)
   lastR = sh1.Range("B" & sh1.rows.count).End(xlUp).Row
   arr = sh1.Range("B2:B" & lastR).Value2
   ReDim arrFin(1 To UBound(arr), 1 To 1)
   
   For i = 1 To UBound(arr)
        If sh1.CheckBoxes("Check Box " & i).value = 1 Then
            arrFin(i, 1) = arr(i, 1)
        Else
            arrFin(i, 1) = "XYZ"
        End If
   Next i
   'drop the array content at once:
   sh2.Range("B2").Resize(UBound(arrFin), 1).value = arrFin
End Sub