?
Solved

Excel - VBA Insert checkbox when filtering is on

Posted on 2010-11-10
7
Medium Priority
?
563 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
[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
  • 2
7 Comments
 
LVL 34

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 34

Accepted Solution

by:
Norie earned 1600 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
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

719 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