Fillable Form

[nathanmav]

Hi everyone just want to ask how to make a fillable form in excel that will save the entry into another sheet and serves as a database.. like for example i have an invoice form after i enter all the data it will print and automatically save to the database.

 

[fredlo2008]

Unfortunately yeah some of the formatting will have to be changed. The only big thing causing issues here is the merged cells. After thats done I will help you with the coding,

Thanks

 

[nathanmav]

hi fredlo2008 thanks for your reply.. check the sample i attach i remove all the merge cells and put "Data1..Data2.." on all the fields i need to save in the log sheet.
https://docs.google.com/file/d/0B6RmsSizixYMS0VNV0pNX09tdGM/edit?usp=sharing

thanks you again..

 

[fredlo2008]

Hello nathanmav

OK I have been working with your workbook and its coming along very nicely. I used some code to add the data. Take a look and let me know. Keep your eyes opens for errors. Also read the notes I wrote in the code there are fields that I cannot match the log with the form.

This is the main code:

Option Explicit

Sub MainAdd()

Dim Sh As Worksheet
Dim lSh As Worksheet
Dim lLrow As Long


Set Sh = ThisWorkbook.ActiveSheet
Set lSh = ThisWorkbook.Sheets(Left(ActiveSheet.Name, Len(ActiveSheet.Name) - 5) & " Log Sheet")

'determine the last row in the logs
lLrow = lSh.Cells(Rows.Count, "C").End(xlUp).Row + 1

'select the kind of form thats active according to the sheet name

If Sh.Name = "SSA Form" Or Sh.Name = "SDA Form" Then
    Call Add_SDA_SSA(Sh, lSh, lLrow)
Else
    Select Case Sh.Name
        Case "SMA Form"
            Call Add_SMA(Sh, lSh, lLrow)
        Case "SD Form"
            Call Add_SD(Sh, lSh, lLrow)
    End Select
End If


'clear Data so the next form will be ready to fill
Call ClearData

End Sub

Here you have the auxiliary codes:

Private Sub Add_SDA_SSA(Sh As Worksheet, lSh As Worksheet, lLrow As Long)


Dim n As Long
Dim txtRemark As String


'count how many lines the body items has. This is based on the bellow range. Chnage if you think
'another range should be used.
n = WorksheetFunction.CountA(Sh.Range("A20:A26"))


'header section
lSh.Cells(lLrow, "C").Resize(n, 1).Value = Sh.Cells(6, "J").Value
lSh.Cells(lLrow, "D").Resize(n, 1).Value = Sh.Cells(8, "I").Value
lSh.Cells(lLrow, "E").Resize(n, 1).Value = Sh.Cells(8, "K").Value
lSh.Cells(lLrow, "F").Resize(n, 1).Value = Sh.Cells(9, "I").Value
lSh.Cells(lLrow, "G").Resize(n, 1).Value = Sh.Cells(11, "I").Value




'main body section
lSh.Cells(lLrow, "H").Resize(n, 1).Value = Sh.Cells(20, "A").Resize(n, 1).Value
lSh.Cells(lLrow, "I").Resize(n, 1).Value = Sh.Cells(20, "E").Resize(n, 1).Value
lSh.Cells(lLrow, "J").Resize(n, 1).Value = Sh.Cells(20, "F").Resize(n, 1).Value
lSh.Cells(lLrow, "K").Resize(n, 1).Value = Sh.Cells(20, "G").Resize(n, 1).Value


'footer section
txtRemark = Sh.Cells(39, "C").Value & " " & Sh.Cells(40, "C").Value & " " & Sh.Cells(41, "C").Value
lSh.Cells(lLrow, "L").Resize(n, 1).Value = txtRemark


End Sub


Private Sub Add_SD(Sh As Worksheet, lSh As Worksheet, lLrow As Long)


Dim n As Long


'count how many lines the body items has. This is based on the bellow range. Chnage if you think
'another range should be used.
n = WorksheetFunction.CountA(Sh.Range("A20:A31"))


'<<<>>>
'<<<<again entered="" by="" and="" cannot="" be="" associated="" from="" the="" form="" data="">>>>


'header section


lSh.Cells(lLrow, "C").Resize(n, 1).Value = Sh.Cells(3, "H").Value
lSh.Cells(lLrow, "D").Resize(n, 1).Value = Sh.Cells(4, "H").Value
lSh.Cells(lLrow, "E").Resize(n, 1).Value = Sh.Cells(6, "H").Value
lSh.Cells(lLrow, "F").Resize(n, 1).Value = Sh.Cells(11, "B").Value




'main body section
lSh.Cells(lLrow, "G").Resize(n, 1).Value = Sh.Cells(20, "A").Resize(n, 1).Value
lSh.Cells(lLrow, "H").Resize(n, 1).Value = Sh.Cells(20, "B").Resize(n, 1).Value
lSh.Cells(lLrow, "I").Resize(n, 1).Value = Sh.Cells(20, "C").Resize(n, 1).Value
lSh.Cells(lLrow, "J").Resize(n, 1).Value = Sh.Cells(20, "D").Resize(n, 1).Value




'footer section
lSh.Cells(lLrow, "K").Resize(n, 1).Value = Sh.Cells(43, "B").Value


End Sub


Private Sub Add_SMA(Sh As Worksheet, lSh As Worksheet, lLrow As Long)


Dim txtMatDesc As String


'<<<<again entered="" by="" and="" cannot="" be="" associated="" from="" the="" form="" data="" or="" "data="" 17"="">>>>


'header section
lSh.Cells(lLrow, "C").Value = Sh.Cells(3, "J").Value
lSh.Cells(lLrow, "D").Value = Sh.Cells(4, "J").Value
lSh.Cells(lLrow, "E").Value = Sh.Cells(7, "J").Value




'main body section
txtMatDesc = Sh.Cells(15, "B").Value & " " & Sh.Cells(16, "B").Value


lSh.Cells(lLrow, "F").Value = txtMatDesc


lSh.Cells(lLrow, "G").Value = Sh.Cells(17, "C").Value
lSh.Cells(lLrow, "H").Value = Sh.Cells(18, "C").Value
lSh.Cells(lLrow, "I").Value = Sh.Cells(19, "C").Value
lSh.Cells(lLrow, "J").Value = Sh.Cells(18, "I").Value
lSh.Cells(lLrow, "K").Value = Sh.Cells(19, "I").Value




lSh.Cells(lLrow, "L").Value = Sh.Cells(22, "C").Value
lSh.Cells(lLrow, "M").Value = Sh.Cells(23, "C").Value
lSh.Cells(lLrow, "N").Value = Sh.Cells(24, "C").Value


lSh.Cells(lLrow, "O").Value = Sh.Cells(27, "C").Value
lSh.Cells(lLrow, "P").Value = Sh.Cells(30, "F").Value
lSh.Cells(lLrow, "Q").Value = Sh.Cells(31, "F").Value
lSh.Cells(lLrow, "R").Value = Sh.Cells(33, "F").Value
lSh.Cells(lLrow, "S").Value = Sh.Cells(34, "F").Value




'footer section
lSh.Cells(lLrow, "K").Value = Sh.Cells(43, "B").Value


End Sub

and the code to clear all data:

Sub ClearData()


Dim r As Range


Application.ScreenUpdating = False


For Each r In ActiveSheet.Range("A1:K60")
    If r.Interior.ThemeColor = xlThemeColorAccent3 Then
        r.Value = vbNullString
    End If
Next


Application.ScreenUpdating = False


End Sub

I am attaching a copy of the workbook as well.

https://dl.dropboxusercontent.com/u/30987064/sample.xlsm
Thanks</again></again>

 

[nathanmav]

Hi fredlo2008

thank you very very much for this wonderful code you share.

i have some question only, about the SDA and SSA form it is possible that the transmittal number and the ref number increment automatically? here is the format for Transmittal Number "HCCR/DAH/DT/001" and Ref Num. "HCCR/DT/001"

and also about the SMA and SD Form if i enter the request no rev. 0 and update that request no to rev 1 is it possible to make a new data below to that request no with rev 1 because i notice that every new data that i save will automatically inserted in the last row.


thank you again.

 

[fredlo2008]

nathanmav,

I am glad I was able to help you. I would recommend you to start a new thread with the new requirements and the appropriate description and name.

Thanks

 

[nathanmav]

hi fredlo2008

sorry i encounter an error in sda and ssa form regarding the format of transmittal number "HCCR/DAH/DT/001" and refno "HCCR/DT/001"

thank you again.

 

[fredlo2008]

Hi,

Can you tell me if any specific line gets highlighted yellow. What do you mean with "Format" we are not formatting anything.

Also this is a very specific code, please make sure all the sheets are named the same way as in the workbook you provided, a simple extra space or a Capital letter can be disastrous.

Thanks

 

[nathanmav]

hi fredlo2008

here's the error i encounter...

thanks!

 

[nathanmav]

sorry fredlo2008 for disturbing you.. i already solve the problem.. thanks!

 

[nathanmav]

hi fredlo2008

just want to ask something about on how to retrieve the data that i save in another sheet and show it again to the form by typing the request no or id so that i can edit and update the data.. thank you!