I have two sheets sheet1 = "output" and sheet2= "input". In sheet2 there are multiple checkbox assign to some certain variable. for example:
B | C | |
---|---|---|
1 | flow | checkbox1 |
2 | speed | checkbox2 |
3 | size | checkbox3 |
4 | current | checkbox4 |
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:
B | |
---|---|
1 | XYZ |
2 | flow |
3 | blank |
4 | size |
5 | current |
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