Combo box help

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Could anyone tell me how to format a combo box to display
2 decimal places. I cannot get it to display 12.30 etc. It
leaves off the zero and displays 12.3. Where do I do this.
Thanks.
 
Assuming a combobox from the control toolbox toolbar

if isnumeric(Combobox1.Value) then
Combobox1.Value = format(cdbl(combobox1.Value),"#.00")
End Sub

You haven't said where the combobox is located or what you are doing/how the
value gets in the combobox, so hard to say what event to use. Personally,
if I was using AddItem, when I added it I would do

combobox1.AddItem Format(vVal,"#.00")

so it is already formatted correctly in the the combobox dropdown.

I suspect you are linking to a worksheet range, however.
 
Hi Tom,
Also, I posted the below request on 4th Sept last and you
kindly replied with the code below. It works fine but
progressively drops off entries from the output.


Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9 Missing 4 combinations
8 6 9
8 6 7 Missing 3
8 5 9
8 5 7
8 5 6 Missing 2
8 4 9
8 4 7
8 4 6
8 4 5 Missing 1
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4 Correct


Also, what change do I need to do to the code to have the
value in M10 appear in the second column only with all the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick
 
You say 8,7,2 is missing, but it isn't 8,2,7 is produced. You must not
understand the mathematical concept of combinations. Combinations don't
assign any special significance to order - so 8,7,2 is the same as 8,2,7 or
2,7,8, etc.

to the best of my knowledge, the code produces the unique combinations. If
you want permutations, then that is different - but you asked for
combinations.
 
Certainly didn't understand the difference between the
two, Tom. Below is what I'm trying to get to. If thats
called permutations then that is what I'm after. Just like
this below. Could you assist.

The reason I need them like this is that they refer to
specific options such as OptionA OptionB OptionC
I need 3 macros to do this as they are unique (dare I say
it, combinations). The next macro would produce as below
except the 8 would be the common number in the middle e.g.
7 8 9 and 7 8 6 and 7 8 5. The last macro would be 7 9 8
and 7 6 8 and 7 5 8 etc.
Hope I haven't confused the issue again.

Obviously the greater the number (up to 10 can be used
with the common number), the larger the permutations. If
you could help I would be grateful.
Thanks.

8 7 9
8 7 6
8 7 5
8 7 4
8 7 2
8 6 9
8 6 7
8 6 5
8 6 4
8 6 2
8 5 9
8 5 7
8 5 6
8 5 4
8 5 2
8 4 9
8 4 7
8 4 6
8 4 5
8 4 2
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4
 
You left out
8 9 7
8 9 6
8 9 5
8 9 4
8 9 2

Anyway, this macro will do all three types. set the variable sType equal to
"A", "B", "C"

"A" m10 value is on the left
"B" M10 value is in the middle
"C" M10 value is on the right

Option Explicit

Sub tester9()
Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long, temp As Long
Dim sType As String
'
' set sType to "A" or "B" or "C"
'
sType = "A"
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
For k = 0 To 9
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = _
Arrtype(arr1, sType)
j = j + 1
temp = arr1(2)
arr1(2) = arr1(3)
arr1(3) = temp
Cells(j, 3).Resize(1, 3).Value = _
Arrtype(arr1, sType)
j = j + 1
End If
End If
Next
Range("C70").CurrentRegion.Sort _
Key1:=Range("C70"), Order1:=xlDescending, _
Key2:=Range("D70"), Order2:=xlDescending, _
Key3:=Range("E70"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:= _
xlTopToBottom
End Sub

Function Arrtype(ar As Variant, _
ltr As String) As Variant
Dim temp As Long
Dim arr As Variant
arr = ar
Select Case UCase(Left(ltr, 1))
Case "A"

Case "B"
temp = arr(1)
arr(1) = arr(2)
arr(2) = temp
Case "C"
temp = arr(3)
arr(3) = arr(1)
arr(1) = temp
End Select
Arrtype = arr
End Function


Sub bldArr(num As Long, arr() As Long, _
cnt As Long)
Dim lNum As Long, i As Long
lNum = num
Dim sStr As String
sStr = ""
cnt = 0
For i = 9 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(9 - i) = 1
sStr = sStr & "1"
Else
arr(9 - i) = 0
sStr = sStr & "0"
End If
Next
End Sub
 
Back
Top