Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-12-2018, 04:55 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default Need help to add vba for Delete, Next and Previous command buttons


My knowledge of VBA is to record a macro and I do this a lot in my line or work and it often works like hiding and unhiding columns. A teacher friend asked my help about data entry and other related school forms that she must submit. I tried creating a user form from scratch but gave up when I needed to write VBAs for the form that I created. I copied J. Walkenbach's Enhanced Data Form, unfortunately something goes wrong when I add a record. I sent an email to John and his answer was the form is not for universal application so he couldn't help me with my issue. So I went back to my original userform and searched the internet for the appropriate VBAs. I made progress with adding data and closing the userform. But when I executed the macro that I copied elsewhere for the Next and Previous buttons, it registered an error message. I am afraid the codes got mangled, so:
1. Please correct the syntax errors;
2. please add the code for the delete command.
3. I tried changing the default colors to make the userform a bit fancier but the result is still the bland blue form. What could be missing in the changes that i made in the properties window?
4. Is it possible to add an icon for the userform in the QAT?

Thank you. BTW this sheet is the data source of the Form that p45cal is currently working on.
Attached is the sample sheet and the codes.
Attached Files
File Type: xlsm SAMPLE.xlsm (45.7 KB, 6 views)
Reply With Quote
  #2  
Old 12-04-2018, 05:07 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Hi everyone, below are the nearly completed vba codes for the add, next, previous, search, delete and close buttons of a user Form. These codes were running okay but when I tried to add a new record and hit the "add" button, the following message from Smadav popped out:
"Macro in this excel file tried to execute unknown application. The action is blocked for security reason". Then excel restarts.
My knowledge of VBA is next to nothing so what I did was to search, copy, paste and edit the codes that I think would fit with the command buttons. The add command codes were running smoothly but when I added the codes for the search command, the message appeared.
Please help me point out the unknown application and would somebody else write the codes for the "insert" button. Thank you.
I'm not sure if I need to start a new thread for this.


Code:
Public nCurrentRow As Long
Private Sub cmdClear_Click()
    ClearData
End Sub
Private Sub cmdInsert_Click()

End Sub
Private Sub UserForm_Initialize()
    nCurrentRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    'TraverseData (nCurrentRow)
End Sub
Private Sub TraverseData(nRow As Long)
    Me.cmbSchool.Value = Sheet2.Cells(nRow, 1)
    Me.cmbSchoolID.Value = Sheet2.Cells(nRow, 2)
    Me.tbxLRN.Value = Sheet2.Cells(nRow, 3)
    Me.tbxLastName.Value = Sheet2.Cells(nRow, 4)
End Sub
Private Sub ClearData()
    'Clear input controls.
    Me.cmbSchool.Value = ""
    Me.cmbSchoolID.Value = ""
    Me.tbxLRN.Value = ""
    Me.tbxLastName.Value = ""
End Sub
Private Sub cmdNext_Click()
    Do
        nCurrentRow = nCurrentRow + 1
        TraverseData (nCurrentRow)
    Loop Until Sheet2.Cells(nCurrentRow, 1).Value = "" Or Sheet2.Cells(nCurrentRow, 1).Value = Me.cmbSchool.Value
End Sub
Private Sub cmdPrevious_Click()
    Do
        nCurrentRow = nCurrentRow - 1
        TraverseData (nCurrentRow)
    Loop Until nCurrentRow = 1 Or Sheet2.Cells(nCurrentRow, 1).Value = Me.cmbSchool.Value
End Sub
Private Sub cmbSexGuard_DropButtonClick()
    'Populate control.
    Me.cmbSexGuard.AddItem "M"
    Me.cmbSexGuard.AddItem "F"
End Sub
Private Sub cmbStatus_DropButtonClick()
    'Populate control.
    Me.cmbStatus.AddItem "Single"
    Me.cmbStatus.AddItem "Married"
End Sub
Private Sub cmbRemarks_DropButtonClick()
    'Populate control.
    Me.cmbRemarks.AddItem "LE"
    Me.cmbRemarks.AddItem "T/I"
End Sub
Private Sub cmbGrade_DropButtonClick()
    'Populate control.
    Me.cmbGrade.AddItem "KINDERGARTEN"
    Me.cmbGrade.AddItem "ONE"
    Me.cmbGrade.AddItem "TWO"
End Sub
Private Sub cmbAcadTrack_DropButtonClick()
   'Populate control.
    Me.cmbAcadTrack.AddItem "ABM"
    Me.cmbAcadTrack.AddItem "HUMSS"
End Sub
Private Sub cmbSex_DropButtonClick()
    'Populate control.
    Me.cmbSex.AddItem "M"
    Me.cmbSex.AddItem "F"
End Sub
Private Sub cmdAdd_Click()
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    ApplicationScreenUpdating = False
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.cmbSchool.Value
        .Cells(lRow, 2).Value = Me.cmbSchoolID.Value
        .Cells(lRow, 3).Value = Me.tbxLRN.Value
        .Cells(lRow, 4).Value = Me.tbxLastName.Value
        
    End With
    'Clear input controls.
        Me.cmbSchool.Value = ""
        Me.cmbSchoolID.Value = ""
        Me.tbxLRN.Value = ""
        Me.tbxLastName.Value = ""
    nCurrentRow = lRow
Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
 End Sub
Private Sub cmdClose_Click()
    Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button!"
  End If
End Sub
Private Sub cmdSearch_Click()
    'Populate Control.
        cmbSchool.Value = ""
        cmbSchoolID.Value = ""
        tbxLRN.Value = ""
        tbxLastName.Value = ""
totRows = Worksheets("DATA").Range("A3").CurrentRegion.Rows.Count
For i = 2 To totRows
    If Trim(Worksheets("DATA").Cells(i, 4)) = Trim(cmbLastName.Value) Then
        cmbSchool.Value = Worksheets("DATA").Cells(i, 1).Value
        cmbSchoolID.Value = Worksheets("DATA").Cells(i, 2).Value
        tbxLRN.Value = Worksheets("DATA").Cells(i, 3).Value
        tbxLastName.Value = Worksheets("DATA").Cells(i, 4).Value
End If
Next i
If cmbLastName.Value = "" Then
MsgBox "Select Last"
Else
cmdDelete.Enabled = True
End If
End Sub
Private Sub CmdDelete_Click()
Dim smessage As String
    smessage = "Are you sure you want to delete? " & vbCrLf + vbCrLf + Chr(32) + cmbSchool.Text + Chr(32) + cmbSchoolID.Text + Chr(32) + tbxLRN.Text
    If MsgBox(smessage, vbQuestion + vbYesNo, _
              "Confirm Delete") = vbYes Then
Dim LastRow As Long, i As Long

'find last row of data in column A
LastRow = Columns(1).Find("*", SearchDirection:=xlPrevious).Row

'loop from last row to row 1
'For i = LastRow To 1 Step -1
 '   If Cells(i, "A") = cmbSchool.Text And Cells(i, "B") = cmbSchoolID.Text And Cells(i, "C") = tbxLRN.Text Then
        Rows(nCurrentRow).Delete
        ClearData
  '  End If
'Next
End If
'If Me.LastName = "" Then
 '   MsgBox "You have not selected a Last Name.", vbInformation
  '  Me.LastName.SetFocus
   ' Exit Sub
'End If
'FindRecord (Val(Me.LastName))
 '   If Not rngFound Is Nothing Then
  '      PopulateForm
   '     response = MsgBox("Are you sure you want to delete this record? " & Me.LastName & " on " & DATA & "!", vbYesNo + vbCritical)
    '    If response = vbYes Then rngFound.EntireRow.Delete
    'Else
     '   MsgBox "There is no record with Last Name " & Me.LastName & " on " & DATA & "!", vbInformation
      '  Me.LastName.SetFocus
    'End If
    'ClearForm
End Sub

Last edited by Marcia; 12-05-2018 at 03:19 AM. Reason: Change tags from quote to code tags as per instruction by the moderator
Reply With Quote
  #3  
Old 12-04-2018, 09:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,225
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Please use code tags (#button) instead of quote tags.
Please amend your post accordingly
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 12-05-2018, 08:14 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Quote:
would somebody else write the codes for the "insert" button
How would one know what, when and where to insert ?
Reply With Quote
  #5  
Old 12-05-2018, 03:00 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
How would one know what, when and where to insert ?

I would like to insert (not add) a new data of School, School ID, LRN and Last Name of a student. This happens when during data encoding, the user missed a student profile so the student personal data must be inserted. The data must be arranged in alphabetical order of the Last Names of students. I tried uploading the User Form but failed.



I hope I am making sense. Thank you.
Reply With Quote
  #6  
Old 12-05-2018, 03:48 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

In your tDATA table from post #1, each row is a record and each cell on the row is a field of the record.
Are you wanting to insert new records or fill in field(s) of existing records?


Can you not sort the data to arrange it alphabetically?
Reply With Quote
  #7  
Old 12-06-2018, 12:45 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

I want to insert new records through the userform, if possible.
Reply With Quote
  #8  
Old 12-06-2018, 07:19 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Quote:
Can you not sort the data to arrange it alphabetically?
You didn't bother to answer that question. Perhaps you're still thinking about it.

Every record can be added to the bottom of the table then sorted into its correct location.
Here's how the macro recorder handles the sort.
Code:
Sub Macro1()
'
' Macro1 Macro
' sort by last name
'

'
    Range("D2").Select
    ActiveWorkbook.Worksheets("DATA").ListObjects("tDATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").ListObjects("tDATA").Sort.SortFields.Add Key _
        :=Range("D2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DATA").ListObjects("tDATA").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Reply With Quote
  #9  
Old 12-06-2018, 07:59 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Thank you NoSparks for these codes that sort the data. I prefer this method than inserting a new record. However, I'm having an issue with the "add" macro caused, according to smadav, by an unknown application. Any idea why this happens?
Reply With Quote
  #10  
Old 12-06-2018, 08:28 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 650
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

You're not having an issue with the "add" macro, you're having an issue with Smadav,
which Google tells me is a secondary anti-virus program.
Maybe there's a setting to include/exclude certain files or file types in Smadav.

Interesting that your "add" macro is the only one using Application.something, perhaps that's what Smadav is concerned about.
Reply With Quote
  #11  
Old 12-06-2018, 06:51 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

I copied then pasted that Application.something when, while searching for possible reason/s why the system was taking several minutes to process a new record, I came across a post somewhere that the application.something lines could be added to make the macro run faster. I deleted the lines now then tried adding a new record but Excel automatically restarted when I hit the add button. Just when I thought I'm almost there at the completion stage, but now this.
As to checking smadav's settings, I'm afraid that is beyond my know how. I'll try anyway, thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hiding Command Buttons before PDF ksigcajun Word VBA 3 05-07-2014 05:31 AM
Command buttons on slidemaster chcope PowerPoint 2 06-13-2013 04:30 PM
Command Buttons lorenambrose Word 0 10-06-2011 11:55 AM
command buttons ronf Excel 0 04-28-2006 08:32 AM
command buttons ronf Excel 0 12-03-2005 06:26 AM


All times are GMT -7. The time now is 01:00 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft