Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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) wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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 " wrote: 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 occur 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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 " wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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 FormatCellsProtection When user double-clicks the cell the msgbox will pop up with No as default. Gord Dibben MS Excel MVP On Fri, 16 Feb 2007 10:23:41 -0800, ShoDan wrote: 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 " wrote: 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pop Up Window in Excel
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 On Fri, 16 Feb 2007 12:27:22 -0800, Gord Dibben <gorddibbATshawDOTca wrote: 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 FormatCellsProtection When user double-clicks the cell the msgbox will pop up with No as default. Gord Dibben MS Excel MVP On Fri, 16 Feb 2007 10:23:41 -0800, ShoDan wrote: 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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Window | Excel Worksheet Functions | |||
Window Inside Window. | Excel Discussion (Misc queries) | |||
The window opens in a smaller window not full sized window. | Excel Discussion (Misc queries) | |||
In Excel - how to delete new window created from window on tool ba | Excel Worksheet Functions | |||
How do I undo the Excel, Window Menu, New Window command | Excel Discussion (Misc queries) |