combo box inputs creates new boxes

[silverskye787]

i have a combo box that contain range of number from (1-9)

i need some help in creating the yellow boxes automatically after i have choose the number that i want from the combo box

thanks

 

[zzjasonzz]

All I see is a red x, fix the picture and i'll help you out

 

[silverskye787]

how about this picture? can see it now?

if cant i will try to explain in details...

In B2 is a combo box with number range from 1-9

if i select 2 from the combo box, it will create two yellow box with border on B3:B4

if i select 4 from the combo box, it will create four yellow box with border on B3: B6 and so on...

 

[zzjasonzz]

can't see the picture, work doesn't like imageshack

do you want it to create actual boxs? or just colour cells B3:Bx yellow witha border?

 

[silverskye787]

yes... yellow cell with full border

 

[zzjasonzz]

right click on the sheet tab at the bottom of the sheet with the combobox on it and add this

Private Sub ComboBox1_Change()
    Dim i
    
    If ComboBox1.Value <> "" Then
        Range("B3:B11").Interior.ColorIndex = 0

        Range("B3:B11").Borders(xlEdgeLeft).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeTop).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeBottom).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeRight).LineStyle = xlNone
        Range("B3:B11").Borders(xlInsideVertical).LineStyle = xlNone
        Range("B3:B11").Borders(xlInsideHorizontal).LineStyle = xlNone
        
            Range("B3:B" & ComboBox1.Value + 2).Select
            Selection.Interior.ColorIndex = 6
            
            With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlInsideVertical)
                .Weight = xlMedium
            End With
            Range("B3").Select
    End If
    
End Sub

 

[silverskye787]

it shows an error on this line

If ComboBox1.Value <> "" Then

 

[zzjasonzz]

it shows an error on this line

If ComboBox1.Value <> "" Then

you need to make sure your combobox is called combobox1 or change the name in the line above.

if you click on the combobox it should tell you the name in the top left of the screen

 

[silverskye787]

the name is the same but there's still an error. still because of the same line..

or is it because of the combo-box that has the problem with it. Because i just copy paste the combo-box from some where

 

[jindon]

Delete current combobox and add new one from the ToolBox.

Private Sub ComboBox1_Click()  '<- change 1 to actual number
Columns("b").ClearFormats
With ComboBox1  '<- change her as well
     If .ListIndex > -1 Then
          Range("b3").Resize(.Value).Interior.Color = vbYellow
          Range("b3").Resize(.Value).Borders.Weight = -4138
     End If
End With
End Sub