Dynamic Ribbon Control & Custom Ui Resources |
Dynamic Ribbon Control & Custom Ui Resources - Excel |
|
I have created many traditional commandbar menus in version 2003 through 2007. I still have a long way to go to learn how to interact with the controls on the ribbon, but I finally think I have the basics down.
I want to share the code and resources with all and I hope others could post to this with their resources that helped them.
The following code is an example of a custom UI ribbonX for excel. It shows a new tab, two groups and a few controls. The main focus is a dropdown box that gets dynamically loaded with the names of visible sheets in the workbook. When a user selects one of the names in the dropdown, it activates that sheet. I added VBA code to have the dropdown change the name shown when a user selects a visible sheet using the tabs.
I used two tools to complete the task: The CustomUIEditor from microsoft which I downloaded from here. Do not try to edit a customUI using CustomUIEditor while you have the workbook loaded into excel; if you do, you may lose your work. This is a simple tool for viewing the xml files and creating the callback routines needed by excel. Microsoft needs to put more work into this. I would rather see an editor within the Vsiual Basic editor in Excel.
Andy Pope's RibbonX Visual Designer which can be found here. This is a good tool that is run inside of Excel. It has a preview tool and it shows the built-in icons from MS office. You need to have the workbook unloaded before opening your project.
I just learned over the last week that MS Office 2007 files are nothing more than compressed files containing xml sheets. You can rename a *.xlsm file to a *.zip file and look inside. When you add a custom ribbon to an Excel workbook, it creates a folder in the compressed file called "customUI" and saves a file in that folder called "customUI.xml". My current project is about 40mb large as a .xlsm file. When I unzip it, it becomes over 134mb large. Now I understand why Excel2007 takes so long to load workbooks!
For some reason there are problems showing the xml code in the code window. I will try attach the xml code soon.
Here is the code only related to the dropdown control. Put this into a standard module.
Code:
Option Explicit ' Public TSNavItems As Integer 'Number of visible sheets (index zero) Public TSNavArray() As String 'Store names of visible sheets Public TSNavSelected As String 'Store selected sheet from control Public TSRibbon As IRibbonUI 'Time Series Custom Ribbon Public TSWorkbook As Workbook 'Time Series Workbook Public TrackTSNav As Integer 'Store the current activated sheet index ' 'This counts and loads the visible sheets and chart sheets into the navigation dropdown Private Sub LoadVisibleNavigate() ' Dim shtCount As Long, wksSheet As Worksheet, ChtSheet As Chart TrackTSNav = 0 'Count all visible worksheets shtCount = 0 For Each wksSheet In TSWorkbook.Worksheets If wksSheet.Visible = xlSheetVisible Then shtCount = shtCount + 1 End If Next wksSheet 'Add the visible Chart Sheets For Each ChtSheet In TSWorkbook.Charts If ChtSheet.Visible = xlSheetVisible Then shtCount = shtCount + 1 End If Next ChtSheet 'Load all visible worksheets into the Navigation Dropdown array for ribbon TSNavItems = shtCount 'index one ReDim TSNavArray(TSNavItems - 1) 'index zero shtCount = -1 For Each wksSheet In TSWorkbook.Worksheets If wksSheet.Visible = xlSheetVisible Then shtCount = shtCount + 1 TSNavArray(shtCount) = wksSheet.Name 'Set the default index for the dropdown to the current activated sheet If wksSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount End If Next wksSheet 'Load the visible Chart Sheets in the dropdown array For Each ChtSheet In TSWorkbook.Charts If ChtSheet.Visible = xlSheetVisible Then shtCount = shtCount + 1 TSNavArray(shtCount) = ChtSheet.Name 'Set the default index for the dropdown to the current activated sheet If ChtSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount End If Next ChtSheet ' End Sub ' 'This gets called when the workbook loads Private Sub myRibbon_******(ribbon As IRibbonUI) Set TSRibbon = ribbon 'My ribbon Call LoadVisibleNavigate 'Load the Navigation dropdown End Sub ' 'This gets called for every item in the dropdown 'This callback gets called during startup and can be called by using 'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate") Public Sub drp_Navigate_getItemCount(control As IRibbonControl, ByRef returnedVal) ' 'The total number of items in the dropdown box (index one based) returnedVal = TSNavItems ' End Sub ' 'This callback gets called during startup automatically. It can be called by using 'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate") Public Sub drp_Navigate_getItemID(control As IRibbonControl, index As Integer, ByRef id) ' 'This is an internal id used by xl. It must be unique id = "TSNavSheets" & index ' End Sub 'This callback gets called during startup automatically. It can be called by using 'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate") Public Sub drp_Navigate_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal) ' 'returns the name of the sheet returnedVal = TSNavArray(index) ' End Sub ' 'This callback gets called during startup automatically. It can be called by using 'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate") Public Sub drp_Navigate_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal) ' 'TrackTSNav is the index of the current selected sheet returnedVal = TrackTSNav ' End Sub ' 'This callback only fires when a user changes the dropdown Public Sub TS_Navigate_OnAction(control As IRibbonControl, id As String, index As Integer) ' TSWorkbook.Activate 'My workbook Sheets(TSNavArray(index)).Activate 'Activate the sheet selected in the dropdown ' End Sub
Here is the code that needs to be put into the "ThisWorkbook" module.
Code:
Private Sub Workbook_Open() xlVersion = Val(Mid(Application.Version, 1, InStr(Application.Version, ".") - 1)) 'Version 12 = Excel2007 'Version 11 = Excel2003 'Version 10 = Excel2000 'Used to decide if the old commandbar menu is loaded Set TSWorkbook = ThisWorkbook 'My Workbook End Sub ' 'The Navigation dropdown control changes when a user selects a new sheet Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim X As Long 'Find the index value for the active sheet in the dropdown array For X = 0 To TSNavItems - 1 If TSNavArray(X) = ActiveSheet.Name Then TrackTSNav = X End If Next X TSRibbon.InvalidateControl ("drp_Navigate") 'Reset the dropdown control End Sub
Similar Topics
I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:
Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s
Sheet 2
Cell C2 has code 1
Cell E2 needs code 2
I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:
=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)
But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.
I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.
I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.
I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:
Sheets("WELCOME").Select Range("A1:N18").Select ActiveWindow.Zoom = True
By repeating this code for each worksheet, I can make each one be zoomed just right.
However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.
I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.
Anyone know how to do this ?
How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?
Example User's path: 'C:\Documents and Settings\jfarc\Desktop'
Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.
Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.
I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path
I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.
On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.
For example Sheet 7 (called total) would have
Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).
I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.
Thanks in advance for any advice.
Edit: I am using Excel 2007 w/ windows XP
I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.
Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.
Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.
I'll post up further comments as I am trying to work my way through it!
Thanks,
Jag
I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.
In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.
I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.
Thanks for your suggestions.
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe
I am using below code to Select the Visible rows in the target range:
Code:
Range("A:p").SpecialCells(xlCellTypeVisible).Select
Problems in this code a
1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.
2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.
E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6
3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.
Some one please revert with the solution.
Thanks in advance.
I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.
What I want is for my code to execute as soon as the data connection refresh has finished.
When I use the statement
Code:
ActiveWorkbook.RefreshAll Application.Run "Project1.xlsm!ConvertDates"
The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.
Is there some way I can tell excel to wait till the refresh has finished and then execute the code?
Any help would be hugely appreciated.
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!
I need the necessity do delete a sequence of sheets in my workbook.
If I use this code:
With ActiveWorkbook
If .Worksheets.Count >= 5 Then
For n = 5 To .Worksheets.Count
Worksheets(n).Delete
Next n
End If
End With
I receive a confirmation message box with this message:
"Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete" [DELETE] [CANCEL]
I wish to delete all sheets without receiving any message.
Is it possible?
Many thanks in advance for your kind support.
Regards,
Giovanni
My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer. I then copied the MSCOMCT2.* files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c:\windows\system32\ ).
No luck. I then rebooted the machine - still no luck.
Then, finally I unregistered the old DLL via
Code:
regsvr32 /u c:\windows\system32\MSCOMCT2.OCX
(not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via
Code:
regsvr32 c:\windows\system32\MSCOMCT2.OCX
and what do you know - it worked.
Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files.
Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.
Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).
There will be a varying number of rows in each of the individual sheets.
If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
If anyone can help it would really cut down the time I spend collating these stats every day!
I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :
Code:
Private Sub CommandButton2_Click() 'REMOVE SELECTION Dim I As Long With ListBox1 For I = .ListCount - 1 To 0 Step -1 If .Selected(I) Then .RemoveItem I Sheets("URL List").Rows(I + 2).EntireRow.Delete End If Next I End With End Sub
Thanks a lot for your help in advance.
I have just tried running the following code
Code:
Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets With ws If .FilterMode Then .ShowAllData End With Next ws
in the hope that it might 'unfilter' my table but it literally doesn't do a thing!
My table is called Activity_Table
Can you provide me with something that will 'unfilter' my table if it is filtered on any or all 6 columns?
Help appreciated,
Dominic
I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?
I tried
http://www.mrexcel.com/forum/showthread.php?t=403989
but it does not work. Probably because column Q is filtered to show only blank cells,
Can somebody help me on this?
Thanks,
awagdarikar
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
i've got the following problem:
I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.
When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.
How can I prevent this message from popping up?
I've already tried
Code:
application.displaywarnings = false
but that didn't work
Thanks
We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.
So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.
The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.
I ran a search on the forums and found something similar, but not quite what I was looking for.
Any help?