Forms

[DOF2001]

Hi,

I trying to build a form that will enable me to lock fields in hte form based on what the user choose on the first field, id there an easy way to do it. now i am doing 5 diferent forms to accomodate this but i just relized that 90% of the fileds are common in all forms so i wonder if what i want is possible

thanks inadvance for allyour assistance.

Dof2001

 

[ColinKJ]

Hi DOF2001,

I don't think it's possible using formula, but it would be possible with VBA, are you open to a VBA solution.

 

[DOF2001]

Hi ColinKJ,

I new before hand that this will involbe VB, i completly open to any methods that will enable me to accomplish the task

Thanks

 

[ColinKJ]

Hi DOF2001,

If I send you a PM with my email add, are you able to send me a copy of your form, along with which cells are involved, and in which order they are to be completed.

 

[mikerickson]

Data Validation with formulae like =AND(($A$1<>"Invoice"),($A$1<>"Pack Slip")) might help you "lock" cells depending on the entry in A1. (The Ignore Blanks option becomes important.)

 

[ColinKJ]

Hi DOF2001,

First, mikerickson's solution may well work for you, and you may want to try it first.

I've put togeter the following code which, based on the selection to question 1, one of questions 2,3,4,5 or 6 are made visible as well as the other questions.

First, put this code in the Open Even to ensure questions 2,3,4,5 and 6 are all hidden until question 1 is selected.

Private Sub Workbook_Open()
Sheets(1).Unprotect
Rows("5:9").Select
    Selection.EntireRow.Hidden = True
 Sheets(1).Protect
End Sub

Then put the following code in the Sheet Change Event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 4 Then
Sheets(1).Unprotect
Rows("5:9").EntireRow.Hidden = True
Select Case Target
Case ""
Rows("5:9").EntireRow.Hidden = True
Case "Test1"
Rows("5").EntireRow.Hidden = False
Case "Test2"
Rows("6").EntireRow.Hidden = False
Case "Test3"
Rows("7").EntireRow.Hidden = False
Case "Test4"
Rows("8").EntireRow.Hidden = False
Case "Test5"
Rows("9").EntireRow.Hidden = False
End Select
Sheets(1).Protect
End If
End Sub

The questions 1 to 13 are in cells B4 to B16, answers in C1 to C16.

I've put together an example WB called "Input Control" which you can download from:

http://www.box.net/shared/r5pa11ikza

 

[DOF2001]

Thank you both for you prompt response , i will give a try to both


Thanks again!