Solved

Excel - VBA Insert checkbox when filtering is on

Posted on 2010-11-10
7
524 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:teylyn
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

932 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

9 Experts available now in Live!

Get 1:1 Help Now