Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Prevent the user from leaving a worksheet

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

  • #16
    Re: Prevent the user from leaving a worksheet

    So many trying to help and trying to understand what you are doing. Your replies to me mean that I can find other people to help. Good luck.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #17
      Re: Prevent the user from leaving a worksheet

      You overlooked the core of my post; surprisingly in the first line:

      The best way to guide (not control) a user is using userforms (what's in a name).

      It's true you can start programming from scratch, but an analysis in advance of what you want to accomplish is also regarding which method would be the most appropriate. If you saved time skipping that phase, don't be surprised it will cost you double the time afterwards, restoring what could have been prevented in the first place. There must have been some rationality at MS having introduced/designed the 'userform'.
      'Structuring precedes coding'

      If you want to get an impression how 'non-reducible' amounts (e.g. 4000 lines) of code can be reduced to nearly oneliners, check some of my posts in this forum (or elsewhere).

      Comment


      • #18
        Re: Prevent the user from leaving a worksheet

        Thank you all. But no thanks.

        Give an insoluble problem to a pure mathematician and he will return with the answer, "I can't solve your problem, and I can prove there is no solution."

        Give it to an applied mathematician and he will return saying, "I can't solve your problem, but here's a similar problem I can solve.'

        My request was crystal clear. Apparently there is no solution.

        So just say so. Stop offering solutions to "closely related problems."

        Good bye.

        (snb: To suggest that with "your" approach, "(e.g. 4000 lines) of code can be reduced to nearly oneliners" with no knowledge of my code is the height of hubris. I'd exemplify my capabilites in creating "best of class" by naming major systems . . . nah, not worth it. You magically know what my code does already, and have reduced it to three lines of code and a cuppa Jolt. To suggest that I didn't start by creating a Functional Description, then a Detailed Specification, is insulting. You do know what those are, don't you? Are flowcharts too too yesterday?)

        Comment


        • #19
          Re: Prevent the user from leaving a worksheet

          OzMVP. I see your approach. I think the user would freak out at having all his sheets "disappear". Rather not.

          I may be wrong but it looks like it changes them to visible whenever there is action on a row at or beyond 3. I doubt that would detect that the data area is empty or not. Further, it would hide the other sheets the moment this sheet is activated, even though it contains the needed data. No. The user has to be able to go to the other sheets, anytime, except when he is at this sheet and causes it to contain no data. Without "destroying" ("They're all gone!", the user sobs) the others.

          Per other response: I now know there is no construct that will do exactly what I want, which has been specified, with precision. Good enough. Simple answer. Not the answer I would have preferred, but definitely preferable to pseudo-answers that are just weaseling out from saying, "You can't do it."

          Comment


          • #20
            Re: Prevent the user from leaving a worksheet

            The test I used to determine whether the sheets should be made visible was a quick, crude test for demonstration purposes. It could be replaced by any test, as complete and complex as you desire.

            I'm not sure that the user would notice the disappearing tabs, most trouble comes from users not being aware.

            How about this approach? Use the same kind of Change event to make the Public variable Flag True if no change has been made in "Master", then in the ThisWorkbook module, code like
            Code:
            ' in ThisWorkbook
            
            Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                If Flag And (Sh.Name <> "Master") Then
                    Worksheets("Master").Select
                End If
            End Sub
            Code:
            ' in Master's code sheet
            
            Private Sub Worksheet_Activate()
                Flag = True
            End Sub
            
            Private Sub Worksheet_Change(ByVal Target As Range)
                Rem crude test for test of concept
                Flag = (Target.Value <> "ok")
            End Sub
            Code:
            ' in normal module
            
            Public Flag As Boolean
            
            Sub otherSubs()
            '...

            Comment


            • #21
              Re: Prevent the user from leaving a worksheet

              Edited: Any reference to the Shows sheet from the VBA while in another effectively temporarily activates the sheet, then returns (apparently). So, checking for the empty Shows sheet in the startup code triggered the "don't leave" code if Shows is empty. Ends up going in a circle several times.

              Required a startup flag to disable that until startup -- whiich does a lot more -- is done.

              ==================

              OzMVP: Beuatiful! Thank you, both for addressing my problem directly, and finding the solution.

              One step further: Since there is nothing to prevent the user from saving and closing the workbook with Shows enpty, I've added the detection and action if Shows are empty to the worknbook's startup procedure.

              With

              Public ShowsMissing As Boolean
              Public StartingUp As Boolean

              in a normal module, . . .

              In ThisWorkbook:
              Code:
              Private Sub Workbook_Open()
                  Dim ShowRows As Integer
                  StartingUp = True
                  Call SetPublics
                  StartingUp = False
              End Sub
              Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                  If ShowsMissing And (Sh.Name <> "Shows") Then Worksheets("Shows").Select
              End Sub
              Within SetPublics, code lines at its end:

              Code:
                  If Sheets("Shows").Columns(1).Find(What:="", After:=Range("A2"), _
                          LookIn:=xlValues).Row - 1 < 3 Then
                      MsgBox "Shows are needed."
                      ShowsMissing = True
                      Sheets("Shows").Activate
                      ActiveWindow.ScrollRow = 3
                      Range("A1").Select
                  End If
              Then, in Shows:
              Code:
              Private Sub Worksheet_Activate()
                  ShowsMissing = False
              End Sub
              Private Sub Worksheet_Deactivate()
                  If StartingUp Then Exit Sub
                  If Columns(1).Find(What:="", After:=Range("A2"), _
                      LookIn:=xlValues).Row - 1 < 3 Then
                      MsgBox "You can't leave Shows empty!!"
                      ShowsMissing = True
                  End If
              End Sub
              Works perfectly!
              Last edited by NeverLift; December 11th, 2012, 05:31. Reason: Not quite perfect

              Comment


              • #22
                Re: Prevent the user from leaving a worksheet

                Final comment:

                I have, currently, 130 "client" rows in their summary sheet. Updating each is quite involved. I put in a public counter that is incremented upon activation, in each sheet, because the run time skyrocketed!

                Turns out a full update for 1 client switches the sheet focus, only when necessary, 12 times during that process (please, don't tell me that it should be recoded!), so it's around 1,500 to do all. Normally, running the full update against all 130 clients takes about 30 seconds.

                When I added this code, it took . . . 40 minutes!!

                So, I now simply check the public count of Shows, set at startup and updated whenever the users leaves the Shows sheet, in each sheet's activate. Not elegant, but works.

                Been interesting. Thanks, all.

                Comment


                • #23
                  Re: Prevent the user from leaving a worksheet

                  "switches the sheet focus, only when necessary"

                  I won't tell you to redo a working code, but without seeing the existing code, its hard to be sure that selecting a sheet ("switching the sheet focus") is really nessesary.

                  Glad it worked for you.

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X