Solved

Excel - VBA Insert checkbox when filtering is on

Posted on 2010-11-10
7
560 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 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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

630 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