Visitor registry with signatures, excel the way to go ?

[MasterBash]

Hello,

I posted here : https://www.excelforum.com/office-365/1426937-visitor-registry-with-signatures-what-software-to-use.html

Once again, I would like the mention how much I appreciate the help that I received on this forum. Unfortunately, I am terrible at Excel/VBA, but I wish I could contribute to this forum too. I am certainly learning a bit.

I am trying to make a visitor/driver registry where people can register themselves when they come in.
One point of entry, one device to register. I will be needing a graphical tablet for the signature. We currently write down everything manually ourselves, except for the signature.

I believe Excel will be the best tool for it, even though it will be similar to a database. I thought about Microsoft Access, but not only I don't know much about Access, but I believe it might be a bit harder to create forms.

Here is a sample of the workbook : 31742

First, I would also like some thoughts if Excel appears to be the correct software to use, or should I forget about it altogether ? I don't mind using another software.

Second, there are some things wrong in the sheet Registry. I am unable to make the required fields all required. I can only do an "Or" condition and as soon as I click Submit, if only one condition is met (only one field is filled), it sends the e-mail. The other problem is the Message Box that says the request has been sent successfully. It appears before even clicking "Send", and I believe there should be some error handling there. Eventually I will be removing the '.Send and it will be sending automatically. I spent a long time trying to fix the condition so all fields must be required, but no luck. Also, after sending the message, I tried a .ClearContents, but it does not seem to play well with merged cell. As a workaround, I chose to empty cells with "". I don't know if there are any drawbacks to doing it this way. Also, for the signature, I chose to clear shapes on the worksheet on submit. However, it only appears to clear the signature (shape) after the message box "Your request has been sent successfully". Why is it not clearing it before the message box ?

The Submit button already sends the form by e-mail. I had to send it as an image, because of the signature. If I were to send it as a table format, the signature would appear as an image below the table and not in the cell. I wish it would be a table and the signature in the correct place, to allow copy and paste of the fields, but that is not a problem, maybe a quality improvement in the future.

Now the biggest task and one of the reasons why I chose Excel over Access, is the form itself. I would like the Submit button to also add as a row in the data sheet, including the signature. The Date would be filled as the current day's date. Time of arrive would automatically get filled inside the form as soon as the visitor/driver starts typing the information. Is that possible to do ? I am sure the other fields are possible, but I am not sure about the signature and it is very important for the signature to be there, because we must keep all records for 3 years.

Last question. If the excel workbook becomes too heavy with too many entries, should we simply retrieve the data from Excel to Access as a way of keeping the records, then clearing the Data sheet in Excel ?

Thank you !

[June7]

It's easy to create data entry forms in Access. Easier than Excel. If you really meant design a "paper" sign in form for printing, yes Excel might be easier but Access can do it with report object.

I would not use Excel.

[MasterBash]

Thank you very much.

I thought as much. Access is a great database tool, especially in this case because it doesn't have to be shared and no one else has to actually learn it or have the application installed... :)

I believe it is now time for me to learn how to use it. I will mark this thread as solved and I will post my questions in the correct forum.

Once again, thank you !

[MasterBash]

After talking to my IT team, it looks like Excel if the only tool I can use for this...

I updated the workbook. 31746

It looks like I was able to make some cells required without the "Or" condition. It also seems like "DoEvents" makes the message box appear after the email is sent. I also added the .ClearContents for merged cells. I will try adding an error message in case the e-mail doesn't send.

There are a few things I would like to add and I am not exactly sure how to accomplish those :

First, automatically add Time of arrival as soon as the person starts filling out the registration form. However, my biggest issue here is how do I also add a Time picker for that field ? I can find date/time pickers on the net, but I can't find Time pickers only.

Second, On submit, I need it to transfer all that data to the sheet "Data". I will be splitting the First name and Last name into different columns on the data sheet and different rows on the registry sheet. I will be testing a few options this week to see if those works.

Third, I believe this is the biggest challenge. How do I "stick" the signature inside the cells I made for this ? For now, anyone can draw a signature anywhere on the sheet. On submit, I would like to automatically save the signature under first name last name date time.jpg or .png and add a link to it in the Data sheet, in the same row.

Thank you !

[barnie]

To automatically add the current time when a user starts filling out the form, you can use the Worksheet_Change event to update a specific cell. For a time picker, Excel does not natively support a time picker control, but you can use a UserForm with a Microsoft Date and Time Picker Control if you have it installed.

[June7]

If you want to insert images in cells, review https://support.microsoft.com/en-us/office/image-function-7e112975-5e52-4f2a-b9da-1d913d51f5d5?ns=excel&version=90&ui=en-us&rs=en-us&ad=us

Alternatively, save image filepath as a hyperlink in cell. Review https://support.microsoft.com/en-us/office/hyperlink-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f

[MasterBash]

31749

I made some progress when it comes to the signature. I found a script online to help. However, similar to my previous project, I am still having problems with saving the file name, this time as first name, last last, date and time.

Inside the User form :


Private Sub Use_Click()

Dim objInk As MSINKAUTLib.InkPicture
Dim bytArr() As Byte
Dim File1 As String


FilePath = "C:\Test" & "\" & "Signature.png"


Set objInk = Me.SignPicture


If objInk.Ink.Strokes.Count > 0 Then
bytArr = objInk.Ink.Save(2)
Open FilePath For Binary As #1
Put #1, , bytArr
Close #1
End If


Signature.File = FilePath


Unload Me
End Sub


The file saves just fine as signature.png. Problem comes when I try to add first name, last name, date and time to it. I tried using cell range, but it doesnt seem to like that. Anyone know why ? :( In any case, I believe it would be better to save the signature once the Submit button is clicked, instead of generating a file when "Use Signature" in the userform is clicked, right ? That way it doesn't cause problems if the visitor fills the signature field first. Initially, it was supposed to save the file in the temp folder and delete the file afterward.

I also made the signature user form pop up when the cell is selected. However, I believe by doing it that way, the signature might be "Behind" the cell because it does not appear. Using it as a button works fine. Maybe that is bad design, I don't know. Is there a better alternative ?

I will be doing some research tomorrow so I can automatically "Place in cell" the signature instead of doing it manually.

Shown my ideas to the management team. They liked it and approved it. Right now, we are still writing everything by hand on paper. However, I am still very far from completing this, but I am slowly making progress.

[June7]

Where is code attempting to build filepath referencing cells to get first name and last name?

If all you want is current date/time: Format(Now(), "yyyymmdd_hhnn")

This worked for me.

With Worksheets("Registry")
FilePath = "C:\Test\" & _
.Range("H17") & "_" & .Range("H13") & "_" & Format(Now(), "yyyymmdd_hhnn") & "_Signature.png"

End With

Signature file saved and displayed on worksheet.

[MasterBash]

I tried to change it in the signature_pad user form, but I chose against it. I think that wouldn't be optimal. I will save it once I click the Submit button.

Speaking about the signature. I tried adding

SignatureImage.PlacePictureInCell
SignatureImage.zOrder msoBringToFront


to the Signature module, but it appears to be incorrect ? At first glance, that would seem like the correct place to put it, because the sizing elements are there. The "Bring to front" is because I made the cell itself pop-up the userform, but I don't know where the signature is going once I click "Use signature", I am guessing it may be hidden behind the cell...


SignatureImage.Top = Range("H41").Top
SignatureImage.Left = Range("H41").Left
SignatureImage.PlacePictureInCell
SignatureImage.zOrder msoBringToFront


Last 2 lines are not working. I really need to read more about VBA...

Cross-posted here : https://www.excelforum.com/excel-programming-vba-macros/1427229-transfer-form-data-to-a-table-on-submit.html#post5973183

[MasterBash]

I received an answer for #2, the data is being transfered.



Dim lo As ListObject: Set lo = ThisWorkbook.Sheets("Data").ListObjects("Table1")
Dim lr As ListRow: Set lr = lo.ListRows.Add

With lr
.Range(1) = Range("C3")
.Range(2) = Range("C5")
.Range(3) = Range("C7")
.Range(4) = Date
.Range(5) = Range("C9")
.Range(6) = Range("C11")
.Range(7) = Range("C13")
.Range(9) = Range("C17")
End With

By rearranging the columns, that worked. :)

31753

I added the reference requirement for signature. Reference Microsoft Tablet PC Type library, version 1.0 is required.

Thank you.

[MasterBash]

31755

Alright. I am still working on this. PlacePictureInCell is now working. The signature is inside C19. It works with the filepath and everything. Thank you June.

However, it is saving some kind of gibberish like this : https://imgur.com/a/RCovDhL (I can't seem to post image using Insert Image ?).

Resolution is so low and sometimes there are random black bars across the picture ? How can I make the background white ?

https://imgur.com/a/RCovDhL
https://imgur.com/a/RCovDhL

[June7]

How do I make signature with this version of workbook?

[MasterBash]

Clicking on the C19 cell should make the user form appear.

[June7]

The saved image file looks fine to me with white background.

However, no signature shows up in email.

Older version did include signature in email.

Also, subsequent attempts trigger "must enter signature" msg and if I save this workbook, continue to get this when reopened.

[MasterBash]

Yes, I added some rows over time, so the signature in the e-mail is not showing up. Eventually, I may simply add extra rows to emailRange.

The signature looks fine with the white background, but I do not know how to save the file with a white background.

It is possible that you may not have the option PlacePictureInCell ? I believe it is a relatively new option (2023?). Some computers at work do not have this option, but luckily, the one that I will be using for the registration does. If you have the option, there should be an icon on the top right of the signature when selected (the code should do it automatically). If you don't, removing
If IsEmpty(Range("C19").Value) = True Then Err.Raise vbError, , "Please enter your signature." from the Registration form will do the trick, when it comes to the validation. I haven't found a workaround for this yet.

[June7]

My saved image has white background and resolution is good. I have no idea why you don't get this result.

Yes, I did comment that line so I could repeat code execution.

PlacePictureInCell not available with Excel2021.

[MasterBash]

Thank you. I messed around a bit with my settings and I can confirm that dark theme is causing the signature to look like this. When I use light theme, the signature has a white background and the resolution is good. I will not change anything in this case, because I know it works.

Last question, then I think everything will be good when it comes to the signature... Is there a way I can take the code from the user form and make it save to the drive only when I click "Submit" ? I am wondering because the way it currently is, if the visitor fills out his signature first, I don't get the first name and last name in the file. I can use the filepath outside of the signature code, under the submit button, but I don't know how I can point the signature and make sure it saves.

[June7]

I would not allow activation of user form until other data is filled in.

[MasterBash]

That makes sense. Thank you once again. :)