Mandatory Cells

[Mel1221]

Hey all

I'm sure this question has been asked a few times before ;o).
Basically I'm looking for a validation rule for the below.
I have a row of data, say A1,B1,C1,D1 where a1 can have either value or used
or Inuse. When the user selects 'Unused' from the dropdown, they will
be forced to enter in details in to the other cells. I.e Name, Department,
Cost Center
ect...

I've tried an IF statement but not being technical this has me stumped.
Any help provided would be a godsend.

Much thanks!
Mel

[excelent]

try explain better, i dont get it :-)


"Mel1221" skrev:

> Hey all
>
> I'm sure this question has been asked a few times before ;o).
> Basically I'm looking for a validation rule for the below.
> I have a row of data, say A1,B1,C1,D1 where a1 can have either value or used
> or Inuse. When the user selects 'Unused' from the dropdown, they will
> be forced to enter in details in to the other cells. I.e Name, Department,
> Cost Center
> ect...
>
> I've tried an IF statement but not being technical this has me stumped.
> Any help provided would be a godsend.
>
> Much thanks!
> Mel
>

[Mel1221 via OfficeKB.com]

sorry bout that...my brain is fried...here goes.

When entering a value into a cell, A1 for example, I would like to force the
user to enter data into B1,C1 aswell.

In this example the user would enter 'Yes' in A1, and then be forced to enter
in their first name in B1 and last name in C1 before exiting out of the
spreadsheet.

Hope this is clearer!

excelent wrote:
>try explain better, i dont get it :-)
>
>"Mel1221" skrev:
>
>> Hey all
>>
>[quoted text clipped - 11 lines]
>> Much thanks!
>> Mel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

[Gord Dibben]

Mel

To "force" a user to enter data in cells requires VBA code.

Either in the Before_Close or Before_Save subs or in a worksheet event code.

Of course, this then requires that the users enable macros in order for the code
to work.

Which leads to more code that shows only a message sheet and hides the others if
the users disable macros.

The message would read similar to .."This workbook is non-usable unless you
re-open it with macros enabled".

The question now becomes.............How much effort and learning are you
willing to invest in this project?

Post back if you really need this. We can try to work you through it.


Gord Dibben MS Excel MVP



On Mon, 03 Jul 2006 09:04:36 GMT, "Mel1221 via OfficeKB.com" <u23678@uwe> wrote:

>sorry bout that...my brain is fried...here goes.
>
>When entering a value into a cell, A1 for example, I would like to force the
>user to enter data into B1,C1 aswell.
>
>In this example the user would enter 'Yes' in A1, and then be forced to enter
>in their first name in B1 and last name in C1 before exiting out of the
>spreadsheet.
>
>Hope this is clearer!
>
>excelent wrote:
>>try explain better, i dont get it :-)
>>
>>"Mel1221" skrev:
>>
>>> Hey all
>>>
>>[quoted text clipped - 11 lines]
>>> Much thanks!
>>> Mel

[Mel1221 via OfficeKB.com]

Thanks for the response, I need it enough that I'm willing to put in the
effort if you are :o)

Where should I start? Instead of having the code in the Before_Save sub
could it be triggered
when the user changes a particular value?

i.e
When user selects the value "used" from a dropdown in say A1
Then A2 cannot be blank, and A3 cannot be blank....?

Although I'm not too sure how to code this into the worksheet?

Again, hope this makes sense!

Cheers,
Mel

Gord Dibben wrote:
>Mel
>
>To "force" a user to enter data in cells requires VBA code.
>
>Either in the Before_Close or Before_Save subs or in a worksheet event code.
>
>Of course, this then requires that the users enable macros in order for the code
>to work.
>
>Which leads to more code that shows only a message sheet and hides the others if
>the users disable macros.
>
>The message would read similar to .."This workbook is non-usable unless you
>re-open it with macros enabled".
>
>The question now becomes.............How much effort and learning are you
>willing to invest in this project?
>
>Post back if you really need this. We can try to work you through it.
>
>Gord Dibben MS Excel MVP
>
>>sorry bout that...my brain is fried...here goes.
>>
>[quoted text clipped - 16 lines]
>>>> Much thanks!
>>>> Mel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

[Gord Dibben]

Mel

I would place code in the Before_Save event in Thisworkbook Module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Sheets("Sheet1").Range("A2,A3")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

If you want users to get a reminder before they try to save, stick this in the
Sheet Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "used" Then
MsgBox "Please fill in cells A2 and A3"
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 05 Jul 2006 10:38:49 GMT, "Mel1221 via OfficeKB.com" <u23678@uwe> wrote:

>Thanks for the response, I need it enough that I'm willing to put in the
>effort if you are :o)
>
>Where should I start? Instead of having the code in the Before_Save sub
>could it be triggered
>when the user changes a particular value?
>
>i.e
>When user selects the value "used" from a dropdown in say A1
>Then A2 cannot be blank, and A3 cannot be blank....?
>
>Although I'm not too sure how to code this into the worksheet?
>
>Again, hope this makes sense!
>
>Cheers,
>Mel
>
>Gord Dibben wrote:
>>Mel
>>
>>To "force" a user to enter data in cells requires VBA code.
>>
>>Either in the Before_Close or Before_Save subs or in a worksheet event code.
>>
>>Of course, this then requires that the users enable macros in order for the code
>>to work.
>>
>>Which leads to more code that shows only a message sheet and hides the others if
>>the users disable macros.
>>
>>The message would read similar to .."This workbook is non-usable unless you
>>re-open it with macros enabled".
>>
>>The question now becomes.............How much effort and learning are you
>>willing to invest in this project?
>>
>>Post back if you really need this. We can try to work you through it.
>>
>>Gord Dibben MS Excel MVP
>>
>>>sorry bout that...my brain is fried...here goes.
>>>
>>[quoted text clipped - 16 lines]
>>>>> Much thanks!
>>>>> Mel