vba user form with mutiple lines

[Nagin]

Hi,

I am fairly new to vba and developing a user-form, after searching online I found a template to which I have adapted to my requirements. I have got the basics working, however I am struggling to get the userform to update on multiple lines. What I would like the userform to do is the following;

The user will enter a list of patients details for that day, with the same date assigned to each row. This data is then transferred to the 'data' sheet. When the user either selects from the drop down 'select date to update' or uses the view records scroll buttons, I would like the form to bring up all patients for that date and not just the first patient on that date.

Any assistance on this would be much appreciated, or suggestions of a better way of recording, updating this information.

Thanks

Nagin

 

[Monty]

Hello Nagin.

As per my knowledge...this can be better developed in Access..as viewing of records is much comfortable in MS access...Even if it is developed in Excel..you may not find exiting..

Let's what our experts says!

 

[SirJB7]

Hi, Nagin!

Firstly a clarification. What you have in worksheet Input is just a worksheet with a design that looks like a form but it's still a worksheet (basically cells, rows & columns) with a few controls that simulate a recordset edition (used by userform's DataControl controls).

Precisely in an userform you can add controls that behave like the 4-control button simulation that you have at worksheet Input: first record, previous record, next record, last record. The place where the recordset is held/stored in your case is worksheet Data.

Now, about the 4 action buttons (Update, Add, New, Delete), all of them operate on row 9 only, where the other 4 buttons operate too. AFAICS there's no easy way to expand it's operation thru row 33 since both groups of controls and the code that's behind them has not been thought to do that.

And in fact, there's no need to build something that does that, since Excel itself does that with rows in its worksheets.

My recommendation has many parts, and no solution provided (sorry about it, but I prefer to be honest):
a) investigate, dig, read, a lot more about how to process and store data in Excel, i.e., how to standardize columns of one or more worksheets to store and hold data, and how to build client worksheets that work as user interface with those previous... I'd start using Google.
b) once done this, analyze your data, define how many subsets you have, define where you'd store it, and check how it matches with what you've found in a)
c) once you feel comfortable with the previous model, try to read about userforms in Excel, then you'll have to get in touch and manage to handle VBA code, and try bo build a new model that replaces that of b).

Hope it helps.

Regards!

 

[Nagin]

Hi SirJB7,

Thank you so much for your insight on this, you are correct in that technically it is not a userform, rather a spreadsheet with form buttons that manipulates the spreadsheet.

Going forward, if I were to create a actual userform, could I have multiple text boxs which relate to each patient details, and have a unique ID to call up that record.

Thanks

 

[SirJB7]

Hi, Nagin!
If you create an userform, you can put as many lines as you want, but you'll have to provide the code for loading them from their storage (worksheet, range) into userform controls, and then the inverse process, from controls to cells range.
Regards!