Protection issues

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top