Problem with ComboBox in percentage

[CouchKing]

Hi all!
<o> </o>
I’m new to this forum and also VBA so be gentle! <o></o>
<o> </o>
I have an excel sheet with a button linking to a form that gives input to a calculation done in the sheet.<o></o>
<o> </o>
One of the inputs in this form is ComboBox1. The source for this combobox is row AF1:AF13. This is formatted as a percentage i.e. 0 %, 5 %, 10 % and so on. In addition I’ve added the following code to the ComboBox:<o></o>
<o> </o>
Private Sub ComboBox1_Change()<o></o>
ComboBox1 = Format(ComboBox1, "##0 %")<o></o>
Range("C17") = ComboBox1.Value<o></o>
End Sub<o></o>
<o> </o>
This is my attempt at getting the correct formatting of the numbers in the form and in the excel sheet. However, I’m not getting the correct values in the form.<o></o>
<o> </o>
In the form when I select the value it’s displayed correctly, but not after selection. For example on selecting 10 % from the dropdown it’s displayed in the form as 0.10.<o></o>
<o> </o>
Other values again turn to a completely wrong number. For example selecting 20 % turns to 3655700 % in the form and the excel sheet.<o></o>
<o> </o>
Any tips from you guys would be appreciated!<o></o>

 

[pbornemeier]

Welcome to the board!

Set up a block of cells (AG1:AG13) adjacent to your numeric value cells (AF1:AF13), format them as text and put the appropriate text values in the cells adjacent to the numeric values.

Set the following properties of ComboBox1:
- Bound Column = 1 'Column that contains the value of the textbox
- TextColumn = 2 'Column that is displayed in the text box
- RowSource = Sheet1!AF1:AG13 'Be sure to define which sheet the data is in

This would be your combobox change code:

Private Sub ComboBox1_Change()
    Sheet1.Range("C17") = ComboBox1.Value
    Sheet1.Range("C17").NumberFormat = "##0 %" 'This should only need to be applied once and can be done manually rather than every time the combobox is updated.
End Sub