Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Course Of The Month
2 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Insert a checkbox to a row from a form
Posted on 2010-11-09
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.
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..
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
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..
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
3 Comments
Try as below, note i AM ADDING IT to column 1 (Cells(lRow, 1).Left ) but modify as you wish.
Chris
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
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?
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| 78 files, need to delete row 2 in every file | 3 | 34 | |
| Importing specific cell from Excel to MS Access | 17 | 54 | |
| Embed XL WS and Objects into a PPTX | 8 | 35 | |
| Produce the count value next to each filterable item in a spreadsheet | 30 | 74 |
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month2 days, 9 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.