Microsoft Office Ultimate 2007 Creating a Custom P

[xianghai06387]

author is Derek Mang,Microsoft Office Professional Plus 2007 Serial, owner of Systems Solution Developers Inc. You can find more useful tips from Derek on his website: www.officevbasolutions.com. post shows you how to create a custom popup menu in Excel. has lots of popup menus that are made available to the user with a right-click depending on what your doing - hence the term context menu. Right-click on a cell and you get the Excel ,Microsoft Office 2007 Standard Serial Key, allowing you to add items to the menu or disable menu items if desired. your needs, a dropdown menu could get to be awkwardly large. Furthermore, you get the same menu for each cell, and if there are dependencies on cell content you may wind up with too many choices. A completely custom menu based on cell content or range, would better satisfy specific needs. an example of the custom menu on the net and extended this using a worksheet class module to include all worksheets in a workbook rather than just one as found in the original example. code will create and popup with three custom menus (Red, Yellow and Green) on any worksheet in the current workbook where the right-clicked cell is a shade of red, yellow or green respectively. requires code in the Workbook.Open event, as well as one code module, and one class module. key is in the class module. The class module contains handlers for worksheet events that are fired whenever an action occurs on (in this case) any worksheet in the workbook. Of particular interest is the Worksheet.BeforeRightClick event. As the event name suggests, possibilities all based on the cell that has been right-clicked by the user. this sample, the Range.Interior property is used to access the cell's (aka target) Interior.ColorIndex property, and then depending on the value for color returned, the default popup menu is cancelled and corresponding custom popup menu is shown depending on the property value returned. discovering this technique, I have included it - and it has been well-received - in several custom Excel solutions to limit end-user activity to specific tasks . Workbook_Open for the Workbook.Open event handler establishes the three popup menus,Office 2007 Professional Plus Activation, and sets up the worksheet objects in the class. Open the Visual Basic Editor and paste this code into the ThisWorkbook module of an Excel Macro-Enabled Workbook (. xlsm) to define the event handler for the Workbook.Open event. Sub Workbook_Open () Set cb_Red = CreateSubMenu (End Sub Code Module module contains the class set up and the actual menu creation routines. In the Visual Basic Editor, click Module on the Insert menu,Microsoft Office 2010 64 Bit, and then paste this code into the module. Cb_Red As CommandBar
Global cb_Yellow As CommandBar
Global cb_Green As CommandBar
Global WSObj As Collection
Global ws As Worksheet Dim WSo As clsWS Set WSObj = Nothing Set WSObj = New Collection For Each ws In ActiveWorkbook.Worksheets Set WSo = New clsWS Set WSo.WSToMonitor = ws WSObj.Add WSo, ws.Name Next ws End Sub Function CreateSubMenu ( strCB) As CommandBar Const CBPREFIX = collection Set cb = CommandBars.Add (Name: = strCBName, _ Position: = msoBarPopup, _ MenuBar: = False, _ Temporary: = False) 'Add controls Set cbc = cb.Controls.Add With cbc. Caption = strCB & = cb Set cbc = Nothing Set cb = Nothing End Function
Sub DeleteCommandBar (cbName) On Error Resume Next CommandBars (cbName). Delete End Sub
Sub DummyMessage () MsgBox CommandBars.ActionControl.Caption, vbInformation + vbOKOnly, End Sub Class Module module determines which menu to into the module. (Be sure to change the name to clsWS so that it will be available as the class of that name for the SetupAllWSEvents () subroutine.) WithEvents aWS As Worksheet Property Set WSToMonitor (uWS As Worksheet) Set aWS = uWS
End Property Private Sub aWS_BeforeRightClick (ByVal Target As Range, Cancel As Boolean) Select Case Target.Interior.ColorIndex Case 3, 9 cb_Red.ShowPopup Cancel = True 'defeat the standard cell popup menu Case 4, 10, 35, 43,Microsoft Office Ultimate 2007, 50, 51, 52 cb_Green.ShowPopup Cancel = True Case 6, 12, 36, 44 cb_Yellow.ShowPopup Cancel = True Case Else Cancel = False End Select
End Sub demo the code, set the fill color of several cells to shades of red, yellow, and green, close the workbook, and then re-open it to run the Workbook_Open () code that sets up the popup menus. When you right -click the colored cells, the popup menus should look like the following examples. original sample that provided inspiration and insight was authored by Andy Wiggins (Byg Software) <div