Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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".
          Code:
          Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
          If SaveAsUI = True Then
             Cancel = True
             MsgBox "Oh no you don't!", vbCritical
          End If
          End Sub

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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]

                  Comment


                  • #10
                    What's LOL?

                    Comment


                    • #11
                      LOL

                      Laugh out Loud

                      Means

                      Friend
                      Humor
                      No disagreement
                      Fun
                      Buddys

                      Kindest possible regards

                      Jack from the United Kingdom

                      Comment


                      • #12
                        Hi Harry,

                        If you seek forgiveness then OK, I forgive you. However, as far as I am concerned there is nothing to be forgiven for. We are all entitled to express our opinions in an open forum and I wouldn't have it any other way.

                        I am, however, pleased that you are now able to see what its like 'from the other side', so to speak. Surely it is better to educate than to ban?

                        I look forward to being able to help you again in the future.

                        Jack,

                        Thanks for your input. Much appreciated.
                        Cross-poster? Read this: Cross-posters
                        Struggling to use tags (including Code tags)? : Forum tags

                        Comment


                        • #13


                          Thanks for all the kind words, both of you!

                          Regards

                          Harry

                          Comment


                          • #14
                            some Biltong for all of us, to go with the beers

                            Hope this helps,
                            Chris

                            Comment


                            • #15
                              Some day ....

                              (Scanned biltong, I'm told, tastes like newspaper. Maybe after a few (a lot!) of beers it wouldn't, eh?!

                              Address?

                              Comment

                              Working...
                              X