vba excel - formatting listbox values

[terriertrip]

This is driving me absolutely nuts. The code I use works perfectly for filling a userform listbox, even with other excel files open (even the macro file I'm trying to export into is open). I export the form and import it into my main project and now when I run it, I get an error 91: object variable or with block variable not set. All the variables are defined in a global list (just like my original form), it's the same identical code (just like my original form), and there is no other code running. I've isolated the problem to the formatting line. I'm at my wits end over something so stupid as a formatting issue? Is there an alternative code to what I'm trying to do? I can't understand why it would work in one document and not the other.

Please Login or Register  to view this content.

[MarvinP]

Hi,

Try this:

Please Login or Register  to view this content.

As shown in:
https://www.techonthenet.com/excel/f...mat_number.php

[terriertrip]

I tried that. Still doesn't work. Works on the original form though.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[terriertrip]

Hi Norie. Thanks for taking a look at this. This is the actual file, not the original. I've noticed that another problem has arisen on form initialization, that being an invalid property array index. If you can solve the formatting bug, you will see what I'm talking about. The form will load a first time upon a workbook open. If I unload it and try and reopen it again, it gives me the 381 error. Something is lingering after closing the form.

[terriertrip]

One thing I notice about the invalid property array index error is if I unload the form (via X or the cmd), and try to initialize the form again, the error occurs. If I click the reset button after the error occurs, the form initializes. What's up with that? Does not unloading the form unload it from memory, or is there some sort of loop that I've inadvertently created that runs even when the form is unloaded?

EDIT: Since this is a new problem unrelated, I've posted another topic pertaining to this particular problem.
https://www.excelforum.com/excel-pro...ml#post4764366

[xlnitwit]

Hi,

The lack of capitalisation of Val is a clue- you have defined a Range variable called val which is interfering. Rename it.

Also, you really need to get out of the habit of public variables for everything- a variable should have the narrowest scope possible and should only be made Public when there is no other practical option.

[terriertrip]

Thank you for your guidance xlnitwit. I have many forms in this project, so my working assumption was that it was a shortcut since I was using the same variables in much of the code - but by all accounts all it does is create more potential issues. I see that now. I will educate myself a little more on the use of Public vs. Private declaration.

[terriertrip]

Thank you all for your help. Taking xlnitwit's suggest has fixed the issues. I'll chalk it up as another hard lesson.