Protection issues

[RET79]

Hi.

I need to protect and make some input sheets for users fool proof.

For instance, the macro I have written refers to dynamic ranges where user will list data to be used in the macro.

What would be the best way of notifying the user to not leave any blanks when putting the data in, as the dynamic range won't pick them up. And no, I don't want to alter the dynamic range to being the other style.

RET79

[Damon Ostrander]

Hi RET79,

You can use Cell Validation to bring up a message as soon as the user activates the cell.

[RET79]

Damon,

I am not sure how to do this validation in this case. What I need is that the user can't enter data if the cell above is blank.

Any help appreciated.

RET79

[Aladin Akyurek]

On 2002-04-20 08:17, RET79 wrote:
Hi.

I need to protect and make some input sheets for users fool proof.

For instance, the macro I have written refers to dynamic ranges where user will list data to be used in the macro.

What would be the best way of notifying the user to not leave any blanks when putting the data in, as the dynamic range won't pick them up. And no, I don't want to alter the dynamic range to being the other style.

RET79

Just curious: What do you mean by the dynamic range won't pick them [blanks] up? As far as I know, a dynamic range name will pick up any blank, e.g., between A1:A7, when A7 itself has a non-blank value.

Aladin

[RET79]

Aladdin,

Without going into great detail, it is a range of the

Range([A1],[A1].End(xldown))

variety.

So if an user will start listing the inputs in this column, and then leave a blank cell, then start listing again, then the rest of them won't be picked up by the range.

RET79

[ This Message was edited by: RET79 on 2002-04-21 15:24 ]

[Jack in the UK]

You subject states protection, OK not related question exactly but my earlier post covers this nicely and simple, again just in case your no awair.

http://www.mrexcel.com/board/viewtop...5838&forum=2&3



*MARKER PROTECTION*

[brettvba]

or some code for checking for null values

Sub Button2_Click()
On Error Resume Next

'could use "CellRef",or "RangeName" instead of "ActiveSheet"
'if there are no blank cells then close and save the sheet
'Otherwise prompt the user to fill in the blanks
If Range("a1").CurrentRegion.Cells.SpecialCells(xlCellTypeBlanks).Activate = False Then
MsgBox "All fields checked for null values - OK to exit", vbInformation + vbOKOnly, "DATABASE CONTROL"
Workbooks("yourworkbook.xls").Close SaveChanges:=True
Exit Sub
Application.Quit
Else
'Help:Special cells method
If Range("a1").CurrentRegion.Cells.SpecialCells(xlCellTypeBlanks).Activate = True Then
MsgBox "You must fill in these blank fields", vbCritical + vbOKOnly, "DATABASE CONTROL"
End If
End If


End Sub

[RET79]

I have heard about these 'event' things, when a program will do something based on an event.

Perhaps what I need is some sort of error message to appear if someone puts data into a cell below a blank cell??

Sorry, I am a novice with events and userforms.

RET79

[RET79]

Brett, thanks I'll give that a try it sounds good!!

RET79

[brettvba]

Sometimes its a bit misleading as many may assume that you are an excel master but that my only have come about because you ask a lot of questions . when answering questions one would look at the status of the poster in the complexity of the answer!