#1
|
||||
|
||||
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. |
#2
|
||||
|
||||
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 |
#3
|
||||
|
||||
Please use code tags (#button) instead of quote tags.
Please amend your post accordingly Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Quote:
|
#5
|
||||
|
||||
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. |
#6
|
|||
|
|||
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? |
#7
|
||||
|
||||
I want to insert new records through the userform, if possible.
|
#8
|
|||
|
|||
Quote:
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 |
#9
|
||||
|
||||
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?
|
#10
|
|||
|
|||
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. |
#11
|
||||
|
||||
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. |
|
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 |