Combobox Question

[Lotus123]

I have pieced together this code from several sources:

Private Sub ComboBox1_change()

Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("B2:B2")
Set rng = Range("F7:G7")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 1
rng.Interior.ColorIndex = 3
Case 2
rng.Interior.ColorIndex = 46
Case 3
rng.Interior.ColorIndex = 6
Case 4
rng.Interior.ColorIndex = 10
Case 5
rng.Interior.ColorIndex = 5
Case 6
rng.Interior.ColorIndex = 13
End Select
End If
End Sub

If I use Worksheet_change instead of ComboBox change, the spreadshee
updates color as expected when I manually change the value of cell B2.
However, I am trying to get the above code to work when I use a form
combobox to change the value of cell B2.

If you want to look at the actual spreadsheet I have uploaded it to:
http://home.swbell.net/nate-sus/excel/colors.xls

Thanks

 

[Jim Rech]

Try replacing Target with Selection.

--
Jim
message |
| I have pieced together this code from several sources:
|
| Private Sub ComboBox1_change()
|
| Dim WatchRange As Range
| Dim CellVal As Integer
| If Target.Cells.Count > 1 Then Exit Sub
| If Target = "" Or Not IsNumeric(Target) Then Exit Sub
| CellVal = Target
| Set WatchRange = Range("B2:B2")
| Set rng = Range("F7:G7")
|
| If Not Intersect(Target, WatchRange) Is Nothing Then
| Select Case CellVal
| Case 1
| rng.Interior.ColorIndex = 3
| Case 2
| rng.Interior.ColorIndex = 46
| Case 3
| rng.Interior.ColorIndex = 6
| Case 4
| rng.Interior.ColorIndex = 10
| Case 5
| rng.Interior.ColorIndex = 5
| Case 6
| rng.Interior.ColorIndex = 13
| End Select
| End If
| End Sub
|
| If I use Worksheet_change instead of ComboBox change, the spreadsheet
| updates color as expected when I manually change the value of cell B2.
| However, I am trying to get the above code to work when I use a forms
| combobox to change the value of cell B2.
|
| If you want to look at the actual spreadsheet I have uploaded it to:
| http://home.swbell.net/nate-sus/excel/colors.xls
|
| Thanks!
|
|
| --
| Lotus123
| ------------------------------------------------------------------------
| Lotus123's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28611
| View this thread: http://www.excelforum.com/showthread.php?threadid=568052
|