Userform Code Required to Control Data between Database and Input Sheet

[Rudidw]

Hi, I really hope someone can assist with a few issues I have with my userform!

I only know very little VBA and therefore the help request. I have attached a workbook with a working userform and some functions already working but I need help with other functions to control data between the database sheet and the Input sheets.

I have a userform that performs a few functions that work through a listbox called "ResultBox" and I would like to achieve the following:

1. My code not working here: On the userform when you click "New Student Button" then Input 1 sheet must open, all current Input data must be deleted from Input 1 and Input 2 sheets and then a new student ID number must be added to cell "C5" on the Input 1 sheet which it will get by looking what the last ID number was on the DB sheet. I do have some code that I got from another sheet but I can't get it to work here.

2. On the userform, when you select a student displayed in the ResultBox (after clicking All Students or Current Students button) and then click on Load Student Info button then it must transfer all associated data from the DB sheet to the corresponding input cells on sheets Input 1 and Input 2

3. On the userform, when you select a student displayed in the ResultBox and click the Delete Student button also on the userform, then all that student's data must be deleted from the DB sheet including the ID number (so basically the whole row must be deleted)

4. Lastly how does one set the formatting in the ResultBox so that the last 3 columns show as currency amounts (3rd and last columns) and a percentage(%) amount with 2 decimal places in the second last column?

Thanks

[davesexcel]

For the first query.

You are wanting to find the last row in the Db sheet and return the value in Column A. You are using the incorrect code sheet name.
In you DB sheet, the last row is 555, so using rows.count would not work.
-
pic1.jpg

If you were to get a correct student number you want to place it in the Input sheet, but you are trying to place it in a sheet that does not exist so the code errors, you have an error trap, so you do not realize the code errors.
-
pic2.jpg


-
I suppose you can simplify it without using a function to figure out what the next Student # would be.
This code is just to answer your original question 1, it is not made to be an answer for any other issues you may have.

Please Login or Register  to view this content.

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Rudidw]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Hi apologies for the mess up...I have fixed the thread title as requested.

[Rudidw]

Please Login or Register  to view this content.

[/QUOTE]

Hi davesexcel,

Thank you for the help on the first problem...The problem I experience with your code is that you reference line 555 as LstRw which works fine until I have to delete a row (which would be the case in point 3. of my initial request) as it the gives a Type mismatch error. Is there a way to make the code have to count (rows.count) the row and determine what the actual last row is at that point in time?

[sintek]

Rudi...If you want to make use of ListObjects in your sheets then you must take advantage of what they have to offer and stop treating them as Ranges...
Two of the many sites available...
https://www.automateexcel.com/vba/ta...d-listobjects/
https://www.thespreadsheetguru.com/b...t-excel-tables

[Rudidw]

Rudi...If you want to make use of ListObjects in your sheets then you must take advantage of what they have to offer and stop treating them as Ranges...
Two of the many sites available...
https://www.automateexcel.com/vba/ta...d-listobjects/
https://www.thespreadsheetguru.com/b...t-excel-tables

Hi sintek,

Thanks, as per my other post my VBA knowledge is very limited but I am trying to understand it when someone sends me something, but I just don't have the skills to write it myself. What is interesting to me is the many different ways that a piece of code can be written and give the same result - but this makes it more confusing

It is difficult sometimes to have the intention to get something done but not the knowledge to do so...lucky a forum like this exists where you can ask for help to a problem

[Rudidw]

For the first query.

You are wanting to find the last row in the Db sheet and return the value in Column A. You are using the incorrect code sheet name.
In you DB sheet, the last row is 555, so using rows.count would not work.
-
If you were to get a correct student number you want to place it in the Input sheet, but you are trying to place it in a sheet that does not exist so the code errors, you have an error trap, so you do not realize the code errors.
-

Hi davesexcel, apologies for the silly mistakes I made and thanks for pointing them out!

The code I used was from another sheet I had and failed to double check the sheet names were correct, so I made the changes to the sheet names as you pointed out and I also deleted all empty rows so that rows.count works and now my original code works great.