+ Reply to Thread
Results 1 to 4 of 4

How to make the Checkboxes write to records as 0 and 1 and not true false

Hybrid View

  1. 05-14-2014, 08:49 AM #1
    rbpd5015
    rbpd5015 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    PSL, FL
    Posts
    271

    How to make the Checkboxes write to records as 0 and 1 and not true false

    Ok guys,

    I need a few more small touches to complete my project.

    My excel sheet has to be perfect for my parser to accept the data, so that is why these small things are so important.

    My Worksheet has a correct incorrect column using 1 and 0 's
    I need a 1 and 0 in each row.

    When I submit a new record I can do the following and it shows my TRUE record as a 1 but all my FALSE 0 records as blanks.

    The problem here is that my False rows are blank and not ZEROS
    I use the - after the = and before the value

    .Cells(iaRow, 2).Value = -Me.CBTrvAns1.Value
    .Cells(iaRow + 1, 2).Value = -Me.CBTrvAns2.Value
    .Cells(iaRow + 2, 2).Value = -Me.CBTrvAns3.Value
    .Cells(iaRow + 3, 2).Value = -Me.CBTrvAns4.Value


    Now when I do an update statement it looks like the following

    CBTrvAns1.Value = r2Find.Offset(4, 2).Value
    CBTrvAns2.Value = r2Find.Offset(5, 2).Value
    CBTrvAns3.Value = r2Find.Offset(6, 2).Value
    CBTrvAns4.Value = r2Find.Offset(7, 2).Value

    I have tried to but the - and the *1 but I cant get it to return anything but TRUE and FALSE to my worksheet


    Any help would be greatly appreciated.

    below is my code 

    Private Sub CBTrvAns1_Click()

If CBTrvAns1.Value = True Then
    CBTrvAns2.Value = False
    CBTrvAns3.Value = False
    CBTrvAns4.Value = False
End If

End Sub

Private Sub CBTrvAns2_Click()

If CBTrvAns2.Value = True Then
    CBTrvAns1.Value = False
    CBTrvAns3.Value = False
    CBTrvAns4.Value = False
End If

End Sub

Private Sub CBTrvAns3_Click()

If CBTrvAns3.Value = True Then
    CBTrvAns2.Value = False
    CBTrvAns1.Value = False
    CBTrvAns4.Value = False
End If

End Sub

Private Sub CBTrvAns4_Click()

If CBTrvAns4.Value = True Then
    CBTrvAns2.Value = False
    CBTrvAns3.Value = False
    CBTrvAns1.Value = False
End If

End Sub

Private Sub NewQues_Click()
Dim iRow As Long
Dim iaRow As Long
Dim ws As Worksheet
Dim ans As Worksheet
Set ws = Worksheets("Questions")
Set ans = Worksheets("Answers")


'find first empty row in database
  iRow = ws.Cells.Find(What:="*", _
                         SearchOrder:=xlRows, _
                         SearchDirection:=xlPrevious, _
                         LookIn:=xlValues).Row + 1
                         
'find first empty row in database
  iaRow = ans.Cells.Find(What:="*", _
                         SearchOrder:=xlRows, _
                         SearchDirection:=xlPrevious, _
                         LookIn:=xlValues).Row + 1
'check for a Trivia Question
If Trim(Me.TxtQNum.Value) = "" Then
  Me.TxtQNum.SetFocus
  MsgBox "Please enter a Trivia Question"
  Exit Sub
End If

'clear the data
Me.TxtQNum.Value = iRow - 1
Me.TxtTrvQues.Value = ""
Me.CBCat.Value = ""
Me.CBDif.Value = ""
Me.TxtTrvAns1 = ""
Me.TxtTrvAns2 = ""
Me.TxtTrvAns3 = ""
Me.TxtTrvAns4 = ""
Me.CBTrvAns1 = ""
Me.CBTrvAns2 = ""
Me.CBTrvAns3 = ""
Me.CBTrvAns4 = ""
Me.TxtTrvQues.SetFocus

SaveQuestion.Visible = False
SubQues.Visible = True

End Sub
Private Sub SaveQuestion_Click()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Questions")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Answers")
Dim rFind As Range
Dim r2Find As Range

strQ = TxtQNum.Text
strQu = TxtTrvAns1.Text

Set rFind = ws1.Range("A1:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)

If Not rFind Is Nothing Then
    rFind.Offset(0, 0).Value = TxtQNum.Text
    rFind.Offset(0, 1).Value = TxtTrvQues.Text
    rFind.Offset(0, 2).Value = CBCat.Text
    rFind.Offset(0, 3).Value = CBDif.Text
End If

Set r2Find = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)

If Not r2Find Is Nothing Then
    r2Find.Offset(0, 1).Value = TxtTrvAns1.Text
    r2Find.Offset(1, 1).Value = TxtTrvAns2.Text
    r2Find.Offset(2, 1).Value = TxtTrvAns3.Text
    r2Find.Offset(3, 1).Value = TxtTrvAns4.Text
    r2Find.Offset(0, 2).Value = CBTrvAns1.Value
    r2Find.Offset(1, 2).Value = CBTrvAns2.Value
    r2Find.Offset(2, 2).Value = CBTrvAns3.Value
    r2Find.Offset(3, 2).Value = CBTrvAns4.Value
End If

SaveQuestion.Visible = True
SubQues.Visible = False
'Show that question was submitted
  MsgBox "Your question has been saved"
End Sub

Private Sub SpinButton1_SpinUp()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Questions")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Answers")
Dim rFind As Range
Dim r2Find As Range

strQ = TxtQNum.Text
strQu = TxtTrvAns1.Text
Set rFind = ws1.Range("A1:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)
If rFind.Value = "1" Then Set rFind = ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

If Not rFind Is Nothing Then
    TxtQNum.Text = rFind.Offset(-1, 0).Value
    TxtTrvQues.Text = rFind.Offset(-1, 1).Value
    CBCat.Text = rFind.Offset(-1, 2).Value
    CBDif.Text = rFind.Offset(-1, 3).Value
End If

Set r2Find = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)
If r2Find.Value = "1" Then Set r2Find = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

If Not r2Find Is Nothing Then
    TxtTrvAns1.Text = r2Find.Offset(-4, 1).Value
    TxtTrvAns2.Text = r2Find.Offset(-3, 1).Value
    TxtTrvAns3.Text = r2Find.Offset(-2, 1).Value
    TxtTrvAns4.Text = r2Find.Offset(-1, 1).Value
    CBTrvAns1.Value = r2Find.Offset(-4, 2).Value
    CBTrvAns2.Value = r2Find.Offset(-3, 2).Value
    CBTrvAns3.Value = r2Find.Offset(-2, 2).Value
    CBTrvAns4.Value = r2Find.Offset(-1, 2).Value
      
    
End If
End Sub

Private Sub SpinButton1_SpinDown()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Questions")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Answers")
Dim rFind As Range
Dim r2Find As Range

strQ = TxtQNum.Text
strQu = TxtTrvAns1.Text

Set rFind = ws1.Range("A1:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)
If rFind.Value = ws1.Range("A" & Rows.Count).End(xlUp).Value Then Set rFind = ws1.Range("A1")

If Not rFind Is Nothing Then
    TxtQNum.Text = rFind.Offset(1, 0).Value
    TxtTrvQues.Text = rFind.Offset(1, 1).Value
    CBCat.Text = rFind.Offset(1, 2).Value
    CBDif.Text = rFind.Offset(1, 3).Value
End If

Set r2Find = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strQ, LookIn:=xlValues, Lookat:=xlWhole)
If r2Find.Value = ws1.Range("A" & Rows.Count).End(xlUp).Value Then Set r2Find = ws2.Range("A1")

If Not r2Find Is Nothing Then
    TxtTrvAns1.Text = r2Find.Offset(4, 1).Value
    TxtTrvAns2.Text = r2Find.Offset(5, 1).Value
    TxtTrvAns3.Text = r2Find.Offset(6, 1).Value
    TxtTrvAns4.Text = r2Find.Offset(7, 1).Value
    CBTrvAns1.Value = r2Find.Offset(4, 2).Value
    CBTrvAns2.Value = r2Find.Offset(5, 2).Value
    CBTrvAns3.Value = r2Find.Offset(6, 2).Value
    CBTrvAns4.Value = r2Find.Offset(7, 2).Value
      
End If
End Sub


Private Sub SubQues_Click()
Dim iRow As Long
Dim iaRow As Long
Dim ws As Worksheet
Dim ans As Worksheet
Set ws = Worksheets("Questions")
Set ans = Worksheets("Answers")

'find first empty row in database
  iRow = ws.Cells.Find(What:="*", _
                         SearchOrder:=xlRows, _
                         SearchDirection:=xlPrevious, _
                         LookIn:=xlValues).Row + 1
                         
'find first empty row in database
  iaRow = ans.Cells.Find(What:="*", _
                         SearchOrder:=xlRows, _
                         SearchDirection:=xlPrevious, _
                         LookIn:=xlValues).Row + 1
                         
'check for a Trivia Question
If Trim(Me.TxtQNum.Value) = "" Then
  Me.TxtQNum.SetFocus
  MsgBox "Please enter a Trivia Question"
  Exit Sub
End If

'copy the data to the database

With ws

  .Cells(iRow, 1).Value = Me.TxtQNum.Value
  .Cells(iRow, 2).Value = Me.TxtTrvQues.Value
  .Cells(iRow, 3).Value = Me.CBCat.Value
  .Cells(iRow, 4).Value = Me.CBDif.Value
  

End With

With ans

  .Cells(iaRow, 1).Value = Me.TxtTrvAns1.Value
  .Cells(iaRow + 1, 1).Value = Me.TxtTrvAns2.Value
  .Cells(iaRow + 2, 1).Value = Me.TxtTrvAns3.Value
  .Cells(iaRow + 3, 1).Value = Me.TxtTrvAns4.Value
  .Cells(iaRow, 2).Value = -Me.CBTrvAns1.Value
  .Cells(iaRow + 1, 2).Value = -Me.CBTrvAns2.Value
  .Cells(iaRow + 2, 2).Value = -Me.CBTrvAns3.Value
  .Cells(iaRow + 3, 2).Value = -Me.CBTrvAns4.Value


End With

SaveQuestion.Visible = True
SubQues.Visible = False
'Show that question was submitted
  MsgBox "Your question has been added"
End Sub

Private Sub TxtQNum_Change()

End Sub

Private Sub TxtTrvAns1_Change()

End Sub

Private Sub TxtTrvQues_Change()

End Sub

Private Sub UserForm_Initialize()
    Dim TrvQues As Worksheet
    Dim TrvAns As Worksheet
    Dim TrvSet As Range
    Dim TrvDif As Range
    Dim ts As Worksheet
    
  Set TrvQues = Worksheets("Questions")
  Set TrvAns = Worksheets("Answers")
  Set ts = Worksheets("Settings")
  
For Each TrvSet In ts.Range("Catagories")
    With Me.CBCat
        .AddItem TrvSet.Value
    End With
Next TrvSet

For Each TrvDif In ts.Range("Difficulty")
    With Me.CBDif
        .AddItem TrvDif.Value
    End With
Next TrvDif



Me.TxtQNum = TrvQues.Range("A2").Value
Me.TxtTrvQues = TrvQues.Range("B2").Value
Me.CBCat = TrvQues.Range("C2").Value
Me.CBDif = TrvQues.Range("D2").Value
Me.TxtTrvAns1 = TrvAns.Range("B2").Value
Me.TxtTrvAns2 = TrvAns.Range("B3").Value
Me.TxtTrvAns3 = TrvAns.Range("B4").Value
Me.TxtTrvAns4 = TrvAns.Range("B5").Value
Me.CBTrvAns1 = TrvAns.Range("C2").Value
Me.CBTrvAns2 = TrvAns.Range("C3").Value
Me.CBTrvAns3 = TrvAns.Range("C4").Value
Me.CBTrvAns4 = TrvAns.Range("C5").Value

SubQues.Visible = False



End Sub
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-14-2014, 09:02 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    Perhaps. 
     
  .Cells(iaRow, 2).Value = Iif(Me.CBTrvAns1.Value, 1, 0)
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 05-14-2014, 09:25 AM #3
    rbpd5015
    rbpd5015 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    PSL, FL
    Posts
    271

    Re: How to make the Checkboxes write to records as 0 and 1 and not true false

    Norie,

    Thanks that worked for that section what about on


    CBTrvAns1.Value = r2Find.Offset(4, 2).Value
    Reply With QuoteRegister To Reply
  4. 05-14-2014, 10:35 AM #4
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    What's the problem there?

    Do you want the value of the checkbox to be determined by the value in the cell?

    I would have thought that code would work, especially if the cell value was 0 or 1.

    If it doesn't perhaps you could do something similar to the code I posted earlier.
    CBTrvAns1.Value = Iif(r2Find.Offset(4, 2).Value = 1, True, False)
    Or perhaps something simpler.
    CBTrvAns1.Value = (r2Find.Offset(4, 2).Value = 1)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Make 0 into a negative and NOT false or true
    By Slender in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-09-2013, 02:28 PM
  2. [SOLVED] How to make textbox visible true/false
    By fremarco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2012, 11:40 PM
  3. make OR() return 0 or 1 instead of true and false
    By boarders paradise in forum Excel General
    Replies: 16
    Last Post: 10-19-2009, 01:06 PM
  4. Creating True or false checkboxes
    By L_ter in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-19-2009, 12:04 AM
  5. Macro to make all checkboxes false and clear all comboxes
    By ynissel in forum Excel General
    Replies: 5
    Last Post: 07-29-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1