Green Error in Cell Corner when selecting from a Combobox

mikeb7500

Board Regular
Joined
Jul 30, 2014
Messages
98
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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!
 
Upvote 0
#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?
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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.


Code:
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.

 
Last edited by a moderator:
Upvote 0
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
 
Last edited:
Upvote 0
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
Code:
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
 
Last edited by a moderator:
Upvote 0
Try using:

Code:
Range("z8").Value = CDbl(Sheets("DISCOVERY").ComboBox5.Value)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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