Green Error in Cell Corner when selecting from a Combobox

[mikeb7500]

Please HELP...I am using a combobox to make a NUMBER selection and linking it to cell z8. When I do, I get the green error mark in the upper left corner, because it's in TEXT FORMAT. I can click on the green error and select, "Convert to Number." But when I make another selection in the combobox, I get the same green error. How can I PREVENT this from happening when I make another NUMBER selection? I must change this selection several times. (I am then taking that selected z8 cell number and using it in a formula, which doesn't work because of the error.) And as always THANKS!

 

[mrshl9898]

https://www.mrexcel.com/forum/excel-questions/70141-selection-combobox-text-not-number.html


combobox data to number... Range("MyValueRange") = val(combobox.value)

 

[mikeb7500]

Thanks for such a QUICK RESPONSE. Went to your link and read this:

1) make sure the cell your putting your data in is formatted as number... or currency depending on whatever you need.
Range("MyValueRange") = val(combobox.value)
2) Values can be converted between text and values as required
For example...
combobox data to number...
cell value changed to a string... Range("MyTextRange") = str("MyValueRange")

I've done #1 , but totally lost on #2 . Please, it would be greatly appreciated if you can walk me through what to do, and where to go. I have a dozen of these cells and it would help me out immensely. Is this done in the Properties box? Totally lost...thanks Scott!

 

[mrshl9898]

#2 is just showing how to get a value in a cell as a string or a value. Can you show me the code where you are inserting the value into the cell?

 

[mrshl9898]

Had a bit more of a look into this and I think we were talking about different things.

If you are using the linked cell in the combobox properties you might want to use something like this instead.

Sub insert()

Range("A1").Value = Sheet1.ComboBox1.Value


End Sub

Here's a post about assigning a macro to the combo box that will run when you make changes.

https://www.mrexcel.com/forum/excel-questions/422010-run-macro-when-combo-box-changes.html

 

[mikeb7500]

I tried copying and pasting, changing the A1 to Z8, then changed the Sheet1 to the sheet name, but I still get the error. I also tried it on a new sheet in a new workbook, where I linked the combobox to cell "A1" and the sheet was sheet1. Still get the error message. I feel I'm so close, and I really do appreciate your help. By the way, my Clearcells works great...thanks to help from the members at Mr. Excel.

Sub Clearcells()


Range("X4").ClearContents
Range("X6").ClearContents
Range("X8").ClearContents
Range("Y8").ClearContents
Range("Z8").ClearContents
Range("X10").ClearContents
Range("X12").ClearContents
Range("X14").ClearContents
Range("X16").ClearContents
Range("Y16").ClearContents
Range("X18").ClearContents
Range("X20").ClearContents
Range("N20:O20").ClearContents
Range("X22").ClearContents
Range("W26:AM26").ClearContents
Range("D28:I28").ClearContents


End Sub


Sub insert()


Range("z8").Value = DISCOVERY.ComboBox5.Value

 




End Sub

I thought I would try to send a screenshot like it was an attachment of the worksheet with the combobox properties, the results are as follows. I guess you can't.

 

[mrshl9898]

Guessing the sheet name is discovery?

Range("z8").Value = Sheets("DISCOVERY").ComboBox5.Value

Sheet1 is the number of the sheet you see in the VBA editor next to the sheet name, Sheets("Sheet1") is referencing a sheets name

 

[mikeb7500]

Yes, you are correct, the sheet name is Discovery. I copied and pasted from your latest. Unfortunately, I'm still getting the same error. Could I possibly send you the file? It's a small enough file to send by email.

Range("z8").Value = Sheets("DISCOVERY").ComboBox5.Value

Sub Clearcells()

Range("X4").ClearContents
Range("X6").ClearContents
Range("X8").ClearContents
Range("Y8").ClearContents
Range("Z8").ClearContents
Range("X10").ClearContents
Range("X12").ClearContents
Range("X14").ClearContents
Range("X16").ClearContents
Range("Y16").ClearContents
Range("X18").ClearContents
Range("X20").ClearContents
Range("N20:O20").ClearContents
Range("X22").ClearContents
Range("W26:AM26").ClearContents
Range("D28:I28").ClearContents


End Sub


Sub insert()


Range("z8").Value = Sheets("DISCOVERY").ComboBox5.Value




End Sub

 

[RoryA]

Try using:

Range("z8").Value = CDbl(Sheets("DISCOVERY").ComboBox5.Value)