Trying to reuse code to access multiple Textboxes

[dentler]

So I have created a userform called Assets which contain multiple text and combo boxes. Presently I have only been playing with Textbox31 which on the attached example is the first textbox beneath Amount. I can use the following code in each textbox for data entry, and that works great.

Please Login or Register  to view this content.

But I would like to reduce the code and have the code within the Textbox on the userform call a routine, that will allow it to change the name for each textbox. So I have created the following sub routine:

Please Login or Register  to view this content.

In the above code you will see the first If statement commented out. Since when I try to run it I get an error: Compile error: Invalid Qualifier.

I have tried a number of different solutions and suggestion that I have found searching online, but so far none of them work.

I would also like to make it when the user is entering numbers into the textbox that the user would not have to enter the decimal point and that each time they enter a number it would then shift it to the left. For example if the enter 90 it would show as .90, but if then enter 390, then it would show as 3.90. Presently they must enter the decimal point manually. If they do not then it does not display any decimal point, so if they entered 390 and no decimal point and hit enter, it would only show 390 in the textbox.

I hope I have made this as clear as possible.

See the attached workbook as an example.

Thank you for any suggestions.

[6StringJazzer]

I haven't had time to try to solve your whole problem but here is why you are getting a compile error:

Please Login or Register  to view this content.

newTextBoxName is a String. A String is a scalar type, not an object, and so does not have a .Value attribute.

Also, IsNumeric on newTextBoxName in your example will always be False. I have no idea what you have in mind with that so it's hard to tell you what is correct.

[dentler]

I have tried some code where I attempted to declare an object and had problems as well.

[Akuini]

Why are you using so many textbox & combobox?
How about just using 3 textbox + 2 combobox + 1 listbox + 1 button
So it works like this:
1. Fill some data in the 3 textbox + 2 combobox > hit the button > data will be transfered to listbox (1 row at a time)
2. Repeat the step with different data as needed.
3. Now you have all data in the listbox, and then you can manipulate it or send it to a sheet.

[dentler]

This sounds like a great solution, but I am not sure how to proceed in a userform? Could you provide some example?

Thank you

[Akuini]

Could you attach a new workbook with some data to test?
And explain what you're trying to do in more detail.

[Artik]

dentler, It is true that the project will develop in a different direction, but I want to show how the current code should be written.
You want to refer to the controls on the Assets form by their names. So:

Please Login or Register  to view this content.

Artik

[jindon]

For example if the enter 90 it would show as .90, but if then enter 390, then it would show as 3.90. Presently they must enter the decimal point manually. If they do not then it does not display any decimal point, so if they entered 390 and no decimal point and hit enter, it would only show 390 in the textbox.

1) Asset form code module

Please Login or Register  to view this content.

2) Insert Class module (Class1) and the code

Please Login or Register  to view this content.

[dentler]

Let me first say thank you to everyone for your suggestions. I will attach a new WB that has incorporated those suggestions into it. The new workbook contains both the old Assets form where I have incorporated data suggested by jindon . Thanks for giving me the code that I was looking for when entering amounts.

The WB also contains a new form called DataEntry that incorporates suggestion made by Akuini. Thanks for asking the question of why so many text and combo boxes. I added data for lists used in the comboboxes and code to save those entries to a worksheet. I believe this is what you were suggesting. If you think I can make any improvements to this, please let me know.

Thanks again to everyone. This forum is quite wonderful and all help present and past has been greatly appreciated. I will wait a bit to mark it as solved, in case there are any other suggestions.

Please check out the attached file with the above mentions changes.

[Akuini]

Ok, here's how I would do it:
I removed Userform Assets & added a listbox in Userform DataEntry

How it works:
1. Fill some data in the 3 textbox + 2 combobox > hit ENTER button > data will be transfered to listbox (1 row at a time)
2. Repeat the step with different data as needed.
3. Now you have all data in the listbox, hit Save Data to Asset button > data from listbox will be transfered to sheet DataStore
4. In case you entered wrong data in listbox you can delete it by double-clicking the wrong item in the listbox.

The code:

Please Login or Register  to view this content.

[dentler]

I will check this out.. Thanks again.

[jindon]

If just one control for each entry then try the attached.
Remove Class1
From code module

Please Login or Register  to view this content.

[dentler]

Thanks again, I really appreciate your expertise.

[dentler]

Now I see what you were mentioning about the list box... great ! I like it.. Thank You.

[Akuini]

Now I see what you were mentioning about the list box... great ! I like it.. Thank You.

Are replying to me or jindon?
In any case, I'm glad it works.

[dentler]

So a couple of quick questions. I was able to incorporate your suggestions /code into my WB with a couple of minor changes. Initially it failed with mismatch errors on the three lines in the initialize routine that contains the filters. I simply commented those out and it seems to work find without any issues that I have been able to see or determine.

So my questions are, what version of office do you use to develop your code in. I use office 2019, and from what I have read, the filters are available in office 365 but not 2019, although they are available in the just released version of Office 2021. The sample WB you provided works great if I run in in Office 2019, but once I transpose the form and code into my WB that is when I get mismatch errs relating to the code containing filters.

If you are running Office 2019 or a prior version, have you added some library or something else for filters to work? Like I said after commenting those lines out it still seems to work as intended, but just wanted to know what version you develop in?

[jindon]

Filter function I used is a native vba function available from xl2003(not sure), not the worksheet function, so it is not the version problem.
It errors when the named range has only one cell or contains error value.

I used Filter function because your named range has empty cell(s).
Change Initialize code to

Please Login or Register  to view this content.