Can I control entry into certain tabs?
 

I am trying to control the way my users use my worksheet. I have several tabs
in my worksheet. I am trying to block the user from using the next tab unless
they enter data into certain cells. These cells cannot by empty to move onto
the next tab. I don't know if this is possible as i cannot find this anywhere
on the website.

Thank you for your help.
Orla

Can I control entry into certain tabs?
 

Hi,

First we need to get some terminology correct. Your workbook has worksheets
and worksheets have sheet tabs that contain the name and allow some other
limited functionality.Data are entered into worksheets not tabs so your
question seems to be can you ensure the correct fields are filled in on a
worksheet before the user moves onto the next worksheet.

You can do that and it requires macros and there you hit your first obstacle
in that how do you make users enable macros and I suggest you search these
forums for the answer to that but on the assumption you overcome that here's
a way to do what you want.

First make the required worksheet active:-
Alt + f11 to open VB editor. Double click 'This workbook' and paste this in.

Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Sheet1").Select
End Sub

Then double click 'Sheet1' and paste this in

Private Sub Worksheet_Deactivate()
myvalue = 1
If Range("A1").Value < myvalue Then
MsgBox "You must fill in A1 before leaving this sheet"
Worksheets("Sheet1").Select
End If
End Sub

This requires the user to put a 1 in a1 or they are redirected back to
sheet1. You will have to set up yourself what values and where you want
populated.

Mike

"OrlaLynch" wrote:

I am trying to control the way my users use my worksheet. I have several tabs
in my worksheet. I am trying to block the user from using the next tab unless
they enter data into certain cells. These cells cannot by empty to move onto
the next tab. I don't know if this is possible as i cannot find this anywhere
on the website.

Thank you for your help.
Orla

Can I control entry into certain tabs?
 

You can do this with data validation, but it is a bit of work... Here is the
theory.
Lets assume that you have 3 cells on sheet 1 that need to be filled in
before you can start work on sheet2. Create a formula on sheet 1 that counts
the number of entries in those cells. Assume cells A1:A3 need to be
completed. In cell A4 add the formula =counta(A1:A3). When all 3 cells are
completed the value will be 3. Cell A4 needs to be a named range so highlight
cell A4 and in the Name Box (directly above Cell A1) change A4 to something
like Tada and hit <Enter.

Now on sheet 2 select cell A1 and Data - Valication - Custom Add the
formula =Tada=3

Now you can only type a value in sheet 2 cell A1 when the 3 cells on sheet 1
are filled in.
--
HTH...

Jim Thomlinson


"OrlaLynch" wrote:

I am trying to control the way my users use my worksheet. I have several tabs
in my worksheet. I am trying to block the user from using the next tab unless
they enter data into certain cells. These cells cannot by empty to move onto
the next tab. I don't know if this is possible as i cannot find this anywhere
on the website.

Thank you for your help.
Orla

Can I control entry into certain tabs?
 

That works pretty well, Thank you for the idea

"Jim Thomlinson" wrote:

You can do this with data validation, but it is a bit of work... Here is the
theory.
Lets assume that you have 3 cells on sheet 1 that need to be filled in
before you can start work on sheet2. Create a formula on sheet 1 that counts
the number of entries in those cells. Assume cells A1:A3 need to be
completed. In cell A4 add the formula =counta(A1:A3). When all 3 cells are
completed the value will be 3. Cell A4 needs to be a named range so highlight
cell A4 and in the Name Box (directly above Cell A1) change A4 to something
like Tada and hit <Enter.

Now on sheet 2 select cell A1 and Data - Valication - Custom Add the
formula =Tada=3

Now you can only type a value in sheet 2 cell A1 when the 3 cells on sheet 1
are filled in.
--
HTH...

Jim Thomlinson


"OrlaLynch" wrote:

I am trying to control the way my users use my worksheet. I have several tabs
in my worksheet. I am trying to block the user from using the next tab unless
they enter data into certain cells. These cells cannot by empty to move onto
the next tab. I don't know if this is possible as i cannot find this anywhere
on the website.

Thank you for your help.
Orla

Can I control entry into certain tabs?
 

I tried another way but I may need to try this at a later stage to make it
more complex. I will keep it in mind, Thank you for the help

"Mike H" wrote:

Hi,

First we need to get some terminology correct. Your workbook has worksheets
and worksheets have sheet tabs that contain the name and allow some other
limited functionality.Data are entered into worksheets not tabs so your
question seems to be can you ensure the correct fields are filled in on a
worksheet before the user moves onto the next worksheet.

You can do that and it requires macros and there you hit your first obstacle
in that how do you make users enable macros and I suggest you search these
forums for the answer to that but on the assumption you overcome that here's
a way to do what you want.

First make the required worksheet active:-
Alt + f11 to open VB editor. Double click 'This workbook' and paste this in.

Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Sheet1").Select
End Sub

Then double click 'Sheet1' and paste this in

Private Sub Worksheet_Deactivate()
myvalue = 1
If Range("A1").Value < myvalue Then
MsgBox "You must fill in A1 before leaving this sheet"
Worksheets("Sheet1").Select
End If
End Sub

This requires the user to put a 1 in a1 or they are redirected back to
sheet1. You will have to set up yourself what values and where you want
populated.

Mike

"OrlaLynch" wrote:

I am trying to control the way my users use my worksheet. I have several tabs
in my worksheet. I am trying to block the user from using the next tab unless
they enter data into certain cells. These cells cannot by empty to move onto
the next tab. I don't know if this is possible as i cannot find this anywhere
on the website.

Thank you for your help.
Orla