Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Protection issues

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi RET79,

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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,033
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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*
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    RET79

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •