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.

 

[sous2817]

One way would be something like:

If Me.cb_FPS3 = True then
   ws.Cells(iRow,11).Value = "FPS3"
Else:
   ws.Cells(iRow,11).Value = ""
end if

repeat for other check boxes.

 

[heidibbb]

Thank you! I'm guessing I'll add this syntax below my existing syntax like this:

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

If Me.cb_FPS3 = True then
ws.Cells(iRow,11).Value = "FPS3"
Else:
ws.Cells(iRow,11).Value = ""
end if

If Me.cb_PAFC = True then
ws.Cells(iRow,11).Value = "FPS3"
Else:
ws.Cells(iRow,11).Value = ""
end if

If Me.cb_FPS3 = True then
ws.Cells(iRow,11).Value = "NMS"
Else:
ws.Cells(iRow,11).Value = ""
end if

If Me.cb_FPS3 = True then
ws.Cells(iRow,11).Value = "SOS"
Else:
ws.Cells(iRow,11).Value = ""
end if

If Me.cb_FPS3 = True then
ws.Cells(iRow,11).Value = "FOSS"
Else:
ws.Cells(iRow,11).Value = ""
end if

 

[sous2817]

yep, that's pretty much it. Just be careful when coping and pasting and be sure to update your text (in your example you have FPS3 listed twice).

 

[heidibbb]

I'm getting an error:

Compile error:
End If without block If

 

[sous2817]

Post all of your code and I'll have a look. You're missing an IF somewhere

 

[heidibbb]

I figured it out. I put the entire statement on one line to save space and it didn't like that. When I went through and formatted it exactly like you sent me it worked fine.

thanks for your help!

 

[sous2817]

Yeah, if keep it all on one line, you don't need the End IF part. For what it's worth, I find keeping it on different lines is easier to debug, follow, and change. Either way, glad you've got it sorted out.

 

[heidibbb]

Hello,

I'm still having problems with this.

The code is changing the true to the value I want, but it's not leaving the cell blank of the box is not checked.

Here's an example of what I have:

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

What I want is to replace TRUE with "FPS3" and blank the field if it is FALSE.

What is happening is it's populating the field with FPS3 for both TRUE and FALSE.

Any ideas?

 

[sous2817]

Where do you have the code? It should be in the checkbox click event.

for instance (not tested, but should work):

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