Pop Up Window in Excel

  • Thread starter Thread starter sklaer.nephal
  • Start date Start date
S

sklaer.nephal

I am currently working on a project that would require a pop up window
for a particular cell. Of course this would be done as a macros. The
pop up would be required to occure whenever a particular cell is
selected, wether by clicking it or tabing to it. The Pop Up would
then have a condtion, such as "Yes" and "No", which the auto select
would be pointed to "No".

If the "yes" condition is selected, then the person can continue
working on the sheet. If the "No" condition is chosen, then the
worksheet goes into protection, where it can only be unlocked once the
cell is selected again and the "Yes" condition is chosen.

I am patricularly adept in creating cell formulas, but very weak when
it comes to macros. Can someone pls help me?

Tks
 
Use code something like the following in the code module of the worksheet:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Res As VbMsgBoxResult
If Target.Address = Range("C3").Address Then
Res = MsgBox("Continue?", vbYesNo + vbDefaultButton1)
If Res = vbNo Then
Me.Protect
Else
Me.Unprotect
End If
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
You can do this in Data Validation. Go to your menu to Data then
Validation, Settings and set your criteria. Then click on Input Message and
check the Show input message box. Then Type what you want your pop-up to
say. Then Click on
the Eerror Alert tab and select what kind of Alert you want if it does not
meet your criteria. STOP,WARNING or INFORMATION. They will get an option you
set for them to try agin or cancel.
This message will appear telling them what date is accepted and what is not.
If they do not meet the criteria it will not allow them to go any further
but a pop up will tell them.
I hope this helps.
Sho
 
One more thing on this. If your office assistant is on the pop-up will
appear with him. If it is shut of the pop up will appear in the cell as the
go to type in it. The warning pop-up shows on the screen if the criteria is
wrong. You do not need a Macro to do this.
Sho
 
To OP

You will need event code to protect the worksheet and unprotect it based on the
"yes" or "no" answer.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$2" Then
Dim Msg, Style, Response
Style = vbYesNo + vbDefaultButton2
Msg = "Yes or No"
Response = MsgBox(Msg, Style)
If Response = vbNo Then
ActiveSheet.Protect Password:="justme"
Else
ActiveSheet.Unprotect Password:="justme"
End If
End If
Cancel = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

NOTE: A2 must first be unlocked at Format>Cells>Protection

When user double-clicks the cell the msgbox will pop up with No as default.


Gord Dibben MS Excel MVP
 
If you would rather have the pop-up occur when selecting or tabbing to the cell,
change the event type.

Replace the double-click event with this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Gord
 
Back
Top