combo box inputs creates new boxes

silverskye787

Board Regular
Joined
Jun 18, 2007
Messages
109
ccclo7.jpg


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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

silverskye787

Board Regular
Joined
Jun 18, 2007
Messages
109
cccyd9.jpg


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

Well-known Member
Joined
Apr 23, 2006
Messages
649
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?
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
right click on the sheet tab at the bottom of the sheet with the combobox on it and add this

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

Board Regular
Joined
Jun 18, 2007
Messages
109
it shows an error on this line

Code:
If ComboBox1.Value <> "" Then
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
it shows an error on this line

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

Board Regular
Joined
Jun 18, 2007
Messages
109
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Delete current combobox and add new one from the ToolBox.
Code:
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
 

Forum statistics

Threads
1,181,407
Messages
5,929,766
Members
436,687
Latest member
Glass of Gin

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
Top