I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?

This would have to be done in the Worksheet events code - I'd use the
_Deactivate() event. Here's sample code of how it might work (change cell
address - also make sure that cell is not Locked before Protecting the sheet,
so that people may enter a value into it)..

To place this code into proper location in the workbook: go to the sheet
this is to happen to and right-click the sheet's name tab and choose [View
Code] from the list. Copy and paste this code into the module that appears,
then modify the code as needed. Save the workbook, or close and 'save
changes'.

Private Sub Worksheet_Deactivate()
If IsEmpty(Range("A1")) Then
'tell why they can't leave!
MsgBox "You must enter a value into cell A1."
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range("A1").Select
End If
End Sub



"Andy" wrote:

I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?

This has been helpful. However I have a a range of three cells that need
information before you can move on. I have tried to type in a range and
either this is not possible, i am typing the range formula incorrectly, or i
need to set up a code for each cell. Can you tell me what to do?
"JLatham" wrote:

This would have to be done in the Worksheet events code - I'd use the
_Deactivate() event. Here's sample code of how it might work (change cell
address - also make sure that cell is not Locked before Protecting the sheet,
so that people may enter a value into it)..

To place this code into proper location in the workbook: go to the sheet
this is to happen to and right-click the sheet's name tab and choose [View
Code] from the list. Copy and paste this code into the module that appears,
then modify the code as needed. Save the workbook, or close and 'save
changes'.

Private Sub Worksheet_Deactivate()
If IsEmpty(Range("A1")) Then
'tell why they can't leave!
MsgBox "You must enter a value into cell A1."
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range("A1").Select
End If
End Sub



"Andy" wrote:

I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?

I'm not sure why your attempt to set things up is failing - but here's code
that could catch 3 cells, giving a unique response for each possible problem.
I should have used Me.Range... in the earlier example to make sure you were
looking at the proper cell on the correct sheet. That might be the cause of
your problem. It's a matter of timing. Hope this helps.

Private Sub Worksheet_Deactivate()
Dim emptyCellAddress As String

If IsEmpty(Me.Range("A1")) Then
emptyCellAddress = "A1"
ElseIf IsEmpty(Me.Range("B1")) Then
emptyCellAddress = "B1"
ElseIf IsEmpty(Me.Range("C1")) Then
emptyCellAddress = "C1"
Else
'all 3 cells seem to have something in them
Exit Sub ' just quit
End If
'tell why they can't leave!
MsgBox "You must enter a value into cell: " & _
emptyCellAddress
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range(emptyCellAddress).Select

End Sub


"Andy" wrote:

This has been helpful. However I have a a range of three cells that need
information before you can move on. I have tried to type in a range and
either this is not possible, i am typing the range formula incorrectly, or i
need to set up a code for each cell. Can you tell me what to do?
"JLatham" wrote:

This would have to be done in the Worksheet events code - I'd use the
_Deactivate() event. Here's sample code of how it might work (change cell
address - also make sure that cell is not Locked before Protecting the sheet,
so that people may enter a value into it)..

To place this code into proper location in the workbook: go to the sheet
this is to happen to and right-click the sheet's name tab and choose [View
Code] from the list. Copy and paste this code into the module that appears,
then modify the code as needed. Save the workbook, or close and 'save
changes'.

Private Sub Worksheet_Deactivate()
If IsEmpty(Range("A1")) Then
'tell why they can't leave!
MsgBox "You must enter a value into cell A1."
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range("A1").Select
End If
End Sub



"Andy" wrote:

I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?

Thanks again for the help. however prior to receiving this i found a way to
accompolish what i wanted. Instead of having the cells go in order, i.e. C1,
C2, etc...I reversed the order, C3, C2, C1 and it worked.

"JLatham" wrote:

I'm not sure why your attempt to set things up is failing - but here's code
that could catch 3 cells, giving a unique response for each possible problem.
I should have used Me.Range... in the earlier example to make sure you were
looking at the proper cell on the correct sheet. That might be the cause of
your problem. It's a matter of timing. Hope this helps.

Private Sub Worksheet_Deactivate()
Dim emptyCellAddress As String

If IsEmpty(Me.Range("A1")) Then
emptyCellAddress = "A1"
ElseIf IsEmpty(Me.Range("B1")) Then
emptyCellAddress = "B1"
ElseIf IsEmpty(Me.Range("C1")) Then
emptyCellAddress = "C1"
Else
'all 3 cells seem to have something in them
Exit Sub ' just quit
End If
'tell why they can't leave!
MsgBox "You must enter a value into cell: " & _
emptyCellAddress
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range(emptyCellAddress).Select

End Sub


"Andy" wrote:

This has been helpful. However I have a a range of three cells that need
information before you can move on. I have tried to type in a range and
either this is not possible, i am typing the range formula incorrectly, or i
need to set up a code for each cell. Can you tell me what to do?
"JLatham" wrote:

This would have to be done in the Worksheet events code - I'd use the
_Deactivate() event. Here's sample code of how it might work (change cell
address - also make sure that cell is not Locked before Protecting the sheet,
so that people may enter a value into it)..

To place this code into proper location in the workbook: go to the sheet
this is to happen to and right-click the sheet's name tab and choose [View
Code] from the list. Copy and paste this code into the module that appears,
then modify the code as needed. Save the workbook, or close and 'save
changes'.

Private Sub Worksheet_Deactivate()
If IsEmpty(Range("A1")) Then
'tell why they can't leave!
MsgBox "You must enter a value into cell A1."
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range("A1").Select
End If
End Sub



"Andy" wrote:

I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?

Just glad to hear you found a solution that worked for you. Thanks for
posting back.

"Andy" wrote:

Thanks again for the help. however prior to receiving this i found a way to
accompolish what i wanted. Instead of having the cells go in order, i.e. C1,
C2, etc...I reversed the order, C3, C2, C1 and it worked.

"JLatham" wrote:

I'm not sure why your attempt to set things up is failing - but here's code
that could catch 3 cells, giving a unique response for each possible problem.
I should have used Me.Range... in the earlier example to make sure you were
looking at the proper cell on the correct sheet. That might be the cause of
your problem. It's a matter of timing. Hope this helps.

Private Sub Worksheet_Deactivate()
Dim emptyCellAddress As String

If IsEmpty(Me.Range("A1")) Then
emptyCellAddress = "A1"
ElseIf IsEmpty(Me.Range("B1")) Then
emptyCellAddress = "B1"
ElseIf IsEmpty(Me.Range("C1")) Then
emptyCellAddress = "C1"
Else
'all 3 cells seem to have something in them
Exit Sub ' just quit
End If
'tell why they can't leave!
MsgBox "You must enter a value into cell: " & _
emptyCellAddress
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range(emptyCellAddress).Select

End Sub


"Andy" wrote:

This has been helpful. However I have a a range of three cells that need
information before you can move on. I have tried to type in a range and
either this is not possible, i am typing the range formula incorrectly, or i
need to set up a code for each cell. Can you tell me what to do?
"JLatham" wrote:

This would have to be done in the Worksheet events code - I'd use the
_Deactivate() event. Here's sample code of how it might work (change cell
address - also make sure that cell is not Locked before Protecting the sheet,
so that people may enter a value into it)..

To place this code into proper location in the workbook: go to the sheet
this is to happen to and right-click the sheet's name tab and choose [View
Code] from the list. Copy and paste this code into the module that appears,
then modify the code as needed. Save the workbook, or close and 'save
changes'.

Private Sub Worksheet_Deactivate()
If IsEmpty(Range("A1")) Then
'tell why they can't leave!
MsgBox "You must enter a value into cell A1."
'force back to this sheet
Worksheets(Me.Name).Select
'point out cell needing entry in it
Range("A1").Select
End If
End Sub



"Andy" wrote:

I want to set up a worksheet so you have to enter a value in a cell before
you can move to the next worksheet. This worksheet is also has protected
cells. How and/or can this be done?