Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 23

Thread: Prevent the user from leaving a worksheet

  1. #11
    Join Date
    8th March 2010
    Posts
    1,651

    Re: Prevent the user from leaving a worksheet

    The best way to guide (not control) a user is using userforms (what's in a name).
    If a user's tries to circumvent 'your' program you should reconsider what you have built.
    If your 'solution' isn't helpful to the user, force isn't the right answer; listening to the user is.

    You can't 'force' a user.
    There's always ctrl-Break or ctrl-alt-del.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #12
    Join Date
    16th March 2008
    Posts
    26

    Re: Prevent the user from leaving a worksheet

    Nope.

    Code now is

    VB:
    Private Sub Worksheet_Activate() 
    End Sub 
     
    Private Sub Worksheet_Deactivate() 
        Worksheets("Shows").Select 
    End Sub 
    
    
    I put a breakpoint at the end of activate. "Shows" was indeed activated. I stepped along, it returned to finish the deactivate that had issued the select, finished it, then went to the sheet the user tried for, errored out.


    snb: Not a useful answer. This is a large complex system. I'm just putting in failsafes to preclude user accidents from destroying it, there are dozens of them throughout. it is totally illogical for a user to run with no Shows entries, but accidents happen and I'd rather prevent them from being disasters than say, "well, the user can do what he wants." Of course he can, he can unprotect protected sheets and mess wuth them. But that would be deliberate sabotage -- and he gets what he deserves -- not an accident. By "forcing" him to stay here, I'm inviting repair -- which I know this user wants.

    I'm beginning to think there is no way to capture the exit from a sheet to another via a sheet tab and not go to that tab's sheet. If there is a way, fine. Let me know that. If that's impossible, I'll live with this kind of (pretty stupid) user error.

    Rewriting about 4,000 lines of VBA ain't gonna happen.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #13
    Join Date
    27th February 2006
    Location
    Cochrane, AB
    Posts
    56

    Re: Prevent the user from leaving a worksheet

    I would not give up so easily,
    You have replies from forum members that have 1,000's of posts. Your question does seem to be clear enough that an answer is relevant to your situation.
    Maybe supply a sample workbook that shows what you are looking for. You need to help us help you. Why can you just give us the complete details?
    Where the heck did 4,000 lines of VBA come from?( I am asuming it has something to do with the macro recorder), What would any of that 4,000 line of code have to with your question?
    Just tell us what you are trying to do!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #14
    Join Date
    16th March 2008
    Posts
    26

    Re: Prevent the user from leaving a worksheet

    The 4,000 line comment was in frustration when it seemed one reply basically told me to redo the entire system using a different approach.

    No, the code is not recorded macros. I record a macro generally to get an outline of some portion of the logical operation I wish to use. It almost never is exactly what I need -- it names specific cells when I want code that runs for a range selected by the data, etc.

    The code is VBA, about 50 different procedures, some short, some dreadfully complex. It's a complete system for a "client" -- actually, being done off the books, as a charity for a worthy organization. You really don't want all the details.

    So here is the code. Start on the Shows sheet. Click the SomethingElse tab. No problem.

    Then go back to Shows, clear A3, try to tab SomethingElse. That's the problem.

    I want to stay at Shows, with a message to the user about his error. There will probably be other code there that does not interfere with the Undo button, if I ever get this working.

    I'm trying to make it bulletproof, probably 10% of the code is handling anomalies. Most sheets are protected against accidents and the data are imported from very messy downloads, with a lot of reformatting, analysis, etc., by procedures (initiated with a single hot key), then buttons everywhere accessing procedures for specific functions, many of them.

    But Shows contain user-provided data, its entries can be edited, added to, or deleted. If by accident the user deletes everything in Shows that's almost certainly an accident, and I want to keep him there so he can use the Undo button before screwing up all the work done to get here.

    BTW: I've been programming, designing systems, etc., for literally half a century. O for the good old days of Fortran and Assembler. Google vba excel. 10 MILLION HITS. 99% from programmers trying to do something and not being able to find a real, complete reference document. I think that says something. Down with OOP!

    OK, I needed that rant. VBA is actually a very cool package, but it's been some frustrating weeks figuring out how to do things that I would expect are pretty fundamental. (Nowhere near its roots, Basic.)
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #15
    Join Date
    23rd April 2007
    Posts
    3,489

    Re: Prevent the user from leaving a worksheet

    Try putting something like this in the sheet's code module


    VB:
    Dim Flag As Boolean 
     
    Private Sub Worksheet_Activate() 
        Dim oneSheet As Worksheet 
         
        For Each oneSheet In ThisWorkbook.Worksheets 
            If oneSheet.Name <> Me.Name Then 
                oneSheet.Visible = xlSheetHidden 
            End If 
        Next oneSheet 
        Flag = True 
    End Sub 
     
     
    Private Sub Worksheet_Change(ByVal Target As Range) 
        Dim oneSheet As Worksheet 
        If Flag Then 
            If (3 <= Target.Row) Then 
                For Each oneSheet In ThisWorkbook.Worksheets 
                    oneSheet.Visible = xlSheetVisible 
                Next oneSheet 
                Flag = False 
            End If 
        End If 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #16
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,508

    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

    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.

  7. #17
    Join Date
    8th March 2010
    Posts
    1,651

    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).

    Excel Video Tutorials / Excel Dashboards Reports


  8. #18
    Join Date
    16th March 2008
    Posts
    26

    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?)

    Excel Video Tutorials / Excel Dashboards Reports


  9. #19
    Join Date
    16th March 2008
    Posts
    26

    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."

    Excel Video Tutorials / Excel Dashboards Reports


  10. #20
    Join Date
    23rd April 2007
    Posts
    3,489

    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
    VB:
     ' in ThisWorkbook
     
    Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
        If Flag And (Sh.Name <> "Master") Then 
            Worksheets("Master").Select 
        End If 
    End Sub 
    
    
    VB:
     ' 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 
    
    
    VB:
     ' in normal module
     
    Public Flag As Boolean 
     
    Sub otherSubs() 
         '...
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: January 28th, 2012, 14:27
  2. Replies: 4
    Last Post: January 22nd, 2011, 01:53
  3. Replies: 2
    Last Post: January 21st, 2010, 03:42
  4. Replies: 4
    Last Post: May 20th, 2008, 15:34
  5. Replies: 4
    Last Post: May 16th, 2005, 10:55

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