Prevent data entry except from DropDown List?

[jimbokentuck]

In excel 2000 I have 2 cells that are requiring user entry for which I've provided drop down lists. These can trigger macros for a userform, which populates additional data based on the cell ref. Is there a way to force the user to enter data using the drop down list / block the user from copying data down or from manual entering?

[Bob Phillips]

AS long as they enter or copy a value in the list, why bother?

[jimbokentuck]

Well, i didn't think it would matter either but, in the worksheet if a user were to enter 100 in "E11" and 500 in "F11" it triggers a macro that opens a userform that asks a yes or no question. ONce the question is answered another macro hides the userform. For which the code is listed below. So if they were to copy the two values all the way down the columns it screws up the purpose of the userform.

Dim ctl As Control
ActiveCell.EntireRow.Range("ae1").Value = Me.GSTEXPENSE.Value
Exit Sub

[Bob Phillips]

Maybe you could try something like this that only allows them to select one cell



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Target.Cells(1, 1).Select
End Sub

[jimbokentuck]

Sorry to sound dumb but is this how it should look? I'm not seeing any difference


Private Sub GSTEXPENSE_Change()
End Sub
Private Sub Label2_Click()
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Target.Cells(1, 1).Select
End Sub
Private Sub Selection1_Click()
Dim RowCount As Long
If Me.GSTEXPENSE.Value = "" Then
MsgBox "Please select an entry from the list.", vbExclamation, "GSTEXPENSE"
Me.GSTEXPENSE.SetFocus
Exit Sub
End If

Dim ctl As Control
ActiveCell.EntireRow.Range("ae1").Value = Me.GSTEXPENSE.Value
Exit Sub
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then ctl.Value = ""
Exit Sub

Next
End
End Sub
Private Sub UserForm_Click()
End Sub

[Bob Phillips]

No, put that code in the offending worksheet code module.

[jimbokentuck]

That works great! One more quick question. Is there a way to keep the user on the same line until a condition is met? The workbook that I"m creating is really simple. But if the users entering the data on line 40 and types it in as opposed to selecting it from the drop down. And he hits enter or arrow down he ends up on Line 41 and then when the userform populates it thinks the user is answering for line 41 instead of 40. I'd like to keep them in check a little more.

thanks!

[Bob Phillips]

Can you show us your code that fires the form, so that we can see its context?

[jimbokentuck]

Here's the code that's driving the Macros. I've been able to keep the user from copying and gotten the enter key to move to the right. But I'm concerned if the user hits the down arrow and ends up in the next row, it will screw up the userform entry.

Option Explicit
Private Sub Worksheet_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Target.Cells(1, 1).Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("af63").Value = 1 Then
cboGSTINPUT.Show
Exit Sub
End If
If Range("af63").Value = 0 Then
cboGSTINPUT.Hide
Exit Sub
End If
End Sub

[mikerickson]

I had a quick notion that didn't work.