Cannot get value from ListBox in UserForm

Hello,

Below are my codes I typed in Excel Macro.

Please Login or Register  to view this content.

I created 4 ListBoxs in a UserForm, then I added years into ListBox1 and ListBox3, and months into ListBox2 and ListBox 4. After that, I set the defalut values to be today year and month repectively, and there is a button called "Calinterest" to get the values from the ListBoxs. But it seems the program cannot get the ListBox4's value if I just click the button without clicking a value in the ListBox4, even if the value in ListBox 4 is highlighted, it always returns nothing. The program works fine if I click all the ListBoxs. Is there something wrong when I set the default value for the ListBoxs?

Thanks,
Howard

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Please Login or Register  to view this content.

When you declare variables like above, only em is type Integer. All the other variables are the default variable type, Variant.

Please Login or Register  to view this content.

This line is trying to compare em to a nullstring "".
Because em is an Integer variable, it cannot be compared to a string.
The other variable comparisons are okay because they are Variants.



Declare each variable as type String like this and it should work.

Please Login or Register  to view this content.

If later in the code you want to use the variable as a number, wrap the variable in the Val() function e.g.; Val(em)

Originally Posted by howard1324

First, we can declare multiple variables by specifying the variable name and separate them by commas. So I don't think "only em is type Integer".

Hi,

Yes you can declare them like that, but unless you specify the type for each one, all but the last one will be Variants. AlphaFrog is entirely correct.

For your listbox issue, I think you are running into a bug here. Perhaps use

Please Login or Register  to view this content.

Originally Posted by howard1324

First, we can declare multiple variables by specifying the variable name and separate them by commas. So I don't think "only em is type Integer".

Look at the Locals window as you step trough the code and you will see the variables listed and their type. If you declare them your way, only em will be type Integer.

Second, if there is no value got from the ListBox4, em would return "". I know there was no value returned by ListBox4 because I ran the program code line by line and add "watch" to the variables.

Agreed, if there is no value returned by Listbox4 the it returns a nullstring "". ListBoxes return strings. That's why I suggested you declare each variable as a string.

Lastly, I have tried to declare each variable as type String as you suggested. But I still cannot get the value for em from ListBox4.

The main problem here is I cannot get the default value. It returns nothing for ListBox4.

Please Login or Register  to view this content.

If I remove these four lines, the program works fine. But I want to know why it cannot get the ListBox4 value when I assigned one for it.

If you did change the declaration for each variable to string as I suggested, I don't know why it doesn't work. I do know that if you declare em as Integer, then If em = "" will throw a type mismatch error.

Perhaps double-check that your ListBox4 is actually named ListBox4 and not somethineg else.