Solved

Insert a checkbox to a row from a form

Posted on 2010-11-09
3
265 Views
Last Modified: 2012-08-13
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
Comment
Question by:agepab
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 250 total points
ID: 34093723
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
 

Author Comment

by:agepab
ID: 34096594
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34097568
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now