Combo boxes, data validation
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Combo boxes, data validation
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
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.
TX.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
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.
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
Hi,
There is a VBA password on the module?
There is a VBA password on the module?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
Sorry about that...thought that I had removed it...I attach another copy of same file with p/word removed.
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
Hi,
Add these two lines into your code
In the position as indicated below:
Add these two lines into your code
Code: Select all
cboTemp.Object.SelStart = 0
cboTemp.Object.SelLength = cboTemp.Object.TextLength
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
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.
...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.
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
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...
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...
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
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
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
Oh, very important...
Please add this to the ThisWorkbook Event Module:
If you don't reset the OnKey events, they will continue to be used in other workbooks...
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo boxes, data validation
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Combo boxes, data validation
Ah...that is clearer without the integers in the Case Statements as before.
Nice update
Nice update
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo boxes, data validation
Thank you! The vbKey... constants make the code self-documenting.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
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
Last edited by HansV on 11 Jul 2014, 13:57, edited 1 time in total.
Reason: to add [code] and [/code] tags around the code
Reason: to add [code] and [/code] tags around the code
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo boxes, data validation
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.
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
Ok...thank you....I will give that some thought.... :):) ..
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
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
Last edited by HansV on 23 Jul 2014, 17:54, edited 1 time in total.
Reason: to add [code] ... [/code] tags
Reason: to add [code] ... [/code] tags
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo boxes, data validation
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Combo boxes, data validation
OK...thank you for that quick answer...problem understood, and solution (ie: make a choice) is on the way.. :):)