Search code examples
vbaexcelcheckboxuserform

Chechbox inserting value into specific cell dynamically

I found this code for inserting values from a checkbox in an Excel Userform: 

Dim rw As Integer
Dim ws As Worksheet 
Set ws = Worksheets("Sheet1")
If Checkbox1.Value = True Then
ws.Cells(rw, 2).Value = "X" 
Else
ws.Cells(rw, 2).Value = "" 
End If

It works just great! But now i need it in another userform where i look up a value from a ComboBox, where information connected to the specific number in the ComboBox will show up in the userform. The user is able to save new information to this specific number, and here i cant just use the code shown above? 

Private Sub pSave()

Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

If blnSearch = True Then

    'Unlocking the sheet
    Sheets("Sheet1").Unprotect Password:="*********"

    totRows = Worksheets("Sheet1").Range("A3").CurrentRegion.Rows.Count

    For i = 2 To totRows
        If Trim(Worksheets("Sheet1").Cells(i, 1)) = Trim(ComboBox_ID.Text) Then
            Worksheets("Sheet1").Cells(i, 1).Value = TextBox_ID.Text
            'Worksheets("Sheet1").Cells(i, 2).Value = CheckBox_Eldesign.Value

I found that just inserting the checkbox the same way as the other boxes in the userform worked, but I cant figure out how to determine what the checkbox is going to write in the cell as i can with the other code?

In advance, thank you for your time and help!

Solution

  • Multiple ways to solve this:

    • the way you already have (the line you commented out) is good. You can then use simple conditional formatting in excel. If the cell value is true, it should show an X. If it is false, it should show an empty cell.

    • Reuse the same code as the upper code snippet you provided. Instead of the commented out line, just insert the parts between the If and the End If. Also, don’t forget that multi-line Then statements require you to end by an End If.

        If Trim(...) = Trim(...) Then

  Worksheets(...) = TextBox_ID.Text

  With Worksheets(...).Cells(i,2)

      If CheckBox_Eldesign.Value Then
             .Value = “X”
      Else
             .Value = “”
      End if

  End With

  End If

    (I cant make proper formatting from my phone, apologies for that...)