Need VBA Help with Userforms

[JasonMcQueen]

I'm trying to create a userform to enable users of a worksheet to enter the data quickly as the sheet is quite wide. I've managed to create the general layout of the userform but havent got a clue on where to start with the VBA code required to facilitate the data entry and then transference into the worksheet. I've tried searching online but there doesnt seem to be any decent guides on how to do such a thing with VBA (if anyone knows of any please point me in the right direction. Can anyone help?

[maniacb]

This site has a great resource for creating a Userform. https://www.contextures.com/xlUserForm02.html

Also check out this link as to how to use the built in Userform. https://www.excelforum.com/excel-for...g-numbers.html

[torachan]

Hi.
I am just in the closing stages of helping out on a similar app.
Take a look at the approach used in the last file posted in the link below.
Out of interest I will have a more detailed look at yours in the next day or two.
torachan

https://www.excelforum.com/excel-pro...ml#post5351016

[Logit]

.
Download this example workbook and study the code. The code can be edited
to conform to your project ...

https://www.amazon.com/clouddrive/sh...J7hoIScTbEWCPo

Please Login or Register  to view this content.

[JasonMcQueen]

Thanks, I'll take a look.

[JasonMcQueen]

Hi.
I am just in the closing stages of helping out on a similar app.
Take a look at the approach used in the last file posted in the link below.
Out of interest I will have a more detailed look at yours in the next day or two.
torachan

That would be great. I'll also see what I can learn for myself from the previous links.

[torachan]

Stage one - loading comboboxes with validation data - converted your ranges of data into tables
As progress takes place table expansion is cleaner than range manipulation.
From experience always have a blank column between tables - prevents interaction between tables during coded actions.
The UserForm initialization event calls two subs which are in module1 ('ClearForm' and 'LoadCombos')
On the basis of program 'once' use 'many times' as the app develops these subs would be called on a number of occasions.
If this stage meets your needs we can then look at transferring data to/from the 'database' sheet.
As this is also in table format reduce its size to the first single row - it expands organically as data is added.
The 'database' sheet has no on sheet 'data validation' or 'formulas' - it is practice to do this within your coding.
As a matter of curiosity, will all the hidden sheets play any part in this app.
happy coding.
torachan

[JasonMcQueen]

Hi, yes thats exactly what I'm trying to do. Thank you. The hidden sheets are for a separate dashboard that I stripped out for this file to avoid confusion.

[torachan]

I have introduced the save/update routines but I am not satisfied by the lack of speed.
There appears to be a lot going on in the rest of the workbook, such as continual calculations whenever there is a change in any sheet.
What is the basic interfacing between the sheets so that it can be isolated when updating your new inputting userform ????
torachan.

[JasonMcQueen]

I've attached a copy of the full worksheet just so you can see everything that is going on. Obviously, I'm happy for pointers on how to simplify things. I've just had to make do with the extent of my knowledge on how to use Excel.

[JasonMcQueen]

Just wondering if that helped or if there is anything else I can do to assist?

[torachan]

Sorry, missed your post #10
I will take a look latter today.

[JasonMcQueen]

No problem at all, I appreciate the help.

[torachan]

Despite switching to manual calculation while the userform is running my 'little steam driven computer' has had a nervous breakdown.
I have tried to find what is switching automatic calculations back on, all to no avail so far.
Any writing to/or reading from a sheet puts the workbook into an endless procedure, such that my computer gives up and 'hangs'.
If you change a value in any sheet how long does your computer take before handing control back to you.
I find it frustrating dealing with 'glitches' that have no apparent logic, switching off the data validation arrows on the 'AccidentLog' took 5.5 minutes to action.
After which all the column headers changed to the word 'False' for no apparent reason.
Regrettably despite considerable experience I would take another path if you really want to use UserForm input.
As there does not appear to be any on-sheet formula referencing any other sheets other than 'Validation' I would consider a separate workbook.

[JasonMcQueen]

haha, thanks. There is normally a few seconds before when you make a change but I always put that down to the sheet being hosted on our company SharePoint drive.

What if we essentially started the workbook from scratch? If you could get a working userform to enter the data on the accident log that works correctly I can rebuild all the other data and charts from the masterfile and try and eliminate any problems and streamline the workflow.

[torachan]

Do my best for Saturday - tomorrows a busy day.

[JasonMcQueen]

No problem at all and as I say, I fully appreciate the effort. There's no time limit on this, its a side project to try and improve the user experience for people in my team who are even more technically challenged when it comes to Excel than I am

[torachan]

As promised a standalone UserForm as a framework to build on.
The basic functions are there 'ADD' / 'UPDATE' / 'REMOVE'
The form probably needs finishing touches to your personal layout.
When moving "label/textbox/combobox" units remember the textbox is held on top of the combobox using 'Zorder() - with just the righthand end of the combobox accessible beyond the textbox.
You will probably want to insert a unique number generator to make the records unique - I have added a search combo at the bottom of the form just to search on column 1.
You may require a more complex method of searching after giving the form a trial.
Happy coding.
torachan.

Nearly forgot to remind you the name labels for the textboxes are loaded at initialisation from the Table1 headers.

[JasonMcQueen]

Thats great, thanks for your help. I'll start rebuilding the data and that should show where I need to make changes.

[JasonMcQueen]

Hi,

I've been through the userform and made the changes that I can but I need a bit of help with a couple of points.

Date of accident is in US format MM/DD/YY on the form, how do I change that to DD/MM/YY?
Completed dates and final close dates are in number format rather than date format, how do I change that?

I've had a look through the code in Module 1 but cannot see what controls these elements.

Also, how would I create a unique ID generator in the userform rather than having the user enter one manually? I'd like it to automatically +1 to the last entry in that column when a new record is created.
What I'm working to is having the worksheet locked and then any modification or recovery is done via the userform.

Finally, is it possible to add a 'search by name' function along with searching by unique ID? I know this would return several records but would it be possible to add 'Prev' 'next' arrows to allow users to search through the returned lines.

[torachan]

Date format corrected.
Auto-increment I.D. works as you clear form for new entry.
I have added a vertical scroll bar as I cannot see all the form on my smaller screen.
I have added a combobox to search on name this then loads the choices to the adjacent listbox for selection to view.
To edit select the unique I.D. with 'Search' combobox.
As the listbox search uses a filter it loses its row number (I would have to use a helper column to keep track of row number otherwise).
The last 6 ? entries have added names just for me to check searches worked.
torachan.

[JasonMcQueen]

Thanks, thats great but I'm getting a compile error when using the 'clear' button. Am I using it wrong?

[torachan]

Sorry,
my rushed coding, failed to declare a new variable properly in the auto-increment.
attached should be O.K.
torachan.

[JasonMcQueen]

No problem. Thats seems to have done the trick. Thanks again for all your help!