Combo boxes, data validation

[dmcnab]

Good morning, all....until recently, I used data validation lists to enter information....the font is very small etc etc....however, it is very easy to delete data.....lately, I have learned about combo boxes and data validation (Contextures.com & Deb Dalgliesh)...these give me much more control over how to present the data in the list etc etc...can someone tell me where I can study up on the behaviour of the combo boxes --- for example, what setting do I adjust in Properties so that the list doesn't automatically appear as soon as I select a cell? And, so far, the only way to delete data in a cell seems to be to backspace it out of the cell...surely, there is a setting that can be adjusted that allows the simple use of the delete key etc etc....any help with this is much appreciated, so that I can get the most out of using these combo boxes...thanks.

[Rudi]

Could I request that you upload a sample file with some sample info and a combo box or two and then state what you would like to achieve in this sample file. It is so much easier to assist if there is some thing to work with as well as the help is more relevant to your needs to.

TX.

[dmcnab]

Hi Rudi...I am attaching a partial sample due to size limits..I have figured out how to stop the drop-down menus from appearing automatically...the deletion of data is still an issue...thanks.

[Rudi]

Hi,

There is a VBA password on the module?

[dmcnab]

Sorry about that...thought that I had removed it...I attach another copy of same file with p/word removed.

[Rudi]

Hi,

Add these two lines into your code

Code: Select all

    cboTemp.Object.SelStart = 0
    cboTemp.Object.SelLength = cboTemp.Object.TextLength

In the position as indicated below:

Code: Select all

  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 = False
      .Left = target.Left
      .Top = target.Top
      .Width = target.Width + 15
      .Height = target.Height + 5
      .ListFillRange = str
      .LinkedCell = target.Address
    End With
    cboTemp.Activate
    cboTemp.Object.SelStart = 0
    cboTemp.Object.SelLength = cboTemp.Object.TextLength
  End If

[dmcnab]

Good evening, Rudi and others....I am following up on an earlier post I started in mid-June....I need some help tweaking the settings in a combo box....this is the same combo box VBA and data validation I refer to in earlier posts. Rudi -- thank you for your help with my previous questions....I am attaching another sample workbook...my question is this:

...when you open the sample book, you will see that columns I, L, M, T and U are the ones where data validation occurs. At the moment, I have the VBA code set so that the drop-down menus (boxes) will not appear automatically. If I hit ENTER or TAB, the cursor advances 1 column to the right. How can I tweak the combo box settings [Developer-Design Mode-Properties] so that if I hit ENTER, the cursor moves to the right; if I hit TAB, the cursor moves to the right; if I hit a left arrow key, the cursor will move 1 column to the left; if I hot a right arrow key, the cursor will move 1 column to the right ? At the moment, I can move left or right using the arrow keys, but if the cursor lands in a 'data validation' cell (column I, L, M, T, U), it stops and I must hot ESC twice in order to 'free up' the cursor and continue to move left (or right) using arrow keys.....thank you in advance for your help.

[Rudi]

Hi,

Functioning workbook attached...
Please test it out.

I modified the workbook as such:

In the ThisWorkbook_Open Event Handler I placed this:
It registers the key movements at the opening of the file...

Code: Select all

Private Sub Workbook_Open()
    Application.OnKey "{RIGHT}", "MoveRight"
    Application.OnKey "{LEFT}", "MoveLeft"
    Application.OnKey "~", "MoveRight"
    Application.OnKey "{TAB}", "MoveRight"
End Sub

In a standard module I lace this code:
The macros to run when the key is pressed based on what was set in the Workbook_Open Event...

Code: Select all

Sub MoveRight()
    If TypeOf Selection Is Range Then
    SendKeys "{Esc}"
    ActiveCell.Offset(0, 1).Select
    End If
End Sub
Sub MoveLeft()
    If TypeOf Selection Is Range Then
    SendKeys "{Esc}"
    ActiveCell.Offset(0, -1).Select
    End If
End Sub

[Rudi]

Oh, very important...

Please add this to the ThisWorkbook Event Module:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{RIGHT}"
    Application.OnKey "{LEFT}"
    Application.OnKey "~"
    Application.OnKey "{TAB}"
End Sub

If you don't reset the OnKey events, they will continue to be used in other workbooks...

[HansV]

An alternative, and I think easier method: you can expand the TempCombo_KeyDown event procedure that you already have:

Code: Select all

Private Sub TempCombo_KeyDown( _
        ByVal KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyReturn, vbKeyTab, vbKeyRight
            ActiveCell.Offset(0, 1).Activate
        Case vbKeyLeft
            ActiveCell.Offset(0, -1).Activate
        Case Else
            'do nothing
    End Select
End Sub

[Rudi]

Ah...that is clearer without the integers in the Case Statements as before.
Nice update

[HansV]

Thank you! The vbKey... constants make the code self-documenting.

[dmcnab]

Good morning, Hans, Rudi and others......the code you provided is working very nicely and I am starting to figure out how it all works....I have made a couple of small changes (eg: the TAB key now loves cursor to the left; the L and R arrow keys no longer move the cursor and now they permit in-cell editing.....even though I asked about enabling the L and R arrows to move the cursor, it wasn't until I started working with your code that I realized that doing so meant no more in-cell editing, so I changed the code)......I am copying the current code into this post --- I now see that even though I can edit in-cell, I cannot use ALT-ENTER to word-wrap.....can you adjust this code to permit that? Secondly, I can only edit in-cell...I cannot edit in the function bar, and I would like to be able to do both...can you adjust the code to permit that? If not, it is not a problem b/c at least I can edit in-cell...but the lack of word-wrapping is a bigger problem......thank you, as always....see current code below:

Code: Select all

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("TempCombo")
  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 + 15
      .Height = target.Height + 5
      .ListFillRange = str
      .LinkedCell = target.Address
    End With
    cboTemp.Activate
    cboTemp.Object.SelStart = 0
    cboTemp.Object.SelLength = cboTemp.Object.TextLength
    'open the drop down list automatically
   
  End If

ExitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
ErrHandler:
  Resume ExitHandler

End Sub
Private Sub TempCombo_KeyDown( _
            ByVal KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        Select Case KeyCode
            Case vbKeyReturn
                ActiveCell.Offset(0, 1).Activate
            Case vbKeyTab
                ActiveCell.Offset(0, -1).Activate
            Case Else
                'do nothing
        End Select
    End Sub

[HansV]

In cells with data validation of the List type, the code displays an ActiveX combo box on top of the cell. You can select an item from the dropdown list or edit the value in the text box part of the combo box. You cannot use the combo box to enter a multi-line value, however - combo boxes simply don't support that.
Also, as long as the combo box is active, the formula bar is disabled.

So the behavior that you experience is an unavoidable side effect of using a combo box instead of the built-in validation dropdown. You cannot have both the combo box and Excel's native behavior at the same time.

It's up to you to weigh the advantages and disadvantages of the combo box approach.

[dmcnab]

Ok...thank you....I will give that some thought.... :):) ..

[dmcnab]

Good afternoon, Hans.....I have looked at the choices here and think that I will stick with use of the combo box....I do have another question about something that I noticed while doing this: without using combo boxes, I can use the auto-correct feature that is part of Excel, Word etc etc.....if I use the combo box, I can't use the auto-correct feature (as far as I know, based on looking at the Properties of the combo box)...I am including the code for the combo box in this post -- is there something that you can change in this code that will permit me to also use auto-correct?...thanks...see code:

Code: Select all

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("TempCombo")
  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 + 15
      .Height = target.Height + 5
      .ListFillRange = str
      .LinkedCell = target.Address
    End With
    cboTemp.Activate
    cboTemp.Object.SelStart = 0
    cboTemp.Object.SelLength = cboTemp.Object.TextLength
     
  End If

ExitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
ErrHandler:
  Resume ExitHandler

End Sub

[HansV]

No, just like combo boxes don't support multiple lines, they don't support AutoCorrect. They do support auto-complete though - I assume that's the main reason you use them here.

[dmcnab]

OK...thank you for that quick answer...problem understood, and solution (ie: make a choice) is on the way.. :):)