Change Shape Color Based On Combo Box Selection

Sevcav · Aug 6th 2007

Hi all,

I am trying to use a userform (Combo Box) to update my named cell "Aircraft1" then based on the value selected change the color of an object.

The code works great if I manually enter the value in the cell "Aircraft1", however if the dropdown list selects the value the object does not update its color.

Is there a way to update the cell via a combo box, and then have the VB code change the color of the object?

Thanks in advance for any possible solution to my problem.

This is my code:

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Aircraft1")) Is Nothing Then Exit Sub
ActiveSheet.Shapes("Arrow1").Select
With Range("Aircraft1")
If .Value = 1 Then
ActiveSheet.Shapes("Arrow1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 21#
Selection.ShapeRange.Width = 18#
Selection.ShapeRange.Rotation = 0#
ElseIf .Value = 2 Then
ActiveSheet.Shapes("Arrow1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 12
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 21#
Selection.ShapeRange.Width = 18#
Selection.ShapeRange.Rotation = 0#
ElseIf .Value > 2 Then
ActiveSheet.Shapes("Arrow1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 21#
Selection.ShapeRange.Width = 18#
Selection.ShapeRange.Rotation = 180#
End If
.Select
End With
End Sub

Display More

**mikerickson** · Aug 6th 2007

Re: Combo Box To Udate Cell Value Then Perfrom Code Actions

As you have found out, selecting from a validation list does not trigger a Change event. Controling the arrow's color is easiest when done at the same time as filling the cell.

Code

Private Sub ComboBox1_AfterUpdate()
ThisWorkbook.Range("Aircraft1") = Me.ComboBox1.Value
With ActiveSheet.Shapes("Arrow1").ShapeRange.Fill.ForeColor
Select Case Me.ComboBox1.Value
Case 1
.SchemeColor = 17
Case 2
.SchemeColor = 12
Case is >2
.SchemeColor = 10
End Select
End Sub

Display More

Sevcav · Aug 7th 2007

Re: Combo Box To Update Cell Value Then Perform Code Actions

I believe I follow what you are saying. I tried to run the code after re-naming combobox "Drop Down 357" to ComboBox1, but it never updates "Aircraft1" cell, nor changes the object "arrow1" color. Could this be because I have used the Excel Forms Combobox feature vice creating in VBA?

Please pardon my VBA knowledge as I am trying to learn some basics here.

Thanks again for any hlep on my problem.

**mikerickson** · Aug 7th 2007

Re: Combo Box To Update Cell Value Then Perform Code Actions

The original post said that the combo box was on a userform. The code I gave should go in the code module for the Userform.

If the dropdown is from the Forms Menu (not the Toolbox, that's a third kind of combobox.)
Assign the combobox to this macro (after ajusting the names in the macro.)

Code

Sub DropDown3_Change()
Dim ddVal As Variant
With ActiveSheet
ddVal = .Shapes("Drop Down 3").ControlFormat.Value
Range("Aircraft1").Value = ddVal
With .Shapes("Autoshape 4").Fill.ForeColor
Select Case ddVal
Case 1
.SchemeColor = 17
Case 2
.SchemeColor = 12
Case Is > 2
.SchemeColor = 10
End Select
End With
End With
End Sub

Display More

**Reafidy** · Aug 7th 2007

Re: Combo Box To Update Cell Value Then Perform Code Actions

Sevcav,

Please use code tags correctly like this:

[noparse]

Code

your code here

[/noparse]

Thanks,

Sevcav · Aug 7th 2007

Re: Change Shape Color Based On Combo Box Selection

Sorry for the confusion. This last code worked perfectly. Thank you very much for the help!

R/
Ken

elpicantemartinez · Nov 25th 2020

Buenas!, estoy tratando de realizar la misma operacion , tengo experiencia en excel pero no en estos metodos.

me podrías explicar el codigo que te funciono ?

yo quiero hacer algo similar tener en el desplegable 3 palabras,

1-Finalizado

2-En proceso

3- sin intervencion

al 1 aplicarle color verde

al 2 amarillo

al 3 gris.

al elemento .shapes.

pero no entiendo ... si alguien me da una mano genial ! gracias!

**royUK** · Nov 26th 2020

Welcome to the Forum

Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

Change Shape Color Based On Combo Box Selection

royUK Nov 26th 2020

Share