How to make checkbox return yes or no instead of true and false

G

GingerBeardo

New Member
Joined
Feb 20, 2019
Messages
25
Working with some checkboxes in a user form, when hitting submit and the info hits the sheet they are showing TRUE or FALSE, I need Yes or No and when, yes being the box in the form was checked and no if it is not checked. Below is the code for what the submit button is doing. If something doesn't quite make sense or seems a convoluted way of doing something it's because I don't really know what I am doing, it's what I've been able to make fit.

VBA Code: 
Private Sub cmdSubmit_Click()

    Dim ws As Worksheet
    Dim freeRow As Long
    
             
        If cmbPosition.Text = "" Or txtFName.Text = "" Or txtLName.Text = "" Or txtDOB.Text = "" Or txtPhone.Text = "" Or txtLast4.Text = "" Then
    
             Else
                Set ws = ActiveSheet
                    freeRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1

             If Not ws Is Nothing Then
                ws.Cells(freeRow, 2) = cmbPosition.Text
                ws.Cells(freeRow, 3) = txtFName.Text
                ws.Cells(freeRow, 4) = txtLName.Text
                ws.Cells(freeRow, 5) = txtDOB.Text
                ws.Cells(freeRow, 6) = txtLast4.Text
                ws.Cells(freeRow, 7) = txtPhone.Text
                ws.Cells(freeRow, 9) = cbPEC.Value
                ws.Cells(freeRow, 10) = cbH2S.Value
                ws.Cells(freeRow, 11) = cbCS.Value
                ws.Cells(freeRow, 12) = cbFT.Value
                ws.Cells(freeRow, 13) = cbFA.Value
                ws.Cells(freeRow, 14) = cbCPR.Value
                ws.Cells(freeRow, 15) = cbBBP.Value
                ws.Cells(freeRow, 16) = cbFS.Value
                ThisWorkbook.Save
                cmbPosition.Text = ""
                txtFName.Text = ""
                txtLName.Text = ""
                txtDOB.Text = ""
                txtPhone.Text = ""
                txtLast4.Text = ""
                cbPEC.Value = ""
                cbH2S.Value = ""
                cbCS.Value = ""
                cbFT.Value = ""
                cbFA.Value = ""
                cbCPR.Value = ""
                cbBBP.Value = ""
                cbFS.Value = ""
                cmbPosition.SetFocus
            
            End If
        End If
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
You could just change all the values afterwards (before the save) with this loop of code:
VBA Code: 
For i = 9 To 16
 If ws.Cells(freeRow, i) Then
   ws.Cells(freeRow, i) = "Yes"
 Else
   ws.Cells(freeRow, i) = "No"
 End If
Next i
 
Upvote 0
Solution
GingerBeardo said:
Working with some checkboxes in a user form, when hitting submit and the info hits the sheet they are showing TRUE or FALSE, I need Yes or No and when, yes being the box in the form was checked and no if it is not checked.
Click to expand...

Create a small function:
VBA Code: 
Function CBYesNo(CBState As Boolean) As String
    If CBState Then
        CBState = "Yes"
    Else
        CBState = "No"
    End If
End Function

Put it in the same code module as your other function and use it in your code like this:
VBA Code: 
    ws.Cells(freeRow, 9) = CBYesNo(cbPEC.Value)
    ws.Cells(freeRow, 10) = CBYesNo(cbH2S.Value)
    ws.Cells(freeRow, 11) = CBYesNo(cbCS.Value)
    ws.Cells(freeRow, 12) = CBYesNo(cbFT.Value)
    ws.Cells(freeRow, 13) = CBYesNo(cbFA.Value)
    ws.Cells(freeRow, 14) = CBYesNo(cbCPR.Value)
    ws.Cells(freeRow, 15) = CBYesNo(cbBBP.Value)
    ws.Cells(freeRow, 16) = CBYesNo(cbFS.Value)
 
Upvote 0
rlv01 said:
Create a small function:
VBA Code: 
Function CBYesNo(CBState As Boolean) As String
    If CBState Then
        CBState = "Yes"
    Else
        CBState = "No"
    End If
End Function

Put it in the same code module as your other function and use it in your code like this:
VBA Code: 
    ws.Cells(freeRow, 9) = CBYesNo(cbPEC.Value)
    ws.Cells(freeRow, 10) = CBYesNo(cbH2S.Value)
    ws.Cells(freeRow, 11) = CBYesNo(cbCS.Value)
    ws.Cells(freeRow, 12) = CBYesNo(cbFT.Value)
    ws.Cells(freeRow, 13) = CBYesNo(cbFA.Value)
    ws.Cells(freeRow, 14) = CBYesNo(cbCPR.Value)
    ws.Cells(freeRow, 15) = CBYesNo(cbBBP.Value)
    ws.Cells(freeRow, 16) = CBYesNo(cbFS.Valu
[QUOTE="offthelip, post: 5768325, member: 419487"]
You could just change all the values afterwards (before the save)  with this loop of code:
[CODE=vba]
For i = 9 To 16
 If ws.Cells(freeRow, i) Then
   ws.Cells(freeRow, i) = "Yes"
 Else
   ws.Cells(freeRow, i) = "No"
 End If
Next i
Click to expand...
I tried this out and it worked, except that the check marks dont clear after hitting submit, they turn grey and count as not being checked. How can I change this and clear the checkboxes?
[/QUOTE]
 
Upvote 0
Can you post your code you are using to clear them?
 
Upvote 0
rlv01 said:
Create a small function:
VBA Code: 
Function CBYesNo(CBState As Boolean) As String
    If CBState Then
        CBState = "Yes"
    Else
        CBState = "No"
    End If
End Function

Put it in the same code module as your other function and use it in your code like this:
VBA Code: 
    ws.Cells(freeRow, 9) = CBYesNo(cbPEC.Value)
    ws.Cells(freeRow, 10) = CBYesNo(cbH2S.Value)
    ws.Cells(freeRow, 11) = CBYesNo(cbCS.Value)
    ws.Cells(freeRow, 12) = CBYesNo(cbFT.Value)
    ws.Cells(freeRow, 13) = CBYesNo(cbFA.Value)
    ws.Cells(freeRow, 14) = CBYesNo(cbCPR.Value)
    ws.Cells(freeRow, 15) = CBYesNo(cbBBP.Value)
    ws.Cells(freeRow, 16) = CBYesNo(cbFS.Value)
Click to expand...
I tried this one and I don't believe I placed it correctly, I keep getting errors for "type mismatch" and "expected sub".
 
Upvote 0
rlv01 said:
Can you post your code you are using to clear them?
Click to expand...
here is everything with the suggestion that worked, minus the grey cb marks after submitting form input.

VBA Code: 
Private Sub UserForm_Initialize()
With cmbPosition
    .AddItem "Trainee"
    .AddItem "Operator I"
    .AddItem "Operator II"
    .AddItem "Operator III"
End With

End Sub
Private Sub cmdSubmit_Click()

    Dim ws As Worksheet
    Dim freeRow As Long
   
            
        If cmbPosition.Text = "" Or txtFName.Text = "" Or txtLName.Text = "" Or txtDOB.Text = "" Or txtPhone.Text = "" Or txtLast4.Text = "" Then
   
             Else
                Set ws = ActiveSheet
                    freeRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1 'End(xlUp) jumps to the next free cell

             If Not ws Is Nothing Then
                ws.Cells(freeRow, 2) = cmbPosition.Text
                ws.Cells(freeRow, 3) = txtFName.Text
                ws.Cells(freeRow, 4) = txtLName.Text
                ws.Cells(freeRow, 5) = txtDOB.Text
                ws.Cells(freeRow, 6) = txtLast4.Text
                ws.Cells(freeRow, 7) = txtPhone.Text
                ws.Cells(freeRow, 9) = cbPEC.Value
                ws.Cells(freeRow, 10) = cbH2S.Value
                ws.Cells(freeRow, 11) = cbCS.Value
                ws.Cells(freeRow, 12) = cbFT.Value
                ws.Cells(freeRow, 13) = cbFA.Value
                ws.Cells(freeRow, 14) = cbCPR.Value
                ws.Cells(freeRow, 15) = cbBBP.Value
                ws.Cells(freeRow, 16) = cbFS.Value
               
            For i = 9 To 16
                   If ws.Cells(freeRow, i) Then
                        ws.Cells(freeRow, i) = "Yes"
                Else
                        ws.Cells(freeRow, i) = "No"
                End If
                Next i
               
                ThisWorkbook.Save
                cmbPosition.Text = ""
                txtFName.Text = ""
                txtLName.Text = ""
                txtDOB.Text = ""
                txtPhone.Text = ""
                txtLast4.Text = ""
                cbPEC.Value = ""
                cbH2S.Value = ""
                cbCS.Value = ""
                cbFT.Value = ""
                cbFA.Value = ""
                cbCPR.Value = ""
                cbBBP.Value = ""
                cbFS.Value = ""
                cmbPosition.SetFocus
           
            End If
        End If
End Sub
 
Upvote 0
Probably this:

VBA Code: 
    cbPEC.Value = False
    cbH2S.Value = False
    cbCS.Value = False
    cbFT.Value = False
    cbFA.Value = False
    cbCPR.Value = False
    cbBBP.Value = False
    cbFS.Value = False
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA auto due date
Replies
5
Views
382
StephenCrump
S

Forum statistics

Threads
1,215,473
Messages
6,125,018
Members
449,203
Latest member
tungnmqn90

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top