Userform Formatting Not Correct for Currency and Percentage Values

Hi, I really hope someone can assist!

I have attached a workbook with a working userform for easy reference.

I have a userform that when you click the All Students or Current Students buttons, this would populate a main listbox called "ResultBox" and I would like to achieve the following:

1. Fix the formatting in the ResultBox so that the last 3 columns show the correct formatting. Currency amounts (3rd and last columns)with 2 decimal places, and a percentage(%) amount with 2 decimal places in the second last column

2. Name the columns in the Resultbox according to the columns referenced to in the database sheet (DB)

Thanks

Hi sintek, a fellow SA I see thanks for the quick reply!

Thing is I know very little of VBA and have to rely on the expertise of fellow forum members and trust that code I receive works best.

Any help with optimised code will be appreciated though, but so far just using the code provided here that just works (bloated or not), not the best way but atleast works for now.

But still really appreciate the help I get from forum members

Originally Posted by sintek

Yes...hallo daar mater...

So does the above code solve part 1?
What i meant about the column widths set to 0...Just remove those column numbers from the array if you are not needing them... i.e. 4 & 6

Dankie maat,

It works fine in the example book but not in my project book - I suspect it is due to all the extra empty rows I have but I can't just delete them (see explanation below)
As for the column widths (zeros). No I can't just remove those columns because on the userform listbox (ResultBox), only specific columns data must be displayed in a certain order (like 20 comes before 18, 19). Therefore out of the 20 columns in the DB sheet only 7 specific columns are displayed in ResultBox but then when you click on a "student" in ResultBox then ListBox2 would then display additional data not displayed in ResultBox. My current code all works 100% but just need to get the formatting right preferably by using the same code I provided if possible. So your "new" structured code is no working in my project book.

Explanation...The problem is that the example workbook I shared here is only a fraction of the size of my actual project workbook so I have to replicate as best as I can the example book to get my point across.

In my project workbook I have to use data stored on DB sheet that has about 90 rows (continuously increasing) that stores input data over 773 columns from about 60 different worksheets, where the example book only uses 10 rows in the DB sheet and 2 worksheets, just to try replicate my project workbook (which is a monster workbook ) so I can't just delete all these empty rows and columns in this example book.

I can however delete all the empty rows but if a new "student" has to be saved to the DB sheet using another set of code (not included in this example book) that would need to increase the table size by adding in another line below the entry every time a new "student" is added, but currently don't have that function therefore there are many extra rows currently in the table that need be in the DB sheet.

Sorry for the long message but things are just not that simple

So just to clarify I need to fix the formatting of the last three columns of listbox "Resultbox" preferably using my existing code (that works 100% for what it must do) just to make things simpler to implement on my project workbook?

Originally Posted by sintek

Yes, just incorporate the proper little code snippet that enters that 1 new student into the next available row of the table...This would be the correct coding practice and not having to manually insert hundreds of rows all the time...



Okay, here it is...Just remember currently your listbox is not wide enough to accommodate the column widths...You will need to set the scroll bar property to true or increase your listbox width...

Please Login or Register  to view this content.

Fantastic thank you (dankie ) it works great! I just have 2 questions:

1. Is it possible to make the column headers static so they don't move if you have to scroll down the listbox because there are many "students"?
2. Is it possible to adjust the alignment of each individual column e.g. column 1= centre alignment, column 2 = alignment left, column 3 = alignment right for example etc?

Okay, here it is...Just remember currently your listbox is not wide enough to accommodate the column widths...You will need to set the scroll bar property to true or increase your listbox width...

Please Login or Register  to view this content.

[/QUOTE]

Your code works great with

Please Login or Register  to view this content.

but can you please do the same for

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

as these formats also need to be fixed

Originally Posted by sintek

Not sure what your search code is doing...what is SEARCH criteria normally...What would be typed in?

Thanks for the Private Sub cmdCurrent_Click() code...it works great.

The search has no specific criteria it is just for general searching of the Array(1, 2, 4, 5, 6, 9, 15, 20, 18, 19) columns, because as I mentioned before the amount of rows are always increasing so a search function would become necessary to do a quick search when there are lots of "students" so was hoping to get the correct formatting code for this also.

Originally Posted by sintek

So what would User type in...

Mostly by Name, Last Name, ID number, but could also search for student number, class. These would be the main ones to search for. But anything really in columns 1, 2, 4, 5, 6, 9, 15, 20, 18, 19 in the DB

Originally Posted by sintek

Not happy with your current code...
Is the search criteria entered completely...Why are you making use of wildcard search...

As with all my code I am using currently it was provided to me on this forum by other members, so I use the code it if it works and does the job which my current code seems to do.

Currently you don't have to enter in lets say the whole "last name" for instance, then it will display all names with the same info to narrow down your search to select the one you want. So current code working pretty good so far, just the formatting as with previous posts needs to be the same as others.

Originally Posted by sintek

Okay...For your current code provided by OP...
Insert red snippet

Awesome thanks!!

Do you perhaps a a solution to my previous other 2 questions...

Fantastic thank you (dankie ) it works great! I just have 2 questions:

1. Is it possible to make the column headers static so they don't move if you have to scroll down the listbox because there are many "students"?
2. Is it possible to adjust the alignment of each individual column e.g. column 1 = centre alignment, column 2 = alignment left, column 3 = alignment right for example etc?

Originally Posted by sintek

AFAIK One can only freeze top row if ColumnHeads is set to True and Listbox is populated by RowSource Which includes data only...
Alternatively, you can manually insert labels above the listbox...
As for alignment...See here

Thanks I will have a look into that!

I think that is it for this post so I will mark it as solved.

I am going to make another new post just now if you could help there also, would be much appreciated, but thanks for all your help and effort here!

I blinked and the problem was solved by the time I found the link again.
Rather than condemn my effort to the bin it is attached in case the odd snippet proves useful.
torachan.

Originally Posted by torachan

I blinked and the problem was solved by the time I found the link again.
Rather than condemn my effort to the bin it is attached in case the odd snippet proves useful.
torachan.

Thanks for this! and for the effort...really appreciate it!