Disable Text Entry

[tweek312]

Heres The Deal:

I have a spreadsheet that has validated cells. I would like to limit the type of entry to using the validation dropdown only. In other words... Disable typing in the cell and force the user to select via the dropdown.

This seems possible but I do not know where to start.

Thanks,
tW33k

 

[tweek312]

Any Ideas Men... And Women?

 

[jcrater]

Not sure what version of Excel you're using, but try
Data->Validation (use a list of allowed values)

 

[xlbo]

why ??
if the entry is validated, then it matches a list entry and is ok. If it is not, they cannot enter the data anyway....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[combo]

Looks like you need an interface between data in spreadsheet and user, the validation is not enough for you. In such case you usually hide or protect the area with final data and either use a userform or a worksheet, with macro that validates data and transfers to proper cell(s). The combobox or listbox control can be useful for data selection.
You could also consider change of application, access can give more control when working with data.

combo

 

[tweek312]

I would love to use access but my company does not want to shell out money for an access license. Kinda weird considering that it only costs about 50 bucks when you buy Office Pro.

Your suggestion about locking the cells and using a Userform for them might be A viable option. I might also consider making a mock up access database and show the bosses... Maybe it will intrigue them. He he heeee... =D

As for better description of what I need...

Im trying to find a workaround for a userform which is activated via worksheet_change. When the userform initiates is will give a couple of options and put corresponding data into a cell based on the cellchange that activated the worksheet_change. If a user were to exit the field by pressing the down arrow; then the data will go into the wrong cell. The same problem would occur if the user were to select another cell via the mouse to exit the field. As I have stated above emploring a UserForm to cover the whole area might be an easier and more beneficial route to take.

Thanks,
tW33k

 

[combo]

An example of simple userform interface in excel:
- works with first worksheet in a workbook,
- uses userform containing textbox

Thisworkbook module:

Private Sub Workbook_Open()
With ThisWorkbook.Worksheets(1)
    .Activate
    .Protect UserInterfaceOnly:=True
    .EnableSelection = xlNoSelection
End With
UserForm1.Show
End Sub

Userform module

Private ActiveCOld As Range, ActiveCNew As Range
Dim RowChange As Long, ColChange As Long
Dim RowMin As Long, RowMax As Long
Dim ColMin As Long, ColMax As Long

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
RowChange = 0: ColChange = 0
Select Case KeyCode
Case vbKeyUp
    If ActiveCell.Row > RowMin Then RowChange = -1
Case vbKeyRight
    If ActiveCell.Column < ColMax Then ColChange = 1
Case vbKeyDown
    If ActiveCell.Row < RowMax Then RowChange = 1
Case vbKeyLeft
    If ActiveCell.Column > ColMin Then ColChange = -1
End Select
If RowChange <> 0 Or ColChange <> 0 Then
    ActiveCell.Offset(RowChange, ColChange).Activate
    Me.Caption = ActiveCell.Address
    Set ActiveCOld = ActiveCNew
    Set ActiveCNew = ActiveCell
    ' validate / enter data here, handle formula errors
    ActiveCOld.Formula = TextBox1.Text
    TextBox1.Text = ActiveCNew.Formula
End If
End Sub

Private Sub UserForm_Initialize()
RowMin = 1: RowMax = 100
ColMin = 1: ColMax = 20
Me.Caption = ActiveCell.Address
Set ActiveCNew = ActiveCell
TextBox1.Text = ActiveCNew.Formula
End Sub

combo

 

[tweek312]

It looks as if this code modifies the way the vbArrows (cursors) work in a UserForm. I need to attack the problem before the UserForm even opens.

Thankz,
tW33k