I want to have certain cells in a form I am building to be a mandatory
fill in. I know _very_ little about VBA, I copied this from my Excel
book.
How / where do I put in the information as to what cell/cells I want
included?
A few months ago I made it work, now I don't know how I did it.


Private Sub Worksheet_Slecetionchange(ByVal Target As Range)
Dim myCell As Range
Dim myRange As Range

On Error GoTo NoRange
Set myRange = Range("MustFill")
For Each myCell In Range("MustFill")
If myCell.Value = "" Then
Application.EnableEvents = Flase
myCell.Select
Application.EnableEvents = True
Exit Sub
End If
Next myCell
NoRange:
Application.EnableEvents = True
End Sub


--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837

First, you'll want to watch your typing. You've got a couple of mistakes that
would make your procedure not even start.

I've found it much easier to let excel type the worksheet event procedure name
by choosing the event I want using the dropdowns at the top of the code window.

You'll want to select your range that must be completed on the worksheet. Then
use Insert|name to give it that nice "MustFill" name.

This kind of code goes in the worksheet module that should have the behavior.
So rightclick on the worksheet tab and select view code. Paste this into the
code window that just opened.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
Dim myRange As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = Me.Range("MustFill")
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Please contact CBrausa at #### to fix the MustFill Range"
Exit Sub
End If

For Each myCell In myRange.Cells
If myCell.Value = "" Then
Application.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit For
End If
Next myCell

End Sub


Then back to that worksheet and test it out.

CBrausa wrote:

I want to have certain cells in a form I am building to be a mandatory
fill in. I know _very_ little about VBA, I copied this from my Excel
book.
How / where do I put in the information as to what cell/cells I want
included?
A few months ago I made it work, now I don't know how I did it.

Private Sub Worksheet_Slecetionchange(ByVal Target As Range)
Dim myCell As Range
Dim myRange As Range

On Error GoTo NoRange
Set myRange = Range("MustFill")
For Each myCell In Range("MustFill")
If myCell.Value = "" Then
Application.EnableEvents = Flase
myCell.Select
Application.EnableEvents = True
Exit Sub
End If
Next myCell
NoRange:
Application.EnableEvents = True
End Sub

--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837

--

Dave Peterson

Thanks, it works great.


--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837

We have been working on the real form, it has 44 cells that are a
mandatory fill. If Brian closes and reopens the worksheet, it doesn't
work. Then we can only get 12 cells to work. What are we doing
wrong?


--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837

Did you define the "mustfill" range to be all 44 cells on that worksheet?

Does Brian allow macros to be run when he reopens the workbook?

CBrausa wrote:

We have been working on the real form, it has 44 cells that are a
mandatory fill. If Brian closes and reopens the worksheet, it doesn't
work. Then we can only get 12 cells to work. What are we doing
wrong?

--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837

--

Dave Peterson

In this form there are 44 different cell, (some have been merged) . I
am clicking on each cell and each time it adds the name of the sheet
and the cell, I tried deleting the name and just entering the cell
numbers and when I click ADD it will only accept 26 of the 44 and
deletes the rest. What do I do now?


--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=539837