+ Reply to Thread
Results 1 to 4 of 4

Unhide worksheets with message box entry

Hybrid View

wpryan Unhide worksheets with... 04-26-2010, 11:27 AM
Marcol Re: Unhide worksheets with... 04-26-2010, 11:51 AM
wpryan Re: Unhide worksheets with... 04-26-2010, 12:10 PM
Marcol Re: Unhide worksheets with... 04-27-2010, 11:15 AM
  1. #1
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Unhide worksheets with message box entry

    I have some code, the purpose of which is to unhide a group of worksheets based on the data entry of one or two cells (H11 and H12). I am trying to enforce this by way of a message box entry. The problem I am encountering is that the only place I can find that it works (more or less) is in the Worksheet_Change event, and the problem with this is that every other line of code that causes a change in the worksheet will invoke the message box. I only want it to appear based on a change in range H11. If i try to to put it in the Worksheet_SelectionChange then it crashes (hard - stack overflows and other errors, including simply shutting down). I'd appreciate any advice here, or maybe there's another way to do it... The terms UnhideOpen, UnhideTSC, UnhideRoutine and UnhideDemo refer to the various worksheet groups.
    Application.ScreenUpdating = False
            
    Dim purpose As String
    purpose = Range("H11").Value
    Dim Answer As String
        
    If purpose = "" Then
        UnhideOpen 
    End If
            
    If purpose = "Annual PM or TSC" Or purpose = "Product Clinical Demo" Or _
    purpose = "Installation" Or purpose = "Incoming Inspection" Then
        UnhideTSC 
    End If
    
    If purpose = "Clinical Support" Or purpose = "Emergency Service" Or _
    purpose = "Upgrade or Update" Then
        Answer = MsgBox("Will a TSC be performed during this service visit?", vbYesNo + vbQuestion, "Perform TSC?")
            If Answer = vbYes Then
                UnhideTSC
            Else
                UnhideRoutine
            End If
    End If
        
    If purpose = "Conference or Commercial Demo" Or purpose = "Decommission" Then
        UnhideDemo
    End If
    
    Application.EnableEvents = False
    Last edited by wpryan; 04-27-2010 at 05:37 PM. Reason: Solved

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Unhide worksheets with message box entry

    Try thiis,

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Response As Long
        Dim isect As Range
        
        On Error GoTo ExitLine
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set isect = Intersect(Target, Range("H11"))
            
        If Not isect Is Nothing Then
            Select Case Target
                Case ""
                    UnhideOpen
                Case "Annual PM or TSC", "Product Clinical Demo"
                    UnhideTSC
                Case "Clinical Support", "Emergency Service", "Upgrade or Update"
                    Response = MsgBox("Will a TSC be performed during this service visit?", vbYesNo + vbQuestion, "Perform TSC?")
                    If Response = vbYes Then
                        UnhideTSC
                    Else
                        UnhideRoutine
                    End If
                Case "Conference or Commercial Demo", "Decommission"
                    UnhideDemo
            End Select
        End If
    ExitLine:
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        On Error GoTo 0
    End Sub

    Cheers

    Sorry for rush on time limited machine
    Last edited by Marcol; 04-27-2010 at 11:09 AM. Reason: Errors in code corrected

  3. #3
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Unhide worksheets with message box entry

    Thanks for your help. I had to change the line
    Set isect = intersection(Target, Range("H11"))
    to
    Set isect = intersect(Target, Range("H11"))
    in order to get rid of a "Sub or Finction not defined error. However, when I did that and ran the code, It looks like it's going through a huge loop until an error message appears:

    Run-time error -2147417848 (80010108)
    Method '_Default' of object 'Range' failed

    Then Excel shuts down...
    Last edited by wpryan; 04-26-2010 at 12:11 PM. Reason: Incomplete answer

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Unhide worksheets with message box entry

    Hi

    I have corrected my mistake in post #2 and redefined answer (now Response as Long).
    Sorry about that. (I have edited post #2 rather than post the revised code.)

    I have tried the code and can find no problems.

    Obviously I cannot test your subs UnhideOpen, UnhideTSC, UnhideRoutine, and UnhideDemo.

    I think it is in one of these that the problem must lie,

    Can you post these subs, or better still a sample version of your workbook?

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1