combobox listindex yields TRUE or False, not Numbers?

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
How about just ...

Code:
Private Sub adjustBox_Change()
    Range("thetopprod" & Range("A1").Value).Value = adjustBox.listindex
End Sub
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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?
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Still not a fix. Same problem.
The code works time for products selected independently, but not if the header is selected first
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Do you have the MultiSelect property of the listbox set to True?

Is this on a form, or on a worksheet?
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
It's on a userform.
I don't see multiselect there.
The textcolumn is -1
The TopIndex is -1
Could there be something there?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
I can't duplicate the problem you're seeing.

What's in A1?

The catenation of "thetopprod" and A1 gives a valid named range?
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

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
Top