Compulsory Cells based on Condition
 

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

Compulsory Cells based on Condition
 

Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the

=if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values")

And you may want to look at using Data|Form to let the users enter the data.

axissm wrote:

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

--

Dave Peterson

Compulsory Cells based on Condition
 

Hi Dave,

Thanks for your email. I have tried it but it doesnt not work. Can you
please elaborate on this a little more.

All I want to do is to check the value in cell B. If it is greater the 00:00
then I want when the user saves the form if cell D is empty a message to pop
up and notify the user to enter some data.

For various reasons I cannot user forms.

Many thanks.

Anguel


"Dave Peterson" wrote:

Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the

=if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values")

And you may want to look at using Data|Form to let the users enter the data.

axissm wrote:

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

--

Dave Peterson

Compulsory Cells based on Condition
 

My suggestion was not to use a macro--it just uses an adjacent cell and gives an
immediate warning that only disappears when all the cells in the range that are
counted (with =counta()) have something in them.

I don't understand what you mean by "it doesn't work", though.

What formula did you use?
And what cell got that formula?

adi wrote:

Hi Dave,

Thanks for your email. I have tried it but it doesnt not work. Can you
please elaborate on this a little more.

All I want to do is to check the value in cell B. If it is greater the 00:00
then I want when the user saves the form if cell D is empty a message to pop
up and notify the user to enter some data.

For various reasons I cannot user forms.

Many thanks.

Anguel

"Dave Peterson" wrote:

Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the

=if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values")

And you may want to look at using Data|Form to let the users enter the data.

axissm wrote:

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

--

Dave Peterson


--

Dave Peterson

Compulsory Cells based on Condition
 

ps. Data|Form isn't a userform.

It's built into excel--just like Data|Text to columns or Edit|Replace or
Format|Cells.

Dave Peterson wrote:

My suggestion was not to use a macro--it just uses an adjacent cell and gives an
immediate warning that only disappears when all the cells in the range that are
counted (with =counta()) have something in them.

I don't understand what you mean by "it doesn't work", though.

What formula did you use?
And what cell got that formula?

adi wrote:

Hi Dave,

Thanks for your email. I have tried it but it doesnt not work. Can you
please elaborate on this a little more.

All I want to do is to check the value in cell B. If it is greater the 00:00
then I want when the user saves the form if cell D is empty a message to pop
up and notify the user to enter some data.

For various reasons I cannot user forms.

Many thanks.

Anguel

"Dave Peterson" wrote:

Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the

=if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values")

And you may want to look at using Data|Form to let the users enter the data.

axissm wrote:

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Compulsory Cells based on Condition
 

How about something like this in the Thisworkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
With Sheets("Sheet1")
If Range("B1") 0 And Range("D1") = "" Then
Cancel = True
MsgBox "Please complete entering data in D1"
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Thu, 7 Dec 2006 14:57:00 -0800, adi wrote:

Hi Dave,

Thanks for your email. I have tried it but it doesn’t not work. Can you
please elaborate on this a little more.

All I want to do is to check the value in cell B. If it is greater the 00:00
then I want when the user saves the form if cell D is empty a message to pop
up and notify the user to enter some data.

For various reasons I cannot user forms.

Many thanks.

Anguel


"Dave Peterson" wrote:

Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the

=if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values")

And you may want to look at using Data|Form to let the users enter the data.

axissm wrote:

Hi,

I have an Excel spread sheet with 10 columns as follow:
Date
Start Time (default value is 00:00)
End Time (default value is 00:00)
Account
Type
Department
Time (the time it has taken to complete the task)
Additional Information
Total ours for the week.

Based on these columns I have various other functions and formulas pulling
different reports and data. However I need help with the following.

If a user enters a Start time none of the other fields in the same row
should be left blank (i.e. Date, End Time, Account, Type and Department). I
am not sure how to prompt the user to do that and when to prompt them.
However these prompts should be relevant only to the row where the Start Time
is different than 00:00.
There should not be any prompts for the rows where the Start Time is 00:00.
Also it would be good if the cursor can position itself on the next empty
value the prompt is relating to.

Many thanks in advance.

Andy

--

Dave Peterson