Solved How do I create a form in Excel 2016 with an update button?

October 26, 2016 at 07:41:16
Specs: Macintosh
I want to create a form that different users will be filling out and have a button that they click to transfer the information into a summary sheet. So each new entry would transfer to the next row on the summary sheet. The form has some headings that are simple like name and date but other headings are questions that the user will answer with a 1 for yes and a 0 for no. There is also a comment heading so exact text will need to transfer to the summary sheet. I am using Excel 2016 on a Mac.

See More: How do I create a form in Excel 2016 with an update button?

Report •

#1
October 26, 2016 at 08:48:22
That is very simple and straight forward, the first thing you need to do is tell us what the form will look like, how many text boxes, buttons etc

What i will do is create a small example for you that will take a couple of entries from a form and update a summary sheet. I can email this to younif you pm me your email address, i will also post the code and design here for completeness.

I'll have something simple for you for tomorrow


Report •

#2
October 26, 2016 at 16:38:41
the form uses this format:
Column A has the following Headers:
Date
Time
Reader
# of other Adults
# of Students
Book Title
Observer

Column B is the response

Column A continues with the following statements:

ALL students have an individual communication system that meets their access needs (e.g., Universal Core with partner-assisted scanning layout).
Content and complexity of book is appropriate for age/grade/ability level of students.
Before reading, the adult connects book to previously taught information or experiences.
Core-based comments have been preplanned and are used in the lesson.
Adults comment while reading using communication systems that are similar to the students’ individual systems.
Adults provide adequate wait time and ask or encourage students to participate page-by-page.
Adults recognize, respond to, and expand on students' efforts to participate and communicate.
The adult reads with enthusiasm in a way that fosters a joy for reading.

Summary and Additional Comments:
In column B they will enter a 1 for yes and a 0 for no
Column C will have comments for each one of the statements.

One button at the bottom to transfer the information into a summary sheet.

Thanks so much for your help!


Report •

#3
October 27, 2016 at 01:03:40
✔ Best Answer
Ok so I have put an example together for you, I have not used your data above but have used my own. This will hopefully be enough for you to understand what you need to do, in order to create your own form.

The images of the form and worksheet can be found below
http://postimg.org/gallery/17hqyv0em/

Design Details

1 userform - named FrmuserDetails
4 text boxes - named 'TxtFName', 'TxtSName', 'TxtDOB', 'TxtFF'
2 command buttons - named 'CmdSave', CmdCancel

Code:

Dim invalidEntry As Boolean

Private Sub CmdSave_Click()
    Dim ctl As Control
    
    For Each ctl In FrmUserDetails.Controls
    
        If TypeOf ctl Is MSForms.TextBox Then
            If ctl.Text = Empty Then
                invalidEntry = True
            Else
                invalidEntry = False
            End If
        End If
    Next ctl
    
    If invalidEntry Then
        MsgBox "All fields must be completed", vbCritical, "Invalid Entry"
        Exit Sub
    ElseIf IsDate(TXtDOB.Text) = False Then
        MsgBox "You must enter a valid date", vbCritical, "Errm no"
        TXtDOB.SetFocus
        Exit Sub
    Else
        SaveuserData
    End If
    
    
End Sub

Private Sub SaveuserData()
    
    Dim iCon As Control
    Dim NextRow As Integer
    
    NextRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1
        
    Sheets("Summary").Range("A" & NextRow) = txtFName.Text
    Sheets("Summary").Range("B" & NextRow) = TxtSName.Text
    Sheets("Summary").Range("C" & NextRow) = TXtDOB.Text
    Sheets("Summary").Range("D" & NextRow) = TxtFF.Text
    
    For Each iCon In FrmUserDetails.Controls
        If TypeOf iCon Is MSForms.TextBox Then
            iCon.Text = Empty
        End If
    Next iCon
    
    MsgBox "All details have been saved", vbInformation, "Data Saved"
       
End Sub

All this does is, request some input in the form of txtboxes, validates the entries then saves them to the next available row on the 'Summary' worksheet.


Report •

Related Solutions

#4
October 28, 2016 at 06:28:16
Thanks so much for your time and expertise! This is very helpful.

Report •

Ask Question