combobox listindex yields TRUE or False, not Numbers?

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
571
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
I am sure this is an easy thing to fix.

My combobox change event is intended to then do an offset based on the number that is the result.
However, sometimes it is yielding TRUE or FALSE, in particular with list item 0

How can I ensure it is only a number and NOT True or False?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Here is the code I am using right now as a bandaid

Private Sub adjustBox_Change()


myadj = adjustBox.listindex
Range("thetopprod" & Range("A1")).Select
ActiveCell.Value = myadj
If ActiveCell.Value = False Then ActiveCell.Value = 0
If ActiveCell.Value = True Then adjustBox_Change
End Sub

Right now if the selection is the first item, then the myadj = 0 but it puts FALSE in the cell
Once false is in the cell, if I make a change, it gives TRUE instead of the number.
I really just want the number and having the above code could put me into an endless loop.

Can anyone suggest how to ensure that the list index is entered in a cell as a number, not as TRUE or FALSE?
 
Upvote 0
How about just ...

Code:
Private Sub adjustBox_Change()
    Range("thetopprod" & Range("A1").Value).Value = adjustBox.listindex
End Sub
 
Upvote 0
I had tried that originally, but the problem still occurs.
I added the extra (sloppier) steps to try and solve the problem.

the problem occurs whenever I Select the top item on the list. I get a FALSE instead of 0.
In the longer code with the variable I list above, if I hover over the line after it is read by the macro, it says myadj = 0 but then it gives me FALSE in the cell.
Once that happens, my next choice, no matter which row I choose it gives me TRUE.
I added the redundant if false then 0 to try and force it to go back to numbers, but it still says TRUE
When I choose another list item after that, I get a number for everything except the first item in the list
 
Upvote 0
Hmm. Not obvious to me how that can happen, since ListIndex returns a Long.

Code:
Private Sub adjustBox_Change()
    With Range("thetopprod" & Range("A1").Value)
        .NumberFormat = "0"
        .Value = adjustBox.ListIndex
    End With
End Sub

EDIT: Do you have the MultiSelect property of the listbox set to True?
 
Upvote 0
Still not a fix. Same problem.
The code works time for products selected independently, but not if the header is selected first
 
Upvote 0
Do you have the MultiSelect property of the listbox set to True?

Is this on a form, or on a worksheet?
 
Upvote 0
It's on a userform.
I don't see multiselect there.
The textcolumn is -1
The TopIndex is -1
Could there be something there?
 
Upvote 0
I can't duplicate the problem you're seeing.

What's in A1?

The catenation of "thetopprod" and A1 gives a valid named range?
 
Upvote 0
It is a three year document.
A1 holds a 1 if year 1, a 2 if year 2, a 3 if year 3
No formulas, just the number
I have named every range accordingly

I have a Product Year1 tab, a Priduct Year2 tab etc
On those tabs I have named ranges such as Sales1 and Sales2 respectively
 
Upvote 0

Forum statistics

Threads
1,222,241
Messages
6,164,787
Members
451,917
Latest member
WEB78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top