Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    cell entry via VBA (excel 2003)

    HI

    Is there a way to put text in a cell say Sheet 1!A1 via VBA and to avoid it being removed. (easily that is).

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: cell entry via VBA (excel 2003)

    You can do the following:
    - Select the cells the user must be able to change.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.
    - Select Tools | Protection | Protect Worksheet...
    - Set a password if you like, and specify what the user should be able to do, then click OK.
    The user can only edit the cells you unlocked.
    To change a locked cell in VBA, you must unlock the sheet, set the value, then lock the sheet again:

    With Worksheets("Sheet1")
    .Unprotect
    .Range("A1") = "Braddy"
    .Protect
    End With

    If you have set a password, specify it both for Protect and Unprotect, e.g

    .Unprotect Password:="secret"

  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: cell entry via VBA (excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 24-Apr-05 06:01. Added clarification)</P>If you don't want the protection route, the following macro could also do it

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
    MsgBox "'Cell A1' may not be changed"
    End With
    End If
    End Sub</pre>


    It looks for a change in if A1 is changed, it "UNDO"es it and then pops up a message.

    Steve
    PS the macro should go into the worksheet object not a normal module.

  4. #4
    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: cell entry via VBA (excel 2003)

    Not mentioned in my original post, but to add text to the cell after the macro is created, you can use a macro like this (this goes in a normal module):

    <pre>Sub TextInA1()
    With Application
    .EnableEvents = False
    Range("A1") = "Braddy"
    .EnableEvents = True
    End With
    End Sub</pre>


    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: cell entry via VBA (excel 2003)

    Hi Steve

    Thank you for your response This suits my purpose nicely.

    Thanks also to Hans.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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