Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data validation
I am using Data Validation to restrict entry into a cell to the items in a
list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for any help with these, Gavin |
#2
|
|||
|
|||
Firstly, no
Secondly: Right-click the sheet tab, select View Code, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tf As Boolean On Error Resume Next tf = Target.Resize(1, 1).Validation.InCellDropdown If Err.Number < 0 Then Exit Sub If tf Then SendKeys "%{down}" End If End Sub "gavin" wrote in message ... I am using Data Validation to restrict entry into a cell to the items in a list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for any help with these, Gavin |
#3
|
|||
|
|||
Shame about the first one :-)
But the second one is brilliant!!! Thanks so much for your help, Bob - it's much appreciated. Regards, Gavin "Bob Umlas" wrote in message ... Firstly, no Secondly: Right-click the sheet tab, select View Code, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tf As Boolean On Error Resume Next tf = Target.Resize(1, 1).Validation.InCellDropdown If Err.Number < 0 Then Exit Sub If tf Then SendKeys "%{down}" End If End Sub "gavin" wrote in message ... I am using Data Validation to restrict entry into a cell to the items in a list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for any help with these, Gavin |
#4
|
|||
|
|||
Although this works perfectly I wondered if the code to be tweaked to
account for the occasions when a user opens the workbook with a cell with Data Validation already having the focus - in which case the drop down doesn't work. In other words this code only seems to work when a cell gains the focus. Regards, Gavin "Bob Umlas" wrote in message ... Firstly, no Secondly: Right-click the sheet tab, select View Code, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tf As Boolean On Error Resume Next tf = Target.Resize(1, 1).Validation.InCellDropdown If Err.Number < 0 Then Exit Sub If tf Then SendKeys "%{down}" End If End Sub "gavin" wrote in message ... I am using Data Validation to restrict entry into a cell to the items in a list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for any help with these, Gavin |
#5
|
|||
|
|||
Maybe you could select an out of the way cell on that sheet when you open the
workbook: Option Explicit Private Sub Workbook_Open() Dim CurWks As Worksheet Set CurWks = ActiveSheet With Application .EnableEvents = False .ScreenUpdating = False .Goto Worksheets("sheet1").Cells _ .SpecialCells(xlCellTypeLastCell).Offset(1, 1) CurWks.Select .ScreenUpdating = True .EnableEvents = True End With End Sub Now the user has to select the cell to start it off. And Bob's event will fire. gavin wrote: Although this works perfectly I wondered if the code to be tweaked to account for the occasions when a user opens the workbook with a cell with Data Validation already having the focus - in which case the drop down doesn't work. In other words this code only seems to work when a cell gains the focus. Regards, Gavin "Bob Umlas" wrote in message ... Firstly, no Secondly: Right-click the sheet tab, select View Code, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tf As Boolean On Error Resume Next tf = Target.Resize(1, 1).Validation.InCellDropdown If Err.Number < 0 Then Exit Sub If tf Then SendKeys "%{down}" End If End Sub "gavin" wrote in message ... I am using Data Validation to restrict entry into a cell to the items in a list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for any help with these, Gavin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
Data Validation | Excel Worksheet Functions | |||
Data Validation Formula Help | Excel Worksheet Functions |