Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

Excel - VBA Insert checkbox when filtering is on

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
agepab
Asked:
agepab
  • 2
  • 2
1 Solution
 
NorieCommented:
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
 
agepabAuthor Commented:
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
 
NorieCommented:
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
 
agepabAuthor Commented:
Got it to work, but can't get records to be added to the last empty row. Any suggestions?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now