Solved

Excel - VBA Insert checkbox when filtering is on

Posted on 2010-11-10
7
551 Views
Last Modified: 2012-05-10
Hi again,
Still something to sort out..

I have a sheet with several rows that heve been populated from a form, togheter with a checkbox in column A.

If the user tries to filter the data by different criteria, and forgets to turn it off. He will get problems the next time he tries to add a record from the form. The checkbox will not be inserted, but the text from the form is saved correct.

So is there a way to get the checkbox inserted even if the filter is on?

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lKunde As Long
Dim ws As Worksheet
Dim obj As Object
    
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

'Set in checkbox
With ws
    Set obj = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=.Cells(lRow, 1).Left + 10, Top:=.Cells(lRow, 1).Top + 3, Width:=49, Height:=16)
    obj.Object.Caption = "Fakturer"
    obj.LinkedCell = Cells(lRow, 1).Address
    obj.Object.Value = False
End With

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


Thanks..
0
Comment
Question by:agepab
  • 2
  • 2
7 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 34111459
How about not using checkboxes in the first place?
You could use Data>Validation to provide a Yes/No dropdown for each row of data, you can even set the font to display a tick/cross.
Each time you add a new record to the spreadsheet you can just copy the data validation to the appropriate column.
By the way you could clear the userform by just closing it and opening it again, adding some code in the initialize event to set any defaults.
 

Private Sub cmdAdd_Click()

... code to add new record

' close form and reopen

Unload Me

UserForm1.Show ' change name to that of the user form

End Sub

Private Sub UserForm_Initialize()
' code for defaults
Me.txtDate.Value = Format(Date, "Medium Date") 
Me.txtQty.Value = 1 
Me.txtSats.Value = "3.5" 

End Sub

Open in new window

0
 

Author Comment

by:agepab
ID: 34119252
Great, thanks imnorie for the tips;)

In regards to the Data Validation, is it possible to add the Data Validation whenever a new record is added to the sheet by the cmdAdd?

Or is the only way to do this by selecting the whole column and apply Data Validation and list functionality?
0
 
LVL 33

Accepted Solution

by:
Norie earned 400 total points
ID: 34119934
Yes, you should be able to add it by just copying from the cell above.

If that doesn't work it is programatically possible to add it, but that's something I've not really done.

So I turned on the macro recorder.:)

Perhaps you can adapt this?


With Range("G1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes, No"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0
 

Author Comment

by:agepab
ID: 34120940
Got it to work, but can't get records to be added to the last empty row. Any suggestions?
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34415214
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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