Aplikasi Excel Form Input Data Siswa dengan Macro

Excel Form Application for Input Student’s data by Macro / Visual Basic

Hey Guys, Moshi-Moshi, Annyeong, Ni Hao (Too much for greeting.... ^_^)

In this occasion, I will give you information about application in Microsoft Excel. This is often called “Visual Basic”. This program is needed by university student who take “Computer Program” subject especially science education departement in Surabaya State University.

Ok, This is step for make Input student’s data in Visual Basic. Give your attention!

1. Open your microsoft excel and make table which contain “NIS, Name, Class, Gender”

 

2. Click Developer in Toolbar. (If there isn’t developer in your toolbar, please click ‘office button’, click ‘word options’, choose ‘popular’ and then give checklist in box “show developer tab in ribbon”)

3. Click macro security, in macro setting checklist “enable all macros (not recommended; potentially dangerous code run), klik ok. Click Macros (in developer). Please type Form in dialog box. And then click ‘create’.

   

4. Copy this code in the module 1 (code)

Sub FORM()
UserForm1.Show
Sheets("sheet1").Select
End Sub

Private Sub bukasheet()
Sheets("Database").Select
UserForm1.Show
End Sub

 

5. Click Insert-Userform. Make table as the picture below in userform1 (userform). You can use toolbox especially label (for write the word in left side) and textbox (for box in right side). Toolbox – command button for click box “Tambah data (enter data)” and “Tutup (close)”

 

6. Change name for every text box and command button. You can do this in properties (box in left bottom side). Example: for NIS box, you can change name with “TNIS”. (you must write letter “T” in the first every name word.)

7. Copy this code in userform1 (code)

Private Sub Ttambahdata_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")

'menemukan baris kosong pada database siswa
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check untuk sebuah nis
If Trim(Me.tnis.Value) = "" Then
Me.tnis.SetFocus
MsgBox "Masukan NIS terlebih dahulu"
Exit Sub
End If

'copy data ke database siswa
ws.Cells(iRow, 1).Value = Me.Tnis.Value
ws.Cells(iRow, 2).Value = Me.Tnama.Value
ws.Cells(iRow, 3).Value = Me.TclassValue
ws.Cells(iRow, 4).Value = Me.Tgender.Value

'clear data siswa
Me.Tnis.Value = ""
Me.Tnama.Value = ""
Me.Tclass.Value = ""
Me.Tgender.Value = ""
Me.tnis.SetFocus

End Sub

Private Sub Ttutup_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Gunakan Tombol TUTUP PROGRAM untuk Keluar"

End If
End Sub

8. And then save the file, with save as file “Excel Macro-Enable Workbook”, klik Ok.

9. Close your Macros or Virtual Basic

10. Create link for Run your Macros or Virtual Basic with steps below :

a) Click developer – insert – button (form control)

b) in the dialog box, click your project (Form) and click Ok. 

c) Try click your button. This is final step.

Let’s Try It.

Keep Spirit, Jia you, Ganbatte, Hwaiting

( If you get some trouble, contact me  FB: Younichi Miyashita, twitter: @Ydhiyanti or Email: shitadhiyanti@gmail.com.  I will help you soon)