Help with clinic patient userform template

Hi all
Looking for some help if possible, I do some work for a non profitable charity organization who provide assistance for people with brain injury. they used to use an access tool for client data which was corrupted and unsaveable, I have managed to get the data and trying to create a new one in excel with ADD, EDIT, SAVE commands etc and combination of combo/list box options in the data fields.

My hope is someone may have a similar usable template as patient data is generally standard layout, I'm ok at editing userforms but struggling to get a start on this one. Any help greatly appreciated and thanks for your time.

Do you need this tool to be simultaneous multi user or just one at a time?

BSB

Hi, thanks for getting back, there are three people that manage the patient information so shared would be ideal but if's easier as a single user I'll tell them that's what it is as they only have paperwork copies at the moment. I have tried so many different variations of this but nothing I feel is sufficient enought to let them start using. Thanks again.

Hi !

So Excel is very not the right tool : not a database, many stuff can't work in share mode, …

Access is far a better tool like any other database software working with multiple users.

Hi Marc
Totally agree access would be the tool to use but its too long since I built in access and its expensive to install on 3 pc for one tool. This is a very small staff group who use excel for most things, I will try building a userform to suit but thought I would try the forum first to see if anyone had a similar template I could build onto rather than from start.

I appreciate your thoughts and do agree with what your saying, just not an option in this case. cheers.

I also agree that Excel is not the ideal tool for this, but it could work.

In order to help really we need more info though.

Do you have a list of data fields with their data types?

Would this tool need to account for the one-to-many relationship that some databases use or would it all be one-to-one?


BSB

Hi BSB, I don't have the field list with me but will send it when I get home later today. the one-to-many would be ideal but as I'm not using access have accepted its not realistic. Given they are currently manually writing the data and then typing into word they have no relational data, at least having it relating to a reference number, I can set up indexing driven from the userform feen into worksheet.

I am new to userform and love them, ok with text fields but not comfortable on the ADD, EDIT, SAVE commands etc or combo/list box options. I'll send on the field list just in case it's doable. Any help would be a real plus then me doing it on my own. Thanks

No problem at all as I'm at work right now anyway so won't be able to look at it until later.

Send over what you have and any details of what you need it to do and we can start building.

BSB

Very much appreciated..

Hi BSB
I have added a sample of what I was looking for, it would need a userform 1 and 2 but if you could help me with the first one I will try and use it as leaning tool for the second one. The Userform 1 would be made up of text, combi and calendar which I hope I have provided in the sample. This would need Add, Edit, Save, Exit buttons at the bottom. I appreciate any help you can provide but have no expectations of your time so thanks again.

OK, walk me through how you envisage this working? Let's start with the "Enquiry Userform" sheet.

There are two distinct blocks of data that need entering. Row 2 and Row 6. Would both be filled out for each record? Who do the two sets of data belong to? Patient(?) and...?

Do you simply need a userform with all of those fields that needs filling out and the data stored in a list elsewhere in the file but with the ability to recall a record to the form, update it and overwrite the changes?

Would you just use the Reference No to identify a record to recall?

BSB

You have it spot on, The two blocks would be a single row of date per ref no. It only appears two to split the view on the userform if that makes sense. If its possible to load a search button to pull the data relating to that ref that would be great. Let me know if im not providing enough into. Thanks

That's all I need for now. Leave it with me.

BSB

That looks brill and definitely what I needed, it means the first userform for enquiry is complete and I can use what you have given me to try building the members userform as its similar. Genuinely can't thank you enough for your time and skills on this and that thanks also comes from the charity who provide this help for people with brain injury as well who are managing all this data manually at the moment.

May need to raise a new post to ask your help if I get stuck but only worst case. Thanks again.

Very happy I could help and more than happy to help further if need be.

I think it still needs some work to stop things like accidentally overwriting records if you key in a reference number incorrectly, but I ran out of time last night.

If you do need more assistance on this project then you can use this thread rather than start a new one. I'll get a notification that way and will jump back in as soon as possible.

Good luck with it.

BSB

Thanks again sincerely.

Hi BSB
Been trying to fix this myself but making it worse I think, I have added data for testing and its not pulling through the prost codes. When I look at the tab order it has the tbAddress3_1 and tbAddress3_2 but these don't show on the userform so I'm assuming its confused with tbPostcod_1 and _2

As I say, tired fixing it before pestering you with it but not quite getting it. could you have a look at the sample you sent me and see if its just a missing in the properties? thanks

Ooops! My bad!

Fixed in the attached version.

BSB.

Great, picking up everything now thanks....

No problem.

How are you getting on with the other form?

BSB

Haven't started it yet as it's pretty much the same as the one you done for me so I plan to just create another userform and amend as needed. I know what you mean about the validations as I've entered a few mistakes and not always sure how to resolve. Can I ask you one more thing re the Exit button? Can I have this set to take it back to the workbook rather than close it? I plan to have everything run of a dashboard as there is a lot data to manage and this will only be the client userform so it would be a anther button to open the Carer's data etc. If you could change the detail below I''ll drop it in. Thanks again.

Private Sub butExit_Click()
'CLOSE USERFORM
Unload Me
'SAVE AND CLOSE WORKBOOK
With ThisWorkbook
.Save
.Close
End With
End Sub

If you just want to go back to the workbook then get rid of the bit from With ThisWorkbook down to End With.

Please Login or Register  to view this content.

That will just unload the form.

As for the validation, there are ways around that. You could have it compare some other details when it looks for the reference number. For example it looks for the ref and finds it's but before it writes data over that it checks to see if the surname is the same and throws up a warning if it's not.

Or you could have the tool auto generate the reference numbers so there's no chance anyone can type the wrong one in. May not be feasible depending on how you generate your refs already.

Also, going back to the very beginning of this thread, perhaps you could use Runtime Access (rather than the full paid for Access) and have the data pour into a database table rather than stored in this workbook. That way you could give a copy of the Excel 'front end' to multi users and by utilising an Access 'back end' you could have simultaneous multi user capabilities without the need to make it a shared workbook (and suffer all the issues that comes with).
Just a thought...


BSB

That worked on the closing, great.
Will look at the access datatool for the future but I'll need to look into as I don't know how to set it up. Thanks again for all your help.

Need to akk another one i'm afraid BSB, I'm trying to add the cbConditions drop down option to the user form, I have added it but it doesn't provide the DataVal list likes the one you set up. Am I missing something? I added a column in the data spreadsheet cbConditions after cbAccommodation but when there is no dropdown when adding a new client.

Hope its an easy one.

Can you attach your current file?

Is there a list to go in the Conditions combobox?

BSB

I have added all the client data but I could take it out and send a blank, is that ok?

Yes that will be fine.
I can then show you what to change and you can do that in the one with all the data.

BSB

Thanks, the conditions should be on the one you put together in the DataVal spreadsheet but I have listed below just in case.
Conditions
Fits
Seizures
Blackouts
None
Other (add to notes)

Should have mentioned, the lists in the column drop downs will change, I had just added some to go through showing them something to ask if it works ok. Don't think this is a problem as I tried adding and changing the one you entered for me but mine didn't work.
Thanks.

Sorry for the delay, was cooking dinner.

You need to add the line in red below to the UserForm initialize event. It will apply the list of values to the Conditions drop down.
If you need to make changes to the list that's no problem, just add/amend/remove from the Conditions table on the DataVal sheet and it'll be taken care of.

Please Login or Register  to view this content.

BSB

Works a treat, so many thanks I owe you..

No problem at all. Happy to help

BSB

Hi BSB
onder if you could help me with this one, I loaded the file into the server in Headway but it won't open the buttons! seems to load ok but when I try to Edit it comes up with the attached error. Thought it might be just a single PC but its the same on all 3 running from the server. The PC's are ob Win 8.1, do think it could be that?

Hi BSB
Not sure if you got the mail I sent as Im not sure how to send it to only you. I am trying to install the tool you put together on Win 8.1 and it doesn't the Command Button Class doesn't seem to like it. I thought it may be a different version of excel as I use 365 but it works on 2016 ok so should be ok. Anything showing on the screen shot I took that you can see relating to the failure?

Thanks.

Hi BSB
Need your help on a command button issue preventing the userform to run them. Thanks

Hi
I took a look through your last attached file "sample" and note that the command button class refers to a "datepicker control". Is this available on the server??

No it's not, is that why I can get in on my laptop but not the office PC's? makes sense my friend, thanks for that.

The file has no date picker control involved at all. It's simply a UserForm designed to act like a date picker control. In theory it should work on any machine with Excel installed.

Your post above (#34) mentions an error but the screen grab shows no info on which error you're getting or which line of code is causing it.
Could you give more info?

The folder you have the file in may not be recognised by your network as a 'trusted location'. If that's the case then you may be prompted to enable macros when opening the file. If you don't do that then nothing will work. If it allows you to then do that and see what happens.

If you still have issues then you may need to have a chat with your network admin.

BSB

Hi BSB
Thanks for getting back, can't seem to get a screendump with the error, it says Compile Error: Can't find project or library. The highlighted vba error is

Please Login or Register  to view this content.

Works fine on my laptop, I loaded into 3 PC's and they don't use a server just a external shared hard drive, I tried it on my work PC and it gave me the same error. Hope the error tells you something I'm missing, let me know if you have any thoughts. Thanks

(cleared as misread, my bad)

I don't know enough about your computer setup or your Excel install to be able to work out why the code doesn't work for you I'm afraid.

If you press F8 does it get past that line of code? If so then keep pressing F8 to step through line by line and see which line it stops at then.

You could get rid of the date picker userform altogether and have the users type in a date.
It can be a little more work because you have to write code to validate the entries (which is why I added the date picker in the first place) but it can be done.

BSB

I think it might be the MS date and time picker needs ticked in the tools options, worst case scenario I'll make it a type in date? Can you tell me what to remove to do that without impacting anything else? thanks

Meant to say, I'll try the F8 option to see where it stops. thanks

Originally Posted by JNEWMAN

I think it might be the MS date and time picker needs ticked in the tools options

It's not that, I promise. There is no built in date picker involved in this at all. The calendar that pops up is a custom one that I built, is nothing more than a userform and has NO involvement at all with date pickers other than the name I gave it...

Originally Posted by JNEWMAN

Meant to say, I'll try the F8 option to see where it stops. thanks

Let's see what the outcome of this is before we go down the route of removing the calendar and validating a manually typed date.

BSB

I see what you mean on the calendar, as I saw the custom one built in so I'll rule that out. Let you know how it goes. Thanks

Hi BSB
I tried using F8 on the script on the command button error but it still only brings up the error below, not sure what to change on the PC's it doesn't work on. I know its not your app thats causing the issue as it works fine on mine and appreciate its difficult for you to know whats causing it not seeing a pc it doesn't work on. Error.jpg

If you can think of anything let me know but I'll work around something I'm sure. Thanks

Open the VB Editor (Alt+F11) then go to "Tools / References". A window will pop up with a list of reference files. Have a look through that and see if any are ticked but say "Missing" next to them.

BSB

Gave that try BSB, It shows MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)
Which appears to be the MSCOMCT2.OCX that shows if you don't load it to use the calendar, but as you have added a calendar I can't think why it would look for it to be downloaded.

If you take the tick out of that one, save the file, close Excel then re-open the file, does it work then?

BSB

YES, thats it good to go. Thanks again

No probs. Glad we got there in the end

BSB

Feels good to get it working after your time building it Brill.

A joint effort!

I still think it would work better with a database back end to hold the data....

BSB

Totally agree but don't think I could support it for them.

Fair enough.

Good luck with it and shout if you need more help.

BSB

Hi BSB
Is there anyway I can convert the XLM in the tool you put together? it's stopping it being shareable. Worse case scenario they will need to just access it individually but thought I would ask. Thanks

Hi BSB
IS there anyway I can make the the userform you helped me with a shared tool? I have tried going through different routes but not finding a resolve. Possibly not doable as its telling me it can't work with XML but thought I would ask.

Thanks