Can you change a check box value from a form to something other than TRUE or FALSE in the excel?

[heidibbb]

Hello,

I have a form with many check boxes and I would like to change the value that populates the excel to something other than TRUE if it's checked and null if it's not checked.

Right now my code is as follows:
ws.Cells(iRow, 11).Value = Me.cb_FPS3.Value
ws.Cells(iRow, 12).Value = Me.cb_PAFC.Value
ws.Cells(iRow, 13).Value = Me.cb_NMS.Value
ws.Cells(iRow, 14).Value = Me.cb_SOS.Value
ws.Cells(iRow, 15).Value = Me.cb_FOSS.Value

I'd like to populate the cell with the value of the checkbox abbreviation (FPS3, PAFC, etc.) and have the cell blank if the box is not checked.

Any ideas?
Thanks.

 

[heidibbb]

hmm... here's what my code looks like. Note that the if/then is just on one line as I was testing it. Also, this is my first attempt at VB... so if it looks off or weird, that is why



Private Sub cmdSUBMIT_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("LeadRetrieval")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = StrConv(Me.txtFirst.Value, vbProperCase)
ws.Cells(iRow, 2).Value = StrConv(Me.txtLast.Value, vbProperCase)
ws.Cells(iRow, 3).Value = StrConv(Me.txtTitle.Value, vbProperCase)
ws.Cells(iRow, 4).Value = StrConv(Me.txtSchool.Value, vbProperCase)
ws.Cells(iRow, 5).Value = StrConv(Me.txtAddress.Value, vbProperCase)
ws.Cells(iRow, 6).Value = StrConv(Me.txtCity.Value, vbProperCase)
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value
ws.Cells(iRow, 9).Value = StrConv(Me.txtEmail.Value, vbLowerCase)
ws.Cells(iRow, 10).Value = Me.txtPhone.Value

ws.Cells(iRow, 11).Value = Me.cb_FPS3.Value

If Me.cb_FPS3 = True Then
ws.Cells(iRow, 11).Value = "FPS3"
Else: ws.Cells(iRow, 11).Value = ""
End If

ws.Cells(iRow, 12).Value = Me.cb_PAFC.Value
ws.Cells(iRow, 13).Value = Me.cb_NMS.Value
ws.Cells(iRow, 14).Value = Me.cb_SOS.Value
ws.Cells(iRow, 15).Value = Me.cb_FOSS.Value
ws.Cells(iRow, 16).Value = Me.cb_TM.Value
ws.Cells(iRow, 17).Value = Me.cb_LABP.Value
ws.Cells(iRow, 18).Value = Me.cb_OHAUS.Value
ws.Cells(iRow, 19).Value = Me.cb_INEO.Value
ws.Cells(iRow, 20).Value = Me.cb_SPIRE.Value
ws.Cells(iRow, 21).Value = Me.cb_ETC.Value
ws.Cells(iRow, 22).Value = Me.cb_WW3000.Value
ws.Cells(iRow, 23).Value = Me.cb_MC.Value
ws.Cells(iRow, 24).Value = Me.cb_AOR.Value
ws.Cells(iRow, 25).Value = Me.cb_AOM.Value
ws.Cells(iRow, 26).Value = Me.cb_CVB.Value

ws.Cells(iRow, 27).Value = Me.cb_SalesYes.Value
ws.Cells(iRow, 28).Value = Me.cb_RaffleYes.Value


ws.Cells(iRow, 29).Value = Me.cb_AdoptScience.Value
ws.Cells(iRow, 30).Value = Me.cb_AdoptMath.Value
ws.Cells(iRow, 31).Value = Me.cb_AdoptLiteracy.Value

ws.Cells(iRow, 32).Value = StrConv(Me.txt_Comments.Value, vbLowerCase)
ws.Cells(iRow, 33).Value = Me.txt_Show.Value



'clear the data
Me.txtFirst.Value = ""
Me.txtLast.Value = ""
Me.txtTitle.Value = ""
Me.txtSchool.Value = ""
Me.txtAddress.Value = ""
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""
Me.txtEmail.Value = ""
Me.txtPhone.Value = ""

Me.cb_FPS3.Value = False
Me.cb_PAFC.Value = False
Me.cb_NMS.Value = False
Me.cb_SOS.Value = False
Me.cb_FOSS.Value = False
Me.cb_TM.Value = False
Me.cb_LABP.Value = False
Me.cb_OHAUS.Value = False
Me.cb_INEO.Value = False
Me.cb_SPIRE.Value = False
Me.cb_ETC.Value = False
Me.cb_WW3000.Value = False
Me.cb_MC.Value = False
Me.cb_AOR.Value = False
Me.cb_AOM.Value = False
Me.cb_CVB.Value = False
Me.cb_SalesYes.Value = False
Me.cb_RaffleYes.Value = False
Me.cb_AdoptScience.Value = False
Me.cb_AdoptMath.Value = False
Me.cb_AdoptLiteracy.Value = False
Me.txt_Comments.Value = ""

Me.txt_Show.Value = "NCSC_ATLANTA_2011"



End Sub

 

[sous2817]

I'm at a bit of a loss then. Replace this chunk:

ws.Cells(iRow, 11).Value = Me.cb_FPS3.Value

If Me.cb_FPS3 = True Then
ws.Cells(iRow, 11).Value = "FPS3"
Else: ws.Cells(iRow, 11).Value = ""
End If

with

If Me.cb_FPS3 = True Then
ws.Cells(iRow, 11).Value = "FPS3"
Else: ws.Cells(iRow, 11).Value = ""
End If

(you don't need this part: ws.Cells(iRow, 11).Value = Me.cb_FPS3.Value)

The code I posted works on my test sheet.

 

[heidibbb]

that is working in my test too!!

I think i've got it now... thanks so much again for your help.

 

[sous2817]

Glad I could help. Best of luck with the rest of your project! Feel free to post back should you need anything else.