[Solved] Previous, Next, Save, Delete, Generate on User form

[Gredang]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

 

[Lutfor Rahman Shimanto]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

Hello Gredang

Thanks for reaching out and posting your requirements with such clarity. Your goal can be fulfilled by implementing some sub-procedure—for Example, procedures for Generate, Save Record, Previous and Next Record buttons. Later, you must assign these macros to the intended buttons.

Implementing procedures like these will take several hours. I am analyzing the workbook data and trying to implement the procedures. When I am done coding, I will share the ideas here. Meanwhile, stay with ExcelDemy Forum and good luck!

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

Hello Gredang

I am delighted to inform you that I have implemented all the necessary procedures. All you have to do is to assign the intended macros to the buttons. Additionally, I have developed two event procedures for sheet GPEntryForm.

Macro for Save Record:

Sub StoreDataInGPResult()
    
    Dim entryFormSheet As Worksheet
    Dim resultSheet As Worksheet
    Dim lastRow, i As Long
    
    Set entryFormSheet = ThisWorkbook.Sheets("GPEntryForm")
    Set resultSheet = ThisWorkbook.Sheets("GPResult")
    
    lastRow = resultSheet.Cells(resultSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    resultSheet.Cells(lastRow, 1).Value = resultSheet.Cells(lastRow - 1, 1).Value + 1
    resultSheet.Cells(lastRow, 2).Value = entryFormSheet.Range("A4").Value
    resultSheet.Cells(lastRow, 3).Value = entryFormSheet.Range("A6").Value
    resultSheet.Cells(lastRow, 4).Value = entryFormSheet.Range("A8").Value
    resultSheet.Cells(lastRow, 5).Value = entryFormSheet.Range("A10").Value
    resultSheet.Cells(lastRow, 6).Value = entryFormSheet.Range("A12").Value
    resultSheet.Cells(lastRow, 7).Value = entryFormSheet.Range("A14").Value
    resultSheet.Cells(lastRow, 8).Value = entryFormSheet.Range("A16").Value
    resultSheet.Cells(lastRow, 9).Value = entryFormSheet.Range("A18").Value
    resultSheet.Cells(lastRow, 10).Value = entryFormSheet.Range("D4").Value
    
    On Error Resume Next
    For i = 4 To 18 Step 2
        
        If i = 18 Then
            entryFormSheet.Range("A18:I21").ClearContents
        End If
        
        entryFormSheet.Range("A" & i).ClearContents
    
    Next i
    
    entryFormSheet.Range("A24").ClearContents
    entryFormSheet.Range("F24").ClearContents
    entryFormSheet.Range("D4").ClearContents
    entryFormSheet.Range("F8").ClearContents
    
    MsgBox "Data has been stored in GPResult sheet.", vbInformation

End Sub

Macro for Save Generate:

Sub Generate()
    
    Dim entryFormSheet As Worksheet
    Dim resultSheet As Worksheet
    Dim formSheet As Worksheet
    Dim lastRow, i As Long
    
    Set entryFormSheet = ThisWorkbook.Sheets("GPEntryForm")
    Set resultSheet = ThisWorkbook.Sheets("GPResult")
    Set formSheet = ThisWorkbook.Sheets("GPForm")
        
    formSheet.Range("B2").Value = entryFormSheet.Range("A8").Value
    formSheet.Range("B14").Value = entryFormSheet.Range("A10").Value
    formSheet.Range("E14").Value = entryFormSheet.Range("A12").Value
    formSheet.Range("B16").Value = entryFormSheet.Range("A14").Value
    formSheet.Range("E16").Value = entryFormSheet.Range("A16").Value
    formSheet.Range("B23").Value = entryFormSheet.Range("A4").Value
    formSheet.Range("B27").Value = entryFormSheet.Range("D4").Value
    formSheet.Range("B31").Value = entryFormSheet.Range("A6").Value
        
    MsgBox "Data has been prepared to print in the GPForm sheet.", vbInformation
    
    formSheet.Activate
    formSheet.Cells(30, 7).Select

End Sub

Macro for Previous Record:

Sub PreviousRecord()
    
    Dim entryFormSheet As Worksheet
    Dim resultSheet As Worksheet
    Dim lastRow, i, temp As Long
    
    Set entryFormSheet = ThisWorkbook.Sheets("GPEntryForm")
    Set resultSheet = ThisWorkbook.Sheets("GPResult")
    
    lastRow = resultSheet.Cells(resultSheet.Rows.Count, "A").End(xlUp).Row
    
    If lastRow < 5 Then
        MsgBox "Insert Some Records."
        Exit Sub
    End If
    
    If entryFormSheet.Range("A24").Value = "" Or entryFormSheet.Range("A24").Value = 0 Then
    
        entryFormSheet.Range("A4").Value = resultSheet.Cells(lastRow, 2).Value
        entryFormSheet.Range("A6").Value = resultSheet.Cells(lastRow, 3).Value
        entryFormSheet.Range("A8").Value = resultSheet.Cells(lastRow, 4).Value
        entryFormSheet.Range("A10").Value = resultSheet.Cells(lastRow, 5).Value
        entryFormSheet.Range("A12").Value = resultSheet.Cells(lastRow, 6).Value
        entryFormSheet.Range("A14").Value = resultSheet.Cells(lastRow, 7).Value
        entryFormSheet.Range("A16").Value = resultSheet.Cells(lastRow, 8).Value
        entryFormSheet.Range("A18").Value = resultSheet.Cells(lastRow, 9).Value
        entryFormSheet.Range("D4").Value = resultSheet.Cells(lastRow, 10).Value
        entryFormSheet.Range("A24").Value = resultSheet.Cells(lastRow, 1).Value - 1
        entryFormSheet.Range("F24").Value = entryFormSheet.Range("A24").Value + 2
        Exit Sub
        
    End If
    
    temp = entryFormSheet.Range("A24").Value + 4
    
    entryFormSheet.Range("A4").Value = resultSheet.Cells(temp, 2).Value
    entryFormSheet.Range("A6").Value = resultSheet.Cells(temp, 3).Value
    entryFormSheet.Range("A8").Value = resultSheet.Cells(temp, 4).Value
    entryFormSheet.Range("A10").Value = resultSheet.Cells(temp, 5).Value
    entryFormSheet.Range("A12").Value = resultSheet.Cells(temp, 6).Value
    entryFormSheet.Range("A14").Value = resultSheet.Cells(temp, 7).Value
    entryFormSheet.Range("A16").Value = resultSheet.Cells(temp, 8).Value
    entryFormSheet.Range("A18").Value = resultSheet.Cells(temp, 9).Value
    entryFormSheet.Range("D4").Value = resultSheet.Cells(temp, 10).Value
    entryFormSheet.Range("A24").Value = resultSheet.Cells(temp, 1).Value - 1
    entryFormSheet.Range("F24").Value = entryFormSheet.Range("A24").Value + 2
    
End Sub

Macro for Next Record:

Sub NextRecord()
    
    Dim entryFormSheet As Worksheet
    Dim resultSheet As Worksheet
    Dim lastRow, i, temp As Long
    
    Set entryFormSheet = ThisWorkbook.Sheets("GPEntryForm")
    Set resultSheet = ThisWorkbook.Sheets("GPResult")
    
    lastRow = resultSheet.Cells(resultSheet.Rows.Count, "A").End(xlUp).Row
    
    If lastRow < 5 Then
        MsgBox "Insert Some Records."
        Exit Sub
    End If
    
    If entryFormSheet.Range("F24").Value = "" Or entryFormSheet.Range("F24").Value = lastRow - 3 Then
    
        entryFormSheet.Range("A4").Value = resultSheet.Cells(5, 2).Value
        entryFormSheet.Range("A6").Value = resultSheet.Cells(5, 3).Value
        entryFormSheet.Range("A8").Value = resultSheet.Cells(5, 4).Value
        entryFormSheet.Range("A10").Value = resultSheet.Cells(5, 5).Value
        entryFormSheet.Range("A12").Value = resultSheet.Cells(5, 6).Value
        entryFormSheet.Range("A14").Value = resultSheet.Cells(5, 7).Value
        entryFormSheet.Range("A16").Value = resultSheet.Cells(5, 8).Value
        entryFormSheet.Range("A18").Value = resultSheet.Cells(5, 9).Value
        entryFormSheet.Range("D4").Value = resultSheet.Cells(5, 10).Value
        entryFormSheet.Range("F24").Value = resultSheet.Cells(5, 1).Value + 1
        entryFormSheet.Range("A24").Value = entryFormSheet.Range("F24").Value - 2
        
        Exit Sub
        
    End If
    
    If entryFormSheet.Range("F24").Value <= 0 Then
        entryFormSheet.Range("F24").Value = 1
    End If
    
    temp = entryFormSheet.Range("F24").Value + 4
    
    entryFormSheet.Range("A4").Value = resultSheet.Cells(temp, 2).Value
    entryFormSheet.Range("A6").Value = resultSheet.Cells(temp, 3).Value
    entryFormSheet.Range("A8").Value = resultSheet.Cells(temp, 4).Value
    entryFormSheet.Range("A10").Value = resultSheet.Cells(temp, 5).Value
    entryFormSheet.Range("A12").Value = resultSheet.Cells(temp, 6).Value
    entryFormSheet.Range("A14").Value = resultSheet.Cells(temp, 7).Value
    entryFormSheet.Range("A16").Value = resultSheet.Cells(temp, 8).Value
    entryFormSheet.Range("A18").Value = resultSheet.Cells(temp, 9).Value
    entryFormSheet.Range("D4").Value = resultSheet.Cells(temp, 10).Value
    entryFormSheet.Range("F24").Value = resultSheet.Cells(temp, 1).Value + 1
    entryFormSheet.Range("A24").Value = entryFormSheet.Range("F24").Value - 2

End Sub

Macro for Delete:
As we can write a reply within 10,000 words, the rest of the code will be in the next reply.

Macro for Print:
As we are allowed to write a reply within 10,000 words, the following code will be in the next reply.

Event for GPEntryForm:
We can write a reply within 10,000 words. So, the rest of the code will be in the next reply.

To assist you in understanding better, I've included the Workbook. If you have any other inquiries, don't hesitate to ask them again.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

Macro for Delete:

Sub DeleteRecord()
   
  Dim ws, wsEntry As Worksheet
  Dim rng As Range
  Dim temp, Target As Long
   
  Set ws = ThisWorkbook.Sheets("GPResult")
  Set wsEntry = ThisWorkbook.Sheets("GPEntryForm")
   
  temp = wsEntry.Range("A24").Value
  Target = temp + 5
   
  ws.Activate
  ws.Rows(Target).EntireRow.Delete
   
  Call InsertNo
   
  For i = 4 To 18 Step 2
     
    If i = 18 Then
      wsEntry.Range("A18:I21").ClearContents
    End If
     
    wsEntry.Range("A" & i).ClearContents
   
  Next i
   
  wsEntry.Range("A24").ClearContents
  wsEntry.Range("F24").ClearContents
  wsEntry.Range("D4").ClearContents
  wsEntry.Range("F8").ClearContents
   
  MsgBox "Data has been deleted from GPResult sheet.", vbInformation
   
End Sub

Sub InsertNo()
   
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim rowNum As Long
  Dim numValue As Long
   
  Set ws = ThisWorkbook.Sheets("GPResult")
   
  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
  numValue = 1
   
  For rowNum = 5 To lastRow
    ws.Cells(rowNum, "A").Value = numValue
     
    numValue = numValue + 1
  Next rowNum

End Sub

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

Macro for Print:

Sub PrintUsedRange()
   
  Dim ws As Worksheet
  Dim usedRange As Range
   
  Set ws = ThisWorkbook.Sheets("GPForm")
   
  Set usedRange = ws.Range("A1:L46")
   
  usedRange.PrintOut
   
  ws.Range("B2").ClearContents
  ws.Range("B14").ClearContents
  ws.Range("E14").ClearContents
  ws.Range("B16").ClearContents
  ws.Range("E16").ClearContents
  ws.Range("B23").ClearContents
  ws.Range("B27").ClearContents
  ws.Range("B31").ClearContents
     
End Sub

Regards

 

[Lutfor Rahman Shimanto]

Hi i am trying to use excel to create a beautiful database (instead of using Access). i have a workbook with 3 tabs; GPEntryForm, GPResult and GPForm. In the GPEntryForm, this is the user form that user will key in.
(1) However, how can i use the previous, next, save, delete and generate button to record the entry?

Example, if i enter the details in the entry form, when i click next and save, it would save to GPResult tab. If i click on previous, it will show me the previous result (in the GPEntryform).

(2) Also, once i have entered if i clicked on generate, all my details will be in the GPForm and ready to print. When i go to GPForm, i click on the print icon, it will print.

(3) Isnt possible that we could see the entry (GPResult) by clicking previous record/next record button in GPEntryForm tab?

(4) When i click on the delete icon, the entry in the GPResult will automatically delete the particular trainee that was selected in GPEntryForm tab.


Thank you very much!

Event for GPEntryForm:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim wsEntryForm As Worksheet
  Dim wsResult As Worksheet
  Dim lastRow As Long
  Dim countValue As Long
  
  Set wsEntryForm = ThisWorkbook.Sheets("GPEntryForm")
  Set wsResult = ThisWorkbook.Sheets("GPResult")
  
  If Not Intersect(Target, wsEntryForm.Range("A4")) Is Nothing Then
    
    lastRow = wsResult.Cells(wsResult.Rows.Count, "B").End(xlUp).Row
    
    countValue = 0
    
    For Each cell In wsResult.Range("B5:B" & lastRow)
      If cell.Value = wsEntryForm.Range("A4").Value Then
        countValue = countValue + 1
      End If
    Next cell
    
    wsEntryForm.Range("F8").Value = countValue
  End If
  
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  Dim wsEntryForm As Worksheet
  Dim wsResult As Worksheet
  
  Set wsEntryForm = ThisWorkbook.Sheets("GPEntryForm")
  Set wsResult = ThisWorkbook.Sheets("GPResult")
  
  If Not Intersect(Target, wsEntryForm.Range("A24")) Is Nothing Then
    MsgBox "Do not make any changes."
    wsEntryForm.Range("A25").Select
  End If
  
  If Not Intersect(Target, wsEntryForm.Range("F24")) Is Nothing Then
    MsgBox "Do not make any changes."
    wsEntryForm.Range("A25").Select
  End If
  
End Sub

You have to paste all the procedures into the same module. You must paste the event procedures in the sheet module of GPEntryForm. I hope this idea will reach your goal. Good luck!

Regards
Lutfor Rahman Shimanto