Pop up Window

[jaimed77089]

I am looking to add a warning that will pop up, if a cell is not filled in with a Y. The cell should be completed before the workbook is closed. Is there a way to do this?

 

[Smitty]

Welcome to the Board!

How 'bout something like this:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> rng = Sheets("Sheet1").Range("A1")
        
        <SPAN style="color:#00007F">If</SPAN> rng <> LCase("y") <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Cell " & rng.Address & " on " & rng.Worksheet.Name & " must be filled in!", _
                vbCritical + vbOKOnly, "Hey, You Forgot Something!"
            Application.Goto rng
            Cancel = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[jaimed77089]

Thanks, this has got to be one of the most helpful Excel sites I've used. First, and only, one I've actually joined.

I have a second question, related to the first. This issue just gets better and better. What I'm trying to do, is create a reminder for the user of this report, but I want the reminder to only pop up when they are using, I dont' want our business partners to see it. Any suggestions? It doesn't have to be a pop up window, but it would have to remind them everytime they update the workbook.

 

[Nimrod]

Working with PennySaver's solution I've added the requested functionality.
In this version you must include a hidden sheet in your workbook . This sheet must be named "Users" . In column A of the "Users" sheet you must type in the NT Login names of the "Users" that will be editors. When the "Users" with login names in the list use the book they will be prompted. If their name is not in the list they will not be prompted.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rng As Range
    Dim EditorList As Range
    
    Set EditorList = Sheets("Users").Columns("A:A")
    
    If WorksheetFunction.CountIf(EditorList, Environ("username")) > 0 Then
    
        Set rng = Sheets("Sheet1").Range("A1")
        
        If rng <> LCase("y") Then
            msg = "Cell " & rng.Address & " on " & rng.Worksheet.Name & " must be filled in!"
            Style = vbCritical + vbOKOnly
            Title = "Hey, You Forgot Something!"
            pt = MsgBox(msg, Style, Title)
            Application.Goto rng
            Cancel = True
        End If
        
      End If ' countif
                
End Sub

 

[jaimed77089]

ADVERTISEMENT

I actually wanted the pop up when the workbook opened, can you combine your addition, with the following?

Dim myMsg As String
myMsg = "ACBS Reminder"
myMsg = myMsg & vbCr & "Don’t forget to confirm the Excess Held position in ACBS."
MsgBox myMsg
End Sub

 

[Smitty]

just insert that code in:

Private Sub Workbook_Open()
  '  Code here
End Sub

Smitty

Nice work Nimrod, I like that.

EDIT: You might want to change Nimrod's suggestion a bit so that's it's a stand-alone module that you can call from the Open & Close events, instead of having it twice.

 

[jaimed77089]

ADVERTISEMENT

I'm okay with excel, but far from ya'lls level, so I am going to put it this way. I want a pop up window that will only show if the defined set of users is opening the workbook...can you combine those two strings together?

 

[jaimed77089]

I FIGURED IT OUT!!!!! WAY TO GO ME!!! THANK YOU BOTH FOR YOUR HELP, I'M LOOK LIKE A FREAKING GENIUS NOW....

 

[Nimrod]

Hello Smitty .. thx :wink:

Glad you got it working jaimed.