+ Reply to Thread
Results 1 to 4 of 4

Stopping Prompt Message After One Time

Hybrid View

  1. #1
    Registered User
    Join Date
    10-10-2011
    Location
    St Louis, MO
    MS-Off Ver
    Excel 2010
    Posts
    13

    Stopping Prompt Message After One Time

    I have some columns that auto unhide and when this happens, a prompt message displays to inform the user to complete the next level.

    However, whenever the user makes another selection, the prompt box keeps coming back up.

    How would I only run the message once?

    This is the code I am using and I have attached the sheet,

    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Columns("C:D").EntireColumn.Hidden = Range("B49").Value < 3
        
        Columns("E:F").EntireColumn.Hidden = Range("D49").Value < 3
        
        If Range("D49").Value = 3 Then
        MsgBox "Please complete assessment for Mastery Level"
        
        ElseIf Range("B49").Value = 3 Then
        MsgBox "Please complete assessment for Intermediate Level"
    
        End If
        
            
    End Sub
    Attached Files Attached Files
    Last edited by JonathonHardy; 11-16-2011 at 11:18 AM.

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Stopping Prompt Message After One Time

    Try this...I think i understand what you are after. Basically, change the order of the operations (prompt then unhide) then you can use and IF statement to only perform the action IF the columns are hidden.

    That way, once they are not hidden, the message will not fire again.

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Columns("E:F").EntireColumn.Hidden = True Then
            If Range("D49").Value = 3 Then
                MsgBox "Please complete assessment for Mastery Level"
                Columns("E:F").EntireColumn.Hidden = Range("D49").Value < 3
            End If
        End If
        If Columns("C:D").EntireColumn.Hidden = True Then
            If Range("B49").Value = 3 Then
                MsgBox "Please complete assessment for Intermediate Level"
                Columns("C:D").EntireColumn.Hidden = Range("B49").Value < 3
            End If
        End If
        
    End Sub
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-10-2011
    Location
    St Louis, MO
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Stopping Prompt Message After One Time

    Thank you. That worked great.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Stopping Prompt Message After One Time

    But, i wasn't sure if this made the sheet function exactly how you want, it does handle the "don't prompt again" part of things

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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