Cell to be mandatory fill in

[CBrausa]

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

 

[Dave Peterson]

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]

Thanks, it works great.

 

[CBrausa]

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

 

[Dave Peterson]

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]

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?

 

[Dave Peterson]

Merged cells can cause trouble. I do my best to avoid them.

This version may work with your merged cells.

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.Address = myCell.MergeArea.Cells(1).Address Then
If myCell.Value = "" Then
Application.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit For
End If
End If
Next myCell

End Sub

 

[CBrausa]

Dave-
If I attach the worksheet could you see what the matter is?

The cells involved are:
T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18,M18,L19,M20,N21,U21,N23,B24,K29,B30,L31,B33,I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49,T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57

In the Define Name box there isn't enough space in the Refers to: cell
I changed the sheet name to A and it stops at P47.

What to do

 

[Peo Sjoblom]

You need the cells to be absolute like in $T$4

and you need an equal sign first, there shouldn't be any problems naming
those cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Dave Peterson]

This is a text only newsgroup. Attachments to posts are frowned upon.

Attachments in excelforum are fine, but lots of people don't connect through
excelforum, so they'll never see the workbook--or they'll never bother to visit
excelforum to find that workbook.

An alternative way to setting a range is to use a macro:

Option Explicit
Sub testme()
With worksheets("sheet999") '<---
.Range("T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18," _
& "M18,L19,M20,N21,U21,N23,B24,K29,B30,L31,B33," _
& "I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49," _
& "T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57").Name _
= "'" & .Name & "'!mustfill"
End With
End Sub

Change the worksheet name to match.

 

[CBrausa]

I have followed the steps in my Excel book on "Using the Macro
Recorder", I get to step 10. then it doesn't make sense.
10. Double click the VBA module that contains your recorded code (at
this point I haven't recorded because I don't know where to or How to).
....Note that this is a Sub procedure in a worksheet formula. If you
examine the code, however, you'll see a reference to the UserName
property. You can use this information when creating a Function
procedure. For example, the following Function procedure uses the
UserName property. This function, when used in a worksheet formula,
returns the name of the user. ...

 

[CBrausa]

I have followed the steps in my Excel book on "Using the Macro
Recorder", I get to step 10. then it doesn't make sense.
10. Double click the VBA module that contains your recorded code (at
this point I haven't recorded because I don't know where to or How to).
....Note that this is a Sub procedure in a worksheet formula. If you
examine the code, however, you'll see a reference to the UserName
property. You can use this information when creating a Function
procedure. For example, the following Function procedure uses the
UserName property. This function, when used in a worksheet formula,
returns the name of the user. ...

 

[CBrausa]

I have followed the steps in my Excel book on "Using the Macro
Recorder", I get to step 10. then it doesn't make sense.
10. Double click the VBA module that contains your recorded code (at
this point I haven't recorded because I don't know where to or How to).
....Note that this is a Sub procedure in a worksheet formula. If you
examine the code, however, you'll see a reference to the UserName
property. You can use this information when creating a Function
procedure. For example, the following Function procedure uses the
UserName property. This function, when used in a worksheet formula,
returns the name of the user. ...

 

[Bryan Hessey]

If you want to record a macro, select Tools, Macro, Record New Macro,
select a name, and then start recording the steps you want. Select
Tools, Macro, Stop recording to finish (if the small 'stop' is lost).

Select Tools, Macros, Macro, select your macro and EDIT to see the
code.

Hope this helps

--