Thread: Multiple Combo Boxes

Hi,

Okay, I did away with the merged cells, and now I have a combo box in every cell that I want it in.

My problem is, I would like to format each combo box seperately. Every cell that has a combo box in it has data vakidation activated. I copy and pasted code to get it to work. It works great, but I would still like to change the combo box size and tab and enter functions for each individual one. I created more than one combo box but I kept getting an error on them. Any help or knowledge would be greatly appreciated.

I am posting just the code that I copy and pasted.

Code:

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("Locations")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 195
      .Height = Target.Height + 4
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub Locations_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 4).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, -1).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================

Thank you in advance.

Hi,
can't help you with the combo boxes, but merged cells are BAD BAD BAD and should be avoided because they lead to all kinds of problems. Instead use the formatting option to "Center across selection" in the Horizontal options of the Alignment tab.

OK. More often than not merged cells are used to achieve the "look" of a cell spreading across several columns. Say you want A1, B1 and C1 to look as if it's one cell with some text in it. One way is to merge the three cells. But that can lead to all sorts of trouble, for example when you later change the layout of the workbook and want to add a new column between A and B, you first have to unmerge the cells.

As an alternative to merging, select cells A1, B1 and C1, click Format - Cells, select the Alignment tab and in the dropdown box for Horizontal choose the option "Center across selection" Now your text in cell A1 is centered across the three columns. You can put a nice border around A1:C1 and they look like one cell. But they still are separate and you can insert columns without having to undo anything first.

HTH

Hi Whatsmyname
can you please edit and add code tags to script in your first post as it makes the code easier to read?
Place ['code] then the script ['/code] at the end

whatsmyname
To

change the combo box size and tab and enter functions for each individual one

In vb editor switch the "Design Mode"
then in the work sheet Right click on Combobox and select View Properties.
This is where custom setting are made. Or is there a pattern to the changes?

How have you named the combo boxes

do you have a sample workbook that you can attach?