Multiple combo-boxes - howto? 2

[djhawthorn]

I'm playing around with forms and stuff within Excel, and I've been scouring the web for a decent how-to or tutorial on combo boxes and haven't been able to come up with anything overly useful.

What I want to do is this:
- I need about 130 combo boxes (one per row basically)
- The combo boxes each have the same five values available (lets just say 'a', 'b', 'c', 'd' and 'e')
- When an option is selected, it changes the value in the cell next to it to another value (lets say 'a' selected in the combo changes the cell next to it to 'v', 'b' maps to 'w', 'c' maps to 'x', 'd' to 'y', 'e' to 'z').
- The text values 'v', 'w', 'x', 'y', 'z' change colours accordingly (eg., 'v' appears in red, 'w' appears in blue, 'x' in green etc.)

I'd like to do this with a bare minimum of code - I certainly don't want 130 'Combo#_Change()' subroutines.

Can someone point me in the right direction?

Just as another side question, is there a way to make the combo box align to a specific cell? At the moment it seems to be floating wherever I drop it. I'd like to be able to lock it to a cell, so that if I change the size of the cell, it will change dimensions with it. The "move and size with cells" option seems to be greyed out...

Thanks!


MCSE NT4/W2K

 

[SkipVought]

Hi,

You might be able to do this with NO CODE.

Take a look at Data/Validation -- List.

The take a look at Format/Conditional Formatting.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[djhawthorn]

Thanks heaps; thats basically what I was looking for.

MCSE NT4/W2K

 

[djhawthorn]

Actually, I've come up against a hurdle - the Conditional formatting only allows upto three formatting types.

I've done a search on VBA code to replace this, and come up with the following so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim intColour As Integer
  Dim strColour As String
  Dim TargetNext As Excel.Range

  If Not Intersect(Target, Range("D2:D15")) Is Nothing Then
    Select Case LCase(Target)
      Case "a"
        intColour = 9
        strColour = "v"
      Case "b"
        intColour = 51
        strColour = "w"
      Case "c"
        intColour = 16
        strColour = "x"
      Case "d"
        intColour = 1
        strColour = "y"
      Case "e"
        intColour = 11
        strColour = "z"
      Case Else
        'Whatever
        intColour = 12
        strColour = "Unknown"
      End Select
        
    Target.Font.ColorIndex = intColour
    
    'TargetNext.Font.ColorIndex = intColour
    'TargetNext.Text = strColour
  End If
End Sub

How do I get 'TargetNext' to be assigned the cell directly to the right (the next column) of the cell being edited?

MCSE NT4/W2K

 

[PHV]

Have a look at Offset:
Target.Offset(0, 1).Value = strColour

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886