VBA Coding for Data Entry Form In an Excel Sheet

A

azad092

Board Regular
Joined
Dec 31, 2019
Messages
198
Office Version
  1. 2007
Platform
  1. Windows
Dear Members
good afternoon
I have data entry sheet, and I want to enter data through a data entry form

Data Entry.xlsm

drive.google.com drive.google.com
In the above link you can find a file link, in which I have added tow sheets DATAFORM and MTN
Please guide me how I can add data from DATAFORM into MTN Sheet
thanks
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hello Azad092,
do you want every time you run a code to insert data from sheet "DATAFORM" to the
new line in the sheet MTN?
 
Upvote 0
Open VBA editor. Insert new module. Paste this code into module.
VBA Code: 
Option Explicit

Sub InputingData()

    Dim varNrows As Long
    
    varNrows = Sheets("MTN").UsedRange.Rows.Count
    Sheets("MTN").Range("B" & varNrows + 1) = Sheets("DATAFORM").Range("C3")
    Sheets("MTN").Range("C" & varNrows + 1) = Sheets("DATAFORM").Range("C4")
    Sheets("MTN").Range("D" & varNrows + 1) = Sheets("DATAFORM").Range("C5")
    Sheets("MTN").Range("E" & varNrows + 1) = Sheets("DATAFORM").Range("C6")
    Sheets("MTN").Range("F" & varNrows + 1) = Sheets("DATAFORM").Range("C7")
    Sheets("MTN").Range("G" & varNrows + 1) = Sheets("DATAFORM").Range("C8")
    Sheets("MTN").Range("L" & varNrows + 1) = Sheets("DATAFORM").Range("C9")
    Sheets("MTN").Range("M" & varNrows + 1) = Sheets("DATAFORM").Range("C10")
    Sheets("MTN").Range("U" & varNrows + 1) = Sheets("DATAFORM").Range("C11")
    Sheets("MTN").Range("V" & varNrows + 1) = Sheets("DATAFORM").Range("C12")
    Sheets("MTN").Range("W" & varNrows + 1) = Sheets("DATAFORM").Range("C13")
    Sheets("MTN").Range("X" & varNrows + 1) = Sheets("DATAFORM").Range("C14")
    Sheets("MTN").Range("Y" & varNrows + 1) = Sheets("DATAFORM").Range("C15")
    Sheets("MTN").Range("Z" & varNrows + 1) = Sheets("DATAFORM").Range("C16")
    Sheets("MTN").Range("AA" & varNrows + 1) = Sheets("DATAFORM").Range("C17")
    Sheets("MTN").Range("AB" & varNrows + 1) = Sheets("DATAFORM").Range("C18")
    Sheets("MTN").Range("AC" & varNrows + 1) = Sheets("DATAFORM").Range("C19")
    
    Sheets("MTN").Range("AD" & varNrows + 1) = Sheets("DATAFORM").Range("E3")
'    Sheets("MTN").Range("AE" & varNrows + 1) = Sheets("DATAFORM").Range("E4")
'    Sheets("MTN").Range("AF" & varNrows + 1) = Sheets("DATAFORM").Range("E5")
    Sheets("MTN").Range("AH" & varNrows + 1) = Sheets("DATAFORM").Range("E6")
    Sheets("MTN").Range("AI" & varNrows + 1) = Sheets("DATAFORM").Range("E7")
    Sheets("MTN").Range("AJ" & varNrows + 1) = Sheets("DATAFORM").Range("E8")
    Sheets("MTN").Range("AK" & varNrows + 1) = Sheets("DATAFORM").Range("E9")
    Sheets("MTN").Range("AL" & varNrows + 1) = Sheets("DATAFORM").Range("E10")
    Sheets("MTN").Range("AW" & varNrows + 1) = Sheets("DATAFORM").Range("E11")
    Sheets("MTN").Range("AX" & varNrows + 1) = Sheets("DATAFORM").Range("E12")
    Sheets("MTN").Range("AY" & varNrows + 1) = Sheets("DATAFORM").Range("E13")
    Sheets("MTN").Range("AZ" & varNrows + 1) = Sheets("DATAFORM").Range("E14")
    Sheets("MTN").Range("BA" & varNrows + 1) = Sheets("DATAFORM").Range("E15")
    Sheets("MTN").Range("BK" & varNrows + 1) = Sheets("DATAFORM").Range("E16")
    Sheets("MTN").Range("BR" & varNrows + 1) = Sheets("DATAFORM").Range("E17")

    Sheets("DATAFORM").Range("C3:C19").ClearContents
    Sheets("DATAFORM").Range("E3:C17").ClearContents
    
End Sub

Insert some shape to the "DATAFORM" sheet.
Right click on this shape. Click "Assign maco".
In the listbox select macro "InputingData".
Click "OK". Save Workbook.
Every time you click on this shape any data from sheet "DATAFORM"
will be inserted to the sheet "MTN" in the new row.
If you need to add some new fields for inputing you can modify code folow the logic of the code.
 
Upvote 0
Hi,
another way perhaps

Place in standard module

VBA Code: 
Option Base 1
Sub DataEntryToMTN()
    Dim EntryCell   As Range, DataEntryRange As Range
    Dim wsMTN       As Worksheet
    Dim NextRow     As Long
    Dim Arr         As Variant
    Dim i           As Integer
    
    With ThisWorkbook
        'cells to copy from Data Entry sheet
        Set DataEntryRange = .Worksheets("DATAFORM").Range("C3:C19,E3,E6:E17")
        ' database sheet
        Set wsMTN = .Worksheets("MTN")
    End With
    
    'database ranges to send data entry values
    Arr = Array("B", "C", "D", "E", "F", "G", "L", "M", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", _
                "AD", "AH", "AI", "AJ", "AK", "AL", "AW", "AX", "AY", "AZ", "BA", "BK", "BR")
    
    'output array to database range
    With wsMTN
        NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        
        For Each EntryCell In DataEntryRange.Cells
            i = i + 1
            .Cells(NextRow, Arr(i)).Value = EntryCell.Value
            EntryCell.ClearContents
        Next
    End With
    
    'inform user
    MsgBox "New Record Added", 64, "New Record"
    
End Sub

Note Option Base 1 statement - This MUST sit at the VERY TOP of the module page OUTSIDE any procedure

Hope Helpful

Dave
 
Upvote 0
Great work Dmt32,
I love simplified code,
and hope so that Azad092 will be able to understand it and use.
 
Upvote 0
EXCEL MAX said:
Open VBA editor. Insert new module. Paste this code into module.
VBA Code: 
Option Explicit

Sub InputingData()

    Dim varNrows As Long
   
    varNrows = Sheets("MTN").UsedRange.Rows.Count
    Sheets("MTN").Range("B" & varNrows + 1) = Sheets("DATAFORM").Range("C3")
    Sheets("MTN").Range("C" & varNrows + 1) = Sheets("DATAFORM").Range("C4")
    Sheets("MTN").Range("D" & varNrows + 1) = Sheets("DATAFORM").Range("C5")
    Sheets("MTN").Range("E" & varNrows + 1) = Sheets("DATAFORM").Range("C6")
    Sheets("MTN").Range("F" & varNrows + 1) = Sheets("DATAFORM").Range("C7")
    Sheets("MTN").Range("G" & varNrows + 1) = Sheets("DATAFORM").Range("C8")
    Sheets("MTN").Range("L" & varNrows + 1) = Sheets("DATAFORM").Range("C9")
    Sheets("MTN").Range("M" & varNrows + 1) = Sheets("DATAFORM").Range("C10")
    Sheets("MTN").Range("U" & varNrows + 1) = Sheets("DATAFORM").Range("C11")
    Sheets("MTN").Range("V" & varNrows + 1) = Sheets("DATAFORM").Range("C12")
    Sheets("MTN").Range("W" & varNrows + 1) = Sheets("DATAFORM").Range("C13")
    Sheets("MTN").Range("X" & varNrows + 1) = Sheets("DATAFORM").Range("C14")
    Sheets("MTN").Range("Y" & varNrows + 1) = Sheets("DATAFORM").Range("C15")
    Sheets("MTN").Range("Z" & varNrows + 1) = Sheets("DATAFORM").Range("C16")
    Sheets("MTN").Range("AA" & varNrows + 1) = Sheets("DATAFORM").Range("C17")
    Sheets("MTN").Range("AB" & varNrows + 1) = Sheets("DATAFORM").Range("C18")
    Sheets("MTN").Range("AC" & varNrows + 1) = Sheets("DATAFORM").Range("C19")
   
    Sheets("MTN").Range("AD" & varNrows + 1) = Sheets("DATAFORM").Range("E3")
'    Sheets("MTN").Range("AE" & varNrows + 1) = Sheets("DATAFORM").Range("E4")
'    Sheets("MTN").Range("AF" & varNrows + 1) = Sheets("DATAFORM").Range("E5")
    Sheets("MTN").Range("AH" & varNrows + 1) = Sheets("DATAFORM").Range("E6")
    Sheets("MTN").Range("AI" & varNrows + 1) = Sheets("DATAFORM").Range("E7")
    Sheets("MTN").Range("AJ" & varNrows + 1) = Sheets("DATAFORM").Range("E8")
    Sheets("MTN").Range("AK" & varNrows + 1) = Sheets("DATAFORM").Range("E9")
    Sheets("MTN").Range("AL" & varNrows + 1) = Sheets("DATAFORM").Range("E10")
    Sheets("MTN").Range("AW" & varNrows + 1) = Sheets("DATAFORM").Range("E11")
    Sheets("MTN").Range("AX" & varNrows + 1) = Sheets("DATAFORM").Range("E12")
    Sheets("MTN").Range("AY" & varNrows + 1) = Sheets("DATAFORM").Range("E13")
    Sheets("MTN").Range("AZ" & varNrows + 1) = Sheets("DATAFORM").Range("E14")
    Sheets("MTN").Range("BA" & varNrows + 1) = Sheets("DATAFORM").Range("E15")
    Sheets("MTN").Range("BK" & varNrows + 1) = Sheets("DATAFORM").Range("E16")
    Sheets("MTN").Range("BR" & varNrows + 1) = Sheets("DATAFORM").Range("E17")

    Sheets("DATAFORM").Range("C3:C19").ClearContents
    Sheets("DATAFORM").Range("E3:C17").ClearContents
   
End Sub

Insert some shape to the "DATAFORM" sheet.
Right click on this shape. Click "Assign maco".
In the listbox select macro "InputingData".
Click "OK". Save Workbook.
Every time you click on this shape any data from sheet "DATAFORM"
will be inserted to the sheet "MTN" in the new row.
If you need to add some new fields for inputing you can modify code folow the logic of the code.
Click to expand...
thanks dear for your kind attention.
It works perfectly
thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Commission Sheet auto load into commission log
Replies
3
Views
100
Rijnsent
Rijnsent
A
  • Question
Breaking up CSV files based on date into individual txt files
Replies
2
Views
229
anuradhagrewal
A
E
  • Question
Copy data from a Data Entry form's table to the correct date in another tab
Replies
0
Views
85
Exceleration65
E
K
  • Locked
  • Question
Altering VBA code to accommodate new columns
Replies
0
Views
126
keranali
K
A
  • Question
Macros for cleaning excel file
2
Replies
18
Views
414
jolivanes
jolivanes

Forum statistics

Threads
1,214,970
Messages
6,122,514
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top