True value on spreadsheet to fill 'x' on checkbox in Userform.... Help!

[anna82marie]

Good evening!

I've been battling all day with a piece of code that I'm using to try and create a fool proof system for our engineers to use, but I've hit a stumbling block.

I have a userform, that when the UIN is input, it populates the existing fields from the spreadsheet to allow them to be edited and then rewrites to the sheet. It works, except for the check boxes; which are all ticked and greyed out. Any idea what I'm doing wrong? My brain is just totally fried..... The relevant code is below and any help would be AMAZING.

Private Sub BtnSave_Click()
'to write edited info of this UF to sheets
Dim LastRow As Long
Dim UINnum As Double
Dim UINrng As Range
Dim WriteRow As Long

'make sure we're on the right sheet
Sheets("CoSHH").Select
With ActiveSheet
'get the last row used so can set up the search range
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'set the range to search for the UIN number
Set UINrng = .Range("A1:A" & LastRow)
'Get the UIN number from what is selected on user form
UINnum = CBUIN.Value
'get the row of this sheet for this UIN number
WriteRow = Application.Match(UINnum, UINrng, 0)
'make this UIN number the active cell
Cells(WriteRow, 1).Select
'write in all the editable stuff (dont bother with the non-editable things)
With ActiveCell

.Offset(RowCount, 2).Value = Me.CmbAssDay.Value
.Offset(RowCount, 3).Value = Me.CmBAssMonth.Value
.Offset(RowCount, 4).Value = Me.CmbAssYear.Value
.Offset(RowCount, 6).Value = Me.TxtAssessor.Value
.Offset(RowCount, 7).Value = Me.CmbSDSDay.Value
.Offset(RowCount, 8).Value = Me.CmbSDSMonth.Value
.Offset(RowCount, 9).Value = Me.CmbSDSYear.Value
.Offset(RowCount, 10).Value = Me.TxtSDSIssue.Value
.Offset(RowCount, 11).Value = Me.CmbCategory.Value
.Offset(RowCount, 12).Value = Me.CmbManufacturer.Value
.Offset(RowCount, 13).Value = Me.CmbProduct.Value
.Offset(RowCount, 14).Value = Me.CmbForm.Value
.Offset(RowCount, 15).Value = Me.TxtUse.Value
.Offset(RowCount, 22).Value = Me.TxtIngestion.Value
.Offset(RowCount, 16).Value = Me.TxtInhalation.Value
.Offset(RowCount, 18).Value = Me.TxtEye.Value
.Offset(RowCount, 20).Value = Me.TxtSkin.Value
.Offset(RowCount, 25).Value = Me.ChkWater.Value
.Offset(RowCount, 26).Value = Me.ChkCO2.Value
.Offset(RowCount, 27).Value = Me.ChkPowder.Value
.Offset(RowCount, 28).Value = Me.ChkFoam.Value
.Offset(RowCount, 29).Value = Me.TxtFireInstruction.Value
.Offset(RowCount, 30).Value = Me.TxtSpill.Value
.Offset(RowCount, 31).Value = Me.TxtHandling.Value
.Offset(RowCount, 32).Value = Me.TxtStorage.Value
.Offset(RowCount, 19).Value = Me.ChkEyeExp.Value
.Offset(RowCount, 21).Value = Me.ChkSkinExp.Value
.Offset(RowCount, 23).Value = Me.ChkIngestionExp.Value
.Offset(RowCount, 17).Value = Me.ChkInhalationExp.Value
.Offset(RowCount, 33).Value = Me.TxtExpLimit.Value
.Offset(RowCount, 34).Value = Me.CBEye.Value
.Offset(RowCount, 39).Value = Me.CBMask.Value
.Offset(RowCount, 36).Value = Me.CBHand.Value
.Offset(RowCount, 38).Value = Me.CBHiVis.Value
.Offset(RowCount, 35).Value = Me.CBHead.Value
.Offset(RowCount, 41).Value = Me.CBClothes.Value
.Offset(RowCount, 40).Value = Me.CBShield.Value
.Offset(RowCount, 43).Value = Me.CBHarness.Value
.Offset(RowCount, 42).Value = Me.CBRPE.Value
.Offset(RowCount, 37).Value = Me.CBFoot.Value
.Offset(RowCount, 45).Value = Me.ChkHarmful.Value
.Offset(RowCount, 46).Value = Me.ChkIrritant.Value
.Offset(RowCount, 47).Value = Me.ChkSensitising.Value
.Offset(RowCount, 48).Value = Me.ChkCorrosive.Value
.Offset(RowCount, 49).Value = Me.ChkToxic.Value
.Offset(RowCount, 50).Value = Me.ChkHighlyToxic.Value
.Offset(RowCount, 51).Value = Me.ChkEnvironment.Value
.Offset(RowCount, 52).Value = Me.ChkExplosive.Value
.Offset(RowCount, 53).Value = Me.ChkOxidising.Value
.Offset(RowCount, 54).Value = Me.ChkFlammable.Value
.Offset(RowCount, 55).Value = Me.ChkHighlyFlammable.Value
.Offset(RowCount, 56).Value = Me.ChkGas.Value
.Offset(RowCount, 57).Value = Me.ChkH300.Value
.Offset(RowCount, 58).Value = Me.ChkH301.Value
.Offset(RowCount, 59).Value = Me.ChkH302.Value
.Offset(RowCount, 60).Value = Me.ChkH304.Value
.Offset(RowCount, 61).Value = Me.ChkH310.Value
.Offset(RowCount, 62).Value = Me.ChkH311.Value
.Offset(RowCount, 63).Value = Me.ChkH312.Value
.Offset(RowCount, 64).Value = Me.ChkH314.Value
.Offset(RowCount, 65).Value = Me.ChkH315.Value
.Offset(RowCount, 66).Value = Me.ChkH317.Value
.Offset(RowCount, 67).Value = Me.ChkH318.Value
.Offset(RowCount, 68).Value = Me.ChkH319.Value
.Offset(RowCount, 69).Value = Me.ChkH330.Value
.Offset(RowCount, 70).Value = Me.ChkH331.Value
.Offset(RowCount, 71).Value = Me.ChkH332.Value
.Offset(RowCount, 72).Value = Me.ChkH334.Value
.Offset(RowCount, 73).Value = Me.ChkH335.Value
.Offset(RowCount, 74).Value = Me.ChkH336.Value
.Offset(RowCount, 75).Value = Me.ChkH340.Value
.Offset(RowCount, 76).Value = Me.ChkH341.Value
.Offset(RowCount, 77).Value = Me.ChkH350.Value
.Offset(RowCount, 78).Value = Me.ChkH350i.Value
.Offset(RowCount, 79).Value = Me.ChkH351.Value
.Offset(RowCount, 80).Value = Me.ChkH360D.Value
.Offset(RowCount, 81).Value = Me.ChkH360Df.Value
.Offset(RowCount, 82).Value = Me.ChkH360F.Value
.Offset(RowCount, 83).Value = Me.ChkH360Fd.Value
.Offset(RowCount, 84).Value = Me.ChkH360FD1.Value
.Offset(RowCount, 85).Value = Me.ChkH361f.Value
.Offset(RowCount, 86).Value = Me.ChkH361fd.Value
.Offset(RowCount, 87).Value = Me.ChkH362.Value
.Offset(RowCount, 88).Value = Me.ChkH370.Value
.Offset(RowCount, 89).Value = Me.ChkH371.Value
.Offset(RowCount, 90).Value = Me.ChkH372.Value
.Offset(RowCount, 91).Value = Me.ChkH373.Value
.Offset(RowCount, 92).Value = Me.ChkEUH029.Value
.Offset(RowCount, 93).Value = Me.ChkEUH031.Value
.Offset(RowCount, 94).Value = Me.ChkEUH032.Value
.Offset(RowCount, 95).Value = Me.ChkEUH066.Value
.Offset(RowCount, 96).Value = Me.ChkEUH070.Value
.Offset(RowCount, 97).Value = Me.ChkEUH071.Value
.Offset(RowCount, 98).Value = Me.TxtAddInfo.Value



End With
'put the cursor in upper left
Cells(1, 1).Select

End With
'clear and unload the userform
Call BtnReset

End With

Unload Me

End Sub

 

[Leith Ross]

Hello anna82marie,

Any control that is grayed out is disabled. Did you disable the CheckBoxes elsewhere in your code?

 

[anna82marie]

Thanks for your response.

I thought that may be the case, but I haven't disabled them that I can see - the only other code it links to, simply clears all the controls. (ie, ChkWater.Value = "False"). Any ideas?

 

[BadgerRoad]

You can enable the manually by changing the Enabled Property or you can do it through code. I would suggest you enable them, run your code and see if they become disabled again. If so, it's happening somewhere in your code.

 

[Leith Ross]

Hello anna82marie,

After giving this more thought, It occurred to me the Check Boxes are linked to the worksheet by the ControlSource property. Whenever the cell contains any value that is not a valid Boolean expression, the Check Box is checked and greyed out. A valid Boolean value is TRUE, FALSE or any positive number (True) or zero (False) or formula that gives any of the previous values.

 

[anna82marie]

That makes sense, but how do I get around it - is the answer to insert false to all blank cells?

 

[Fluff]

Depending on how you are loading the checkboxes, you could do something like

Me.CheckBox1.Value = Range("D4").Value = True