Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Prevent User from SAVING / SAVE AS Excel file [SOLVED]

  1. #1
    Join Date
    19th March 2003
    Location
    Bethlehem, South Africa
    Posts
    339
    Hi again everybody!

    I have been going along just fine with all your help, and guess what: New issues!

    I've been writing macros (via Excel Macro recorder) and tweaking them with your help to VeryHide every single sheet except the one that the macro has directed the user to.

    If the guy uses the macros to exit and close the program, the data is saved and the file re-opens on sheet1, the one it's supposed to.

    What happens now is this: If I click the save button or use the File/Save route and then exit the file via File/Close (or the close button), the sheet that the user was on when he saved the file is opened with Sheet1 when he opens the file again.

    I do not want that!

    What can I do to prevent him using the above ways, and also the Save As way?

    What can I do to close a worksheet as VeryHidden at all times if it's not the active sheet? What can I do do ALWAYS have only sheet1 open up on startup?

    I gotta learn ta code, don' ya tink?

    These late nights are interfering with my cycling. I'm going to bed now!

    Sleep well all of you!

    Regards

    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th January 2003
    Posts
    51
    This may or may not be what you are looking for but....

    take a look at the workbook_beforeclose event in VBA...

    every time the workbook is about to close it will run through whatever code you put out there...including hiding all your sheets except your initial macro page...

    Hope this helps.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th March 2003
    Location
    Bethlehem, South Africa
    Posts
    339
    Hi Richie.

    Have you ever heard of a game called toilet tennis? You go into the loo, sit down and then find yourself staring at the instructions plasted on the door in front of you. These say: ..."Now look at the sign on the wall to your left" You look. That sign says: "Now look to the right" That sign says: "Now look to the left"

    That's what your links are doing. Merely showing me left to right. Was that purposefully done or do you have some answer for me?

    Jason!

    Thanks for the advice. I'll definitely have a look at that and see where it gets me.

    Regards

    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th March 2003
    Location
    Bethlehem, South Africa
    Posts
    339
    Just found the thing I couldn't find last night! It's a bit of VBA code that Roy sent me some time ago.

    Here's the link (Last post on the page): http://www.ozgrid.com/forum/viewthre...id=1151&page=3

    I'll try to understand what it does and let you know!

    Ta, Roy!

    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707
    Hi Harry

    If I have understood you. The code below will stop the Save As being used. To use this code, right click on the Excel icon, top left next to "File" and select "View Code".
    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean #41; 
        If SaveAsUI = True Then 
            Cancel = True 
            MsgBox "Oh no you don't!", vbCritical 
        End If 
    End Sub 
    
    

  6. #6
    Join Date
    19th March 2003
    Location
    Bethlehem, South Africa
    Posts
    339
    Dave!

    I have not had time to look at Roy's code, but having looked at yours (And I liked the message "O no you don't!"), and having viewed the code on the Excel button, I can sense that you've put me onto something. Thing is, however, I can see what that code does, but with the Workbook_BeforeClose and the Workbook_Open code differing as far as true and false are concerned, I am baffled as to what code to use and what it will do. (Just as I think I'm getting ahead with the understanding of the logic behind VBA, I get stumped again. (And by the way, congratulations on winning the World Cup again!)).

    You did understand correctly, but more than just wanting to disable "save as" of the file, and because it is merely a working thing like a calculator, the user should not be allowed any saving or copying options at all while the file is open. Within the file, however, the code has to save certain data, and that is the UserID upon first opening of the file.

    Thereafter, having written that code in a cell, it is to merely check for changes in values.

    Can that code be applied to my workbook and which one do I use? How does that affect the use of the code you gave me?

    I'll get going on this later this evening and keep you posted.

    Kind regards

    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    Originally posted by HarryFrohlich
    Hi Richie.

    Have you ever heard of a game called toilet tennis? You go into the loo, sit down and then find yourself staring at the instructions plasted on the door in front of you. These say: ..."Now look at the sign on the wall to your left" You look. That sign says: "Now look to the right" That sign says: "Now look to the left"

    That's what your links are doing. Merely showing me left to right. Was that purposefully done or do you have some answer for me?
    Harry,

    What are you talking about? I can't see the post you are referring to.

    (Only joking, I DID post a link but it seems to have been removed )

    Have you read the thread here:
    http://www.ozgrid.com/forum/viewthread.php?tid=1113
    or the 'PLEASE READ THIS' link on the New Topic page regarding Cross Posting ?

    The reason that I posted the links on each site was not for your benefit but for the benefit of anyone who may be contemplating helping you out, only to discover that you have already received a reply elsewhere.
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  8. #8
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684
    OK Darn loads here !!!

    Jack opinion is as I have said before

    Post ans reply any where you like – its any site members right

    All I ask correctly of different is to link the cross posts – that’s all

    I know better than most acting giving a post feed is not a few seconds work, takes time. I should know, I was criticized as I said in post I have locked this feed and before I had a chance a member said NO YOU HAVE NOT !!!!

    But I know most of the regular guys on all the forum’s and we are buddy’s, no one will step in and say oi oi if you have not finished your work, it takes time to link all the posts with feeds. All the guys I know will be reasonable and allow time to do this.

    Cross posts really annoy me, in my opinion, but I see it as good practice to say see feed XXX, Jack learns from this, and I would love to read an interesting thread on ANY site, as this help me as much as the original poster.

    Ozgrid will provide answers to almost any question, we are totally spoilt, by top members all very different in their expertise, and skills.

    All I ask is post to the board, no U2U

    I hope I have expressed my opinions as a level member not as any preserved status attached to my sign. Im an ordinary guy, wanting to help guys in troubles related to this forum.

    Kindest possible regards, without prejudice


    Jack in the UK

    BTW , mark cross post guys, that’s all

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684
    Harry

    Top MAN!!!

    Hey no never, any lesser a memebr than i, we are all the same, well in Jacks opinion. If we post questions or post answers = good

    So keep posting regardles, and carry on just as you are, means a lot to Jack

    Just please link up cross posts, this will help Jack a lot, this is how i learn!!!

    LOL!!! as you take my words, please with pleasure and just for you long version

    Kindest possable regards

    Jack in the UK [my home - South London]

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th March 2003
    Location
    Bethlehem, South Africa
    Posts
    339
    What's LOL?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Prevent Saving Certain Worksheets On Save As
    By RickWHaas in forum EXCEL HELP
    Replies: 2
    Last Post: January 19th, 2008, 08:21
  2. Replies: 3
    Last Post: March 24th, 2006, 15:26
  3. Replies: 3
    Last Post: October 21st, 2003, 21:50
  4. Replies: 5
    Last Post: August 7th, 2003, 05:52
  5. Replies: 3
    Last Post: August 4th, 2003, 01:21

Bookmarks

Posting Permissions

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