1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: error "400" running an Excel macro

Discussion in 'Business Applications' started by rcobos, Jul 5, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. rcobos

    rcobos Thread Starter

    Joined:
    Jul 5, 2012
    Messages:
    3
    Hi Guys:

    I have created a macro( 2 line macro actually) that goes through my workbook and it supposed to clear the value from cells b1:b68 on main page. When I try to run the macro with the sheet protected I get a pop up that just says "400" in it with a red circle that has a white "X" through it, whit the sheet unprotected works perfectly !. Because it is a survey I need to protect teh content, to be manipulated by many. Can somebody give a clue? Here is the code:

    Public Sub Clear()
    Sheet1.Cells.Range("B17:B68").ClearContents
    End Sub


    Windows 7
    MExcel 2010

    Many thanks

    Rod
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    So just add a couple of lines of code to first unprotect the sheet, then clear the contents, the protect the sheet again.

    Rollin
     
  3. ssye894

    ssye894

    Joined:
    Jul 5, 2012
    Messages:
    7
  4. rcobos

    rcobos Thread Starter

    Joined:
    Jul 5, 2012
    Messages:
    3
    Thank you very much.......I tried it but when you run the macro, it ask you to input the password to unprotected the sheet, then i have to give everybody the password....... there is no point to protect it............ so back to square one..:(
     
  5. GeorgeJ

    GeorgeJ

    Joined:
    Jan 16, 2012
    Messages:
    144
    Could you not make the VBA unlock the sheet by providing the password
    do what you want
    then lock the sheet again using the password

    Then the user wouldn't know the password if the macro was locked too, possibly ThisWorkbook.Protect Password:="yourpassword"
     
  6. rcobos

    rcobos Thread Starter

    Joined:
    Jul 5, 2012
    Messages:
    3
    Thank you very much for your help, worked like a charm !!!

    Thank for your time !

    Rod
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Keep in mind that if the macro code is attached to the workbook you are sending out others will be able to view the code and retrieve the password unless you password protect the code module.

    Rollin
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059807