Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Excel 97-custom function (Excel 97)

    I need some help with a function, and or a sub, what I have is a Spreedsheet that I want to restrict users from inputting data into a certain range of cells. The work is to be shared, what I would like it to do is If a range is selected, I want it to exit the sub. Anyone? So If the user selects A1, by mistake, I want it to exit with a msgbox.

    Thank you in Advance
    Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 97-custom function (Excel 97)

    Take a look at worksheet protection. Protecting a worksheet in Excel is a two-step process:
    1. <LI>Select the cells that the user must be able to change.
      Select Format | Cells...
      Activate the Protection tab.
      Clear the Locked check box.
      Click OK
      <LI>Select Tools | Protection | Protect Sheet...
      If you like, you can specify a password needed later to unprotect the sheet (note: this is not a strong protection, it is relatively easy to get around it.)
      Click OK. The menu item will change to Unprotect Sheet.
    Users will still be able to select cells in the worksheet, but they can only modify the contents of the cells you unlocked. You can also specify that the user can only select unlocked cells, but you need a bit of Visual Basic for that, it cannot be set from within Excel itself.

    To limit the user to selecting only unlocked cells, do the following:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11)
    <LI>In the Project Explorer (the tree view in the left hand pane), double click ThisWorkbook for the relevant workbook.
    <LI>Type or copy the following code:

    Private Sub Workbook_Open()
    Worksheets("Sheet1").EnableSelection = xlUnlockedCells
    End Sub

    where Sheet1 must be replaced with the name of the worksheet you have protected.
    <LI>Switch back to Excel.
    <LI>Close, save and reopen the workbook.[/list]You should now be restricted to the unlocked cells.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 97-custom function (Excel 97)

    Is this what you are after?
    Add this to the worksheet object (not a normal module) of interest. Change the range as desired ("sStayout") as desired.

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim sStayOut As String
    Dim rng As Range
    sStayOut = "A1:B10"
    Set rng = Range(sStayOut)
    If Not Intersect(Target, rng) Is Nothing Then
    rng.Cells(rng.Count).Offset(1, 0).Select
    MsgBox "You must stay out of the range: " & sStayOut
    End If
    Set rng = Nothing
    End Sub</pre>


  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Excel 97-custom function (Excel 97)

    Thanks Stave and Hans.....
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

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