CODEWITHSUNDEEP
excelvbacheckboxuserform
Nikky
Nikky

Reputation: 3

If a checkbox is checked on a userform, how do I change the value shown in a cell?

I am trying to write code for a userform in VBA that takes the input of checkboxes and pushes the value to a worksheet with the value "Yes/No" instead of TRUE/FALSE. This userform is meant to be able to run multiple times and add to a new row in the worksheet instead of just changing a single cell value every time.

I have tried creating a String variable that if the value is TRUE, the string will be "Yes", and change the cbProd.Value to "Yes" instead of TRUE. And similarly for FALSE. Not sure if I did it wrong or if I am approaching it wrong.

Private Sub cbProd_Click()

    Dim cbProdcut As String

    If cbProd.Value = True Then
       cbProduct = "Yes"
       cbProd.Value = cbProduct
    Else
       cbProduct = "No"
       cbProd.Value = cbProduct
    End If

End Sub

Private Sub InsertRow()

    With Sheet1

        ' Get the current row
        Dim i As Long
        Dim curRow As Long

        If .Range("A4") = "" Then
            curRow = 4
        Else
            curRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        End If

        ' Add item to row
        .Cells(curRow, 6) = cbProd.Value

    End With

End Sub

I expect the output to be "Yes" in the cell except that it just comes out blank. If I don't change the cbProd.Value to be the new string value it at least prints TRUE/FALSE. After changing the value it doesn't print anything.

Upvotes: 0

Views: 4032

Answers (1)

Bilal
Bilal

Reputation: 357

You can't change checkbox value to other than boolean, try changing it's Tag property then read it back in your InsertRow sub

i.e.

If cbProd.Value = True Then cbProd.Tag = "Yes" Else cbProd.Tag = "No"

and then read it like

.Cells(curRow, 6) = cbProd.Tag

Upvotes: 2

Related Questions