Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Insert a checkbox to a row from a form

Hi,
I have made a dialogue box that a user can fill in information, when he push the cmdAdd button in the form, the data will be saved in the next available row in the sheet "Timer til fakturering".

Now, when the data is stored I would like a checkbox to appear in the same row that the data was stored to. Here is my script on the cmdAdd data button.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lKunde As Long
Dim ws As Worksheet
Set ws = Worksheets("Timer til fakturering")

'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
  
lKunde = Me.cboKunde.ListIndex

'check for a "kunde" number
If Trim(Me.cboKunde.Value) = "" Then
  Me.cboKunde.SetFocus
  MsgBox "Vennligst skriv inn kunde"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(lRow, 1).Value = Me.cboKunde.Value
  .Cells(lRow, 2).Value = Me.txtKontakt.Value
  .Cells(lRow, 3).Value = Me.cboAktivitet.Value
  .Cells(lRow, 4).Value = CDate(frmTime.txtDate.Value)
  .Cells(lRow, 5).Value = Me.txtQty.Value
  .Cells(lRow, 6).Value = Me.txtPris.Value
  .Cells(lRow, 7).Value = Me.txtFastpris.Value
  .Cells(lRow, 8).Value = Format$(frmTime.SumTotal.Value)
  .Cells(lRow, 9).Value = Me.txtAntKm.Value
  .Cells(lRow, 10).Value = Me.txtSats.Value
  .Cells(lRow, 11).Value = Format$(frmTime.txtSumtravel.Value)
  .Cells(lRow, 12).Value = Me.txtOvertid.Value
  'Use Caption with labels, value will not work
  .Cells(lRow, 13).Value = Me.LabelUser.Caption
  
    
  
End With

'clear the data
Me.cboKunde.Value = ""
Me.txtKontakt.Value = ""
Me.cboAktivitet.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtPris.Value = ""
Me.txtQty.Value = 1
Me.txtTotal.Value = ""
Me.txtFastpris.Value = ""
Me.txtAntKm.Value = ""
Me.txtSats.Value = "3.5"
Me.txtTotTravel.Value = ""
Me.txtOvertid.Value = ""
Me.cboKunde.SetFocus

End Sub

Open in new window


I would like the checkbox(es) to be inserted in Column N in row 2. Which is the first row of data saved. On the next record it will generate a new checkbox in N3 and so on..
0
agepab
Asked:
agepab
  • 2
1 Solution
 
Chris BottomleyCommented:
Try as below, note i AM ADDING IT to column 1 (Cells(lRow, 1).Left ) but modify as you wish.

Chris
Sub addCB()
Dim obj As Object
    With ActiveSheet
        Set obj = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=.Cells(lRow, 1).Left + 10, Top:=.Cells(lRow, 1).Top, Width:=40.75, Height:=24)
        With obj
            .Object.Caption = "Fred"
        End With
    End With
End Sub

Open in new window

0
 
agepabAuthor Commented:
Thanks Chris, it seem to work;). But is there a way to get it to show true/false in the same row if the checkbox is checked or unchecked?
0
 
Chris BottomleyCommented:
Don't know at the moment - busy on other things i'm afraid.  Try a new question ... you ought to get fresh eyes that way, normally i'd try and answer but just not enough hours!

cHRIS
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now