Checkbos not returning True value in Excel Sheet

[farhad]

Good Day

I have 11 checkboxes on a userform. When I select a checkbox and write the data to excel sheet, the unselected checkboxes return a False Value and
the checked is blank in the Sheet whereas it should return True Value which does not.

Thanks

Code is as follows:

Private Sub CommandButton20_Click() 'Update License Purchase

Application.ScreenUpdating = False
Sheets("Sheet4").Unprotect
    Dim iRow As Long
    Dim ws As Worksheet
    Dim ALBTAG As String
   Dim CLoc As Range
    
Call Module25.License
CommandButton20.Visible = True
    
Set ws = Worksheets("Sheet4")
   If ListBox1.Text = "" Then
        MsgBox "Select a record to edit", vbCritical
            Exit Sub
                End If
    
   ALBTAG = Me.TextBox6.Value
    
   Set CLoc = ws.Columns("A:CZ").Find(What:=ALBTAG, After:=ws.Cells(1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                               xlNext, MatchCase:=False, SearchFormat:=False)
   
    
    If CLoc Is Nothing Then
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row
    Else
        iRow = CLoc.Row
    End If
    ws.Cells(iRow, 47).Value = Me.TextBox200.Value 'User
    ws.Cells(iRow, 48).Value = Me.TextBox201.Value 'branch
    ws.Cells(iRow, 49).Value = Me.TextBox202.Value 'Department
    ws.Cells(iRow, 50).Value = Me.TextBox203.Value 'JT
    ws.Cells(iRow, 51).Value = Me.TextBox216.Value 'Cost Centre
    ws.Cells(iRow, 52).Value = Me.TextBox204.Value 'Date Requested
    ws.Cells(iRow, 53).Value = Me.TextBox205.Value 'Software
    ws.Cells(iRow, 54).Value = Me.TextBox206.Value 'Description
    ws.Cells(iRow, 55).Value = Me.TextBox207.Value 'Category
    ws.Cells(iRow, 56).Value = Me.TextBox208.Value 'License Key
    ws.Cells(iRow, 57).Value = Me.TextBox209.Value 'Supplier
    ws.Cells(iRow, 58).Value = Me.TextBox210.Value 'Order Note Date
    ws.Cells(iRow, 59).Value = Me.TextBox211.Value 'Order Note Number
    ws.Cells(iRow, 60).Value = Me.TextBox212.Value 'Inv Date
    ws.Cells(iRow, 61).Value = Me.TextBox213.Value 'Inv No
    ws.Cells(iRow, 62).Value = Me.TextBox214.Value 'QTY
    ws.Cells(iRow, 63).Value = Me.TextBox215.Value 'Cost
    ws.Cells(iRow, 64).Value = Me.CheckBox100.Value  'Ms OS
    ws.Cells(iRow, 65).Value = Me.CheckBox107.Value  'MS Office
    ws.Cells(iRow, 66).Value = Me.CheckBox111.Value  'MS Acess
    ws.Cells(iRow, 67).Value = Me.CheckBox110.Value  'MS Visio
    ws.Cells(iRow, 68).Value = Me.CheckBox108.Value  'MS Project
    ws.Cells(iRow, 69).Value = Me.CheckBox106.Value  'Exchange
    ws.Cells(iRow, 70).Value = Me.CheckBox102.Value  'Win Server Cals
    ws.Cells(iRow, 71).Value = Me.CheckBox103.Value  'SQL
    ws.Cells(iRow, 72).Value = Me.CheckBox101.Value  'Adobe
    ws.Cells(iRow, 73).Value = Me.CheckBox109.Value  'Office 365
    ws.Cells(iRow, 74).Value = Me.CheckBox104.Value 'ASA Firepower
    ws.Cells(iRow, 75).Value = Me.CheckBox105.Value 'Teamviewer
 
 Call Main 'Progress Bar
        MsgBox "Software License Details Updated ..."
            ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a65536].End(3).Row).Value      'For refresh listbox

Application.ScreenUpdating = False
Unload Me
UserForm1.Show
End Sub

 

[Yongle]

which version of Excel are you using?
since 2007 version Excel has contained 1048576 rows

ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [[COLOR=#ff0000]a65536[/COLOR]].End(3).Row).Value

ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [[COLOR=#ff0000]a1048576[/COLOR]].End(3).Row).Value /CODE]

I can never remember which direction is 1,2,3 or 4 ...
.... so prefer to specify like this: 
[CODE]ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [[COLOR=#ff0000]a1048576[/COLOR]].End([SIZE=3][COLOR=#008080]xlUp[/COLOR])[/SIZE].Row).Value /CODE]

 

[Yongle]

the checked is blank in the Sheet whereas it should return True Value which does not

the default value in a checkbox is blank until checked - whch changes it toTRUE
unchecking changes THAT value to FALSE

if above is important, loop through the checkboxes changing their value to FALSE in Userform_Initialize

 

[Yongle]

post2 should look like this

which version of Excel are you using?
since 2007 version Excel has contained 1048576 rows

ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a65536].End(3).Row).Value

ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a1048576].End(3).Row).Value

I can never remember which direction is 1,2,3 or 4 ...
.... so prefer to specify like this:

ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a1048576].End(xlUp).Row).Value

 

[farhad]

Hi yongle

Solution worked. Thanks a million
Regards