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 LongDim lKunde As LongDim ws As WorksheetSet ws = Worksheets("Timer til fakturering")'find first empty row in databaselRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).RowlKunde = Me.cboKunde.ListIndex'check for a "kunde" numberIf Trim(Me.cboKunde.Value) = "" Then Me.cboKunde.SetFocus MsgBox "Vennligst skriv inn kunde" Exit SubEnd If'copy the data to the databaseWith 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.CaptionEnd With'clear the dataMe.cboKunde.Value = ""Me.txtKontakt.Value = ""Me.cboAktivitet.Value = ""Me.txtDate.Value = Format(Date, "Medium Date")Me.txtPris.Value = ""Me.txtQty.Value = 1Me.txtTotal.Value = ""Me.txtFastpris.Value = ""Me.txtAntKm.Value = ""Me.txtSats.Value = "3.5"Me.txtTotTravel.Value = ""Me.txtOvertid.Value = ""Me.cboKunde.SetFocusEnd Sub
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..
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 WithEnd Sub
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Chris
Open in new window