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

 

[RET79]

Hi Brett,

Yes, on this board I wouldn't really call myself a master compared to the average poster. But at work I'm a genius! (That says more about their excel skills than mine though.....)

As it happens I find it difficult to get some of my ideas accepted at work. People are a little bit afraid of this macro business. Also another problem I find is that if someone has been on an excel course, they think they know all there is to know about excel! But as I have found, there is so much to learn , good grief!

RET79

 

[brettvba]

yea you find that you know more than the programmers do at your work even after just a few courses I think the main thing is self teaching and alot of this posting site helps as your ideas can be made into reality instead of going i wonder if i could do this.

 

[Damon Ostrander]

RETT79,

Here's some event code that will prevent the user from even selecting a cell until the cell above has been filled.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row <> 1 Then
If IsEmpty(Target.Offset(-1)) Then
MsgBox "This cell cannot be edited until data has" & vbCrLf & _
"been entered into the cell above.", vbInformation, _
"Data prerequisite not met"
Dim PrevCell As Range
Set PrevCell = Target.End(xlUp)
If IsEmpty(PrevCell) Then
PrevCell.Select
Else
PrevCell.Offset(1).Select
End If
End If
End If
End Sub

To install this code, simply right-click on the worksheet tab, select View Code, and paste it into the code pane.