Jun 25 2010

microsoft office 2007-Modifying the Ribbon – Part 3

Category: Office 2007 professional — admin @ 7:11 pm

Using Excel’s Images on Custom Ribbon Controls

In Part 2, we built the following custom group on a custom tab:

This time, we’ll replace all the happy face images with pictures from Excel’s native ribbon images. Once you know how, it’s actually relatively easy to state which image you want. Ms office 2007 is so Charismatic.

You just replace the HappyFace portion of the imageMso tag with the name of the Excel control that we want to use. The first question that we’ll obviously need to settle, however, is “How do we get the name of the control?”

What you need to do is go into the Excel Options screen, (click the Office button, then Excel Options,) and click Customize. You’ll now see the list of all the items that you can add to your Quick Access Toolbar (QAT). If you’re a developer in prior versions of Excel, you’ll remember that you pretty much had to create a custom toolbar to work out what FaceID corresponded to what picture. The good news here, is that things are much easier.Office 2007 professionaland Office 2007 ultimate are so powerfull.

Move your mouse over one of the menu items and hover it there. A screen tip will pop up, and you’ll find the MSO image name at the end of the string! A picture is shown below of the tool tip.

What I’ve done below is grab a few random imageMso’s and put them into the XML for the example we created at the end of part 2. (Reproduced above.) The group now looks like this:

The images were selected based only on the fact that I thought they looked interesting. That’s all. Remember that these images will only do what you program them too, as shown in Part 1 of this series.Microsoft Office 2007 is powerfull.

The XML code to create this group is shown below. One final word of note here… the imageMso tag is again Case Sensitive! If you mess up the case, you get no image at all on your control. The ribbon does at least get created though, which is better than the case sensitivity issues that I told you about in Part 2.

PLAIN TEXT
XML:

  1. <customUI xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  2. <ribbon startFromScratch=“false”>
  3. <tabs>
  4. <tab id = “customTab” label=“Custom Tab” insertAfterMso=“TabView”>
  5. <group id=“customGroup1″ label=“Custom Group 1″>
  6. <button id=“BigButton1″ label=“I’m #1!” onAction=“CallControl” size=“large” imageMso=“CalculateSheet” />
  7. <button id=“Button1″ label=“Button 1″ onAction=“CallControl” imageMso=“ChartTypeAllInsertDialog” />
  8. <button id=“Button2″ label=“Button 2″ onAction=“CallControl” imageMso=“SheetBackground” />
  9. <button id=“BigButton2″ label=“I’m #2!” onAction=“CallControl” size=“large” imageMso=“HangulHanjaConversion” />
  10. <button id=“Button4″ label=“Special Button 1″ onAction=“CallControl” imageMso=“InkingStart” />
  11. <button id=“Button5″ label=“Special Button 2″ onAction=“CallControl” imageMso=“GetExternalDataFromAccess” />
  12. <button id=“Button6″ label=“Special Button 3″ onAction=“CallControl” imageMso=“OutlineSettings” />
  13. </group>
  14. </tab>
  15. </tabs>
  16. </ribbon>
  17. </customUI>

IS Windows 7 Professional better than Windows 7 Home Premium ?

Jun 25 2010

Microsoft office 2007-Modifying the Ribbon – Part 4

Category: NEWS — admin @ 7:09 pm

Using Your Own Picture on a Button

Well… maybe not your own picture… unless you’re feeling vain today. I really meant using an image of your choice. Photoshop CS4 is so magic!

This is actually very easy, provided that you use the CustomUI Editor tool from OpenXML Developer that I blogged about in Part 1 of this series. Here’s what you need to do…

First, open up your file in the CustomUI editor tool. Click the picture button (Insert Icons), browse to your picture and click OK. You’ll now see your picture on the right side of the CustomUI editor, just like in the screen capture below:

If the image does not show up there, then you’ll need to try again, as the pane does not show unless an image is attached.Acrobat 9 is so useful!

Now, if you end up with a really long and weird looking file name, don’t despair. You can rename the image to something more usable simply by right clicking it and choosing Change ID. Since the CustomUI editor replaces spaces in file names with the string “_x0020_”, you may want to take advantage of this.

The next step is to edit your XML to refer to the custom picture. If you are using the “imageMso=” tags in your XML, change that to simply read “image=”. So for the example above, you would enter image=”logo”. Again, remember that this will be case sensitive.Dreamweaver CS4 is very easy-to-use!

For the following example, I downloaded my website logo, imported it my file via the CustomUI editor, and used the following XML to create my menu:

PLAIN TEXT
XML:

  1. <customUI xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  2. <ribbon startFromScratch=“false”>
  3. <tabs>
  4. <tab id = “xlgTab” label=“XLG” insertAfterMso=“TabView”>
  5. <group id=“Excelguru” label=“ExcelGuru Tools”>
  6. <button id=“Logo” label=“ExcelGuru.ca” onAction=“CallControl” size=“large” image=“logo” />
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

 

The result of this markup gave me a custom tab named “XLG”, with the following on it:

A quick notes on custom images…

You can import more than one custom image into a file, but there seems to be a size limit to how many images you can store. I tried importing a 928KB image in addition to the 57KB logo file. Ms office 2007 is so Charismatic.The result was that neither button showed and image at all. I did import 3 images into a group, but again, I added a large image file and all the button images disappeared. Despite the fact that images are automatically scaled, I would suggest that full pictures are probably not the most appropriate images (even my website logo is too big.)

Jun 25 2010

microsoft office 2007-Modifying the Ribbon – Part 5

Category: Office 2007 Ultimate — admin @ 7:07 pm

Creating a CheckBox

Until now, all the posts have focussed on creating buttons in a variety of styles, shapes and sizes. Today, we’ll take a look at a simple example of a checkbox. What we’ll do is create a single checkbox on a custom tab.Ms office 2007 is so Charismatic.

The XML to create a checkbox is once again similar to what we’ve seen. One big difference is that we cannot use a picture, of course, since the actual check box will take it’s place. The XML for our test is as follows:

PLAIN TEXT
XML:
  1. <customUI xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  2. <ribbon startFromScratch=“false”>
  3. <tabs>
  4. <tab id = “xlgTab” label=“XLG” insertAfterMso=“TabView”>
  5. <group id=“TestTab” label=“My Testing Tab”>
  6. <checkBox id=“chkbox1″ label=“MyCheckbox” onAction=“CallControl”/>
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

 

Office 2007 professionaland Office 2007 ultimate are so powerfull. This will create a custom tab called “XLG”, and on it will be the lonely control as shown below:

Now, the checkbox is there, but we need to be able to figure out if it is clicked. If you recall Part 1 of this series, we set up a routine called “CallControl” (specified in our XML), that started like this:

PLAIN TEXT
Visual Basic:
  1. Private Sub CallControl(Control As IRibbonControl)

 

To determine the value of the checkbox, however, we need a slightly expanded VBA routine. We still use the basic framework as above, but we expand it to also pick up the “Pressed” portion as shown below:

PLAIN TEXT
Visual Basic:
  1. Private Sub CallControl(control As IRibbonControl, pressed As Boolean)
  2. ‘Tell the user the state of the control when they physically click it.
  3.  
  4. Select Case pressed
  5. Case True
  6. MsgBox “The checkbox is checked!”
  7. Case False
  8. MsgBox “The checkbox is NOT checked!”
  9. End Select
  10. End Sub

 

Again, the code above goes in a standard module. Once you have it there, try clicking the checkbox on the Ribbon, and notice that it will feed back if it is checked or not.

Microsoft Office 2007 is powerfull.

This example is educational only, of course. In a real setting you would replace the message boxes with code that you would wish to execute based on the user’s selection.

In the next part, we will look at how to toggle the values of the checkbox via code, and have it update the Ribbon automatically… stay tuned! IS Windows 7 Professional better than Windows 7 Home Premium ?

Jun 25 2010

Microsoft office 2007-Modifying the Ribbon – Part 6

Category: Microsoft Office 2007 — admin @ 7:06 pm

As mentioned in Part 5 of this series, this post will display how to change the value of a Ribbon control from a VBA procedure. For this example, we will continue to work with the case presented in Part 5, using a checkbox.Ms office 2007 is so Charismatic.

Essential Background
Basically, the application of the RibbonX code works like this:

  • We create our XML markup via the CustomUI editor or manually
  • We open the Excel file
  • The RibbonX code is executed, and the Ribbon item is created

If Macros are enabled, then the following will occur next:

  • Any Workbook_Open macros are executed (providing macros are enabled)
  • Any OnLoad macros (specified in the XML code) are executed (providing macros are enabled).Office 2007 professionaland Office 2007 ultimate are so powerfull.

When a Ribbon item is first presented (the first time your controls are shown), RibbonX will also launch “callbacks” to check what values your controls will have.

To effectively work with RibbonX, one of the first things we need to understand is that RibbonX does not work the same way that VBA collections do. There is no collection of RibbonX items, so we need to store all of our values in our own code. To do this, my preference is to create workbook properties and store my values there. This approach offers the benefit that we can easily refer to our RibbonX items, as well as makes it obvious which workbook we’re dealing withMicrosoft Office 2007 is powerfull.

Modifying the XML Markup

Before we create a custom property to hold our defaults, we might as well get the XML Markup adjustments out of the way. We’re going to add two things to our XML for this example; and OnLoad statement, and a GetPressed statement. The use of these will become apparent later, so for now, change the XML of your file to read:

PLAIN TEXT
XML:

  1. <customUI onLoad=“OnLoad” xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  2. <ribbon startFromScratch=“false”>
  3. <tabs>
  4. <tab id = “xlgTab” label=“XLG” insertAfterMso=“TabView”>
  5. <group id=“TestTab” label=“My Testing Tab”>
  6. <checkBox id=“chkbox1″ label=“MyCheckbox” onAction=“CallControl” getPressed=“GetPressed” />
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

 

Creating the Workbook Properties

We are going to create two new workbook properties for both reading and writing. The first will hold the default state of our checkbox. The second will hold our RibbonUI object so that we can refer to it later. All the following code will go in the ThisWorkbook module of the project:

PLAIN TEXT
Visual Basic:

  1. ‘Private variables to hold state of Ribbon and Ribbon controls
  2. Private bChkBox1 As Boolean
  3. Private pRibbonUI As IRibbonUI
  4.  
  5. Public Property Let chkBox1(b As Boolean)
  6. ‘Set value of chkBox1 property
  7. bChkBox1 = b
  8. End Property
  9.  
  10. Public Property Get chkBox1() As Boolean
  11. ‘Read value of chkBox1 property
  12. chkBox1 = bChkBox1
  13. End Property
  14.  
  15. Public Property Let ribbonUI(iRib As IRibbonUI)
  16. ‘Set RibbonUI to property for later use
  17. Set pRibbonUI = iRib
  18. End Property
  19.  
  20. Public Property Get ribbonUI() As IRibbonUI
  21. ‘Retrieve RibbonUI from property for use
  22. Set ribbonUI = pRibbonUI
  23. End Property

 

Integrating the Checkbox Value Property
In addition to the properties we defined, we’ll need one more piece in the ThisWorkbook module, and that is the code to actually set the default value when we open the workbook. IS Windows 7 Professional better than Windows 7 Home Premium ?

Copy the following procedure below the rest in the ThisWorkbook module:

PLAIN TEXT
Visual Basic:

  1. Private Sub Workbook_Open()
  2. ‘Set the default properties of the ribbon controls
  3. chkBox1 = True
  4. End Sub

 

If you have continued on from Part 5, then you already have the “CallControl” procedure in a standard module. We’ll need to add a line to it to store the toggled value in our custom workbook property, so adjust your code to match the following:

PLAIN TEXT
Visual Basic:

  1. Private Sub CallControl(control As IRibbonControl, pressed As Boolean)
  2. ‘Tell the user the state of the control when they physically click it.
  3. ThisWorkbook.chkBox1 = pressed
  4. Select Case pressed
  5. Case True
  6. MsgBox “The checkbox is checked!”
  7. Case False
  8. MsgBox “The checkbox is NOT checked!”
  9. End Select
  10. End Sub

 

Integrating the RibbonUI Property
Now, as I covered in the introduction, when we open the workbook, our Ribbon items will be created, and the Workbook_Open routine will fire, setting the value of the checkbox to True. How do we get the checkbox to actually return this value, though? Photoshop CS4 is so magic!

This is where the “OnLoad” procedure that we specified in the XML comes in. It forms the stepping stone to being able to do this. If you recall, I stated that there was no Ribbon collection and we’d have to hold our own objects if we wanted to use them. This is the exact purpose of the OnLoad procedure.

Copy the following code into the Standard module which holds the “CallControl” procedure:

PLAIN TEXT
Visual Basic:

  1. Private Sub OnLoad(ribbon As IRibbonUI)
  2. ‘Set the RibbonUI to a workbook property for later use
  3. ThisWorkbook.ribbonUI = ribbon
  4. End Sub

 

What this code does is feed the RibbonUI object into our workbook property, allowing us to “hold onto” it for later use.  We know have a way to access our Ribbon object!

“Great!”, you say, “But how?”

Before we go there, we want to do just one more thing.  We are going to set up one more routine to feed information back to the RibbonUI. Acrobat 9 is so useful! Remember that we added a “GetPressed” specification to the XML?  This also needs to call a procedure, (again in the standard module,) which looks like this:

PLAIN TEXT
Visual Basic:

  1. Private Sub GetPressed(ByVal control As IRibbonControl, ByRef returnVal)
  2. ‘Query the property for the value of the chkbox, and feed it back
  3. ‘to the ribbon so that it can be set appropriately
  4. If control.ID = “chkbox1″ Then returnVal = ThisWorkbook.chkBox1
  5. End Sub

 

The purpose of this routine is to feed the checkbox value back to the RibbonUI so that it can be updated to reflect the value we have on hand for it.  It can be initiated in a couple of different ways.  The first time the control is presented, this routine will be called to return the default value.  Also, we can invalidate the XML, which will trigger this routine as the Ribbon is “rebuilt”.  The first happens naturally after the Ribbon is loaded, but it’s the second that we’re interested in, as it gives us the ability to rebuild the Ribbon control when we want to change the value.Dreamweaver CS4 is very easy-to-use!

Updating the Ribbon Control’s Value via VBA

And here it is, at long last.  A very simple routine to do exactly that.  We’re going to pretend that we have a lot more code, and a real reason to do this.    At some point, we decide that we need to toggle the value of the checkbox and make it reflect in the menu.  This routine works by toggling the checkbox property and invalidating the XML for the menu item, which forces the “GetPressed” method to be called and rebuild the menu:

PLAIN TEXT
Visual Basic:

  1. Sub ToggleChkBox1()
  2. ‘Toggle the value of the checkbox via code, and have it trigger
  3. ‘a ribbon update to reflect the new value
  4.  
  5. ‘Check value of checkbox and flip it
  6. If ThisWorkbook.chkBox1 = True Then
  7. ThisWorkbook.chkBox1 = False
  8. Else
  9. ThisWorkbook.chkBox1 = True
  10. End If
  11.  
  12. ‘Invalidate the control, forcing it to reload
  13. ThisWorkbook.ribbonUI.InvalidateControl “chkbox1″
  14. End Sub

 

Final Notes

The “CallControl” routine here is only to demonstrate the use of the property for toggling the value of the checkbox property when manually clicked.  The “ToggleChkBox1″ routine demonstrates the ability to change the Ribbon display via VBA code alone.

If you are curious about the order that things fire, either step through the code, or put a “Stop” command at the beginning of the following routines:

  • Workbook_Open
  • OnLoad
  • GetPressed

That should give you a fairly good feel for the order things go in.

Jun 25 2010

Microsoft office 2007-Modifying the Ribbon – Part 10

Category: NEWS — admin @ 7:03 pm

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.Ms office 2007 is so Charismatic.

The purpose of this post is to demonstrate how to use the Ribbon to interact with our application. Specifically, this example has an edit box which will update with the worksheet name every time you change worksheets. In addition, if you change the name in the edit button, then press the “commit” button, it will change the name of the worksheet to your new selection. (Providing the new name is allowed, of course.).Office 2007 professionaland Office 2007 ultimate are so powerfull.

XML Markup Required

Our XML for this example uses the onLoad assignment to capture our ribbonUI object, allowing us to force a rebuild of our ribbon items when needed, as well as the onAction, onChange and getText callbacks.

PLAIN TEXT
XML:

  1. <customUI onLoad=“OnLoad” xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  2. <ribbon startFromScratch=“false”>
  3. <tabs>
  4. <tab id = “xlgTab” label=“XLG” insertAfterMso=“TabView”>
  5. <group id=“TestTab” label=“My Testing Tab”>
  6. <editBox id=“editBox1″ label=“New Sheet Name:” onChange=“rbnEditBox1_Change” getText=“rbnGetText” />
  7. <button id=“button1″ label=“Commit Name” onAction=“rbnButton_Click” imageMso=“HappyFace” />
  8. </group>
  9. </tab>
  10. </tabs>
  11. </ribbon>
  12. </customUI>

 

VBA Code Required – ThisWorkbook Module

Again, we have the need to create workbook properties to set and retrieve the values of our ribbon controls. This time, we need to store the name in the editBox, which can be applied as a worksheet name, as well as the RibbonUI object. Microsoft Office 2007 is powerfull.The editBox property is first used immediately upon opening the workbook, when we assign the active worksheet’s name to it. It is then subsequently updated every time a new sheet is activated in the workbook. The code required in the ThisWorkbook module to accomplish this is shown below:

PLAIN TEXT
Visual Basic:

  1. Option Explicit
  2.  
  3. ‘Private variables to hold state of Ribbon and Ribbon controls
  4. Private pRibbonUI As IRibbonUI
  5. Private sEditBox1Text As String
  6.  
  7. Public Property Let EditBox1Text(s As String)
  8. ‘Store the button name
  9. sEditBox1Text = s
  10. End Property
  11.  
  12. Public Property Get EditBox1Text() As String
  13. ‘Retrieve the button name
  14. EditBox1Text = sEditBox1Text
  15. End Property
  16.  
  17. Public Property Let ribbonUI(iRib As IRibbonUI)
  18. ‘Set RibbonUI to property for later use
  19. Set pRibbonUI = iRib
  20. End Property
  21.  
  22. Public Property Get ribbonUI() As IRibbonUI
  23. ‘Retrieve RibbonUI from property for use
  24. Set ribbonUI = pRibbonUI
  25. End Property
  26.  
  27. Private Sub Workbook_Open()
  28. ‘Store the name of the active worksheet
  29. ThisWorkbook.EditBox1Text = ActiveSheet.Name
  30. End Sub
  31.  
  32. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  33. ‘Set the name of the worksheet to the text property
  34. With ThisWorkbook
  35. .EditBox1Text = ActiveSheet.Name
  36. .ribbonUI.InvalidateControl editBox1Name
  37. End With
  38. End Sub

 

VBA Code Required – Standard Module

The following code contains all the callbacks necessary to:

  • Store the ribbonUI object to the Workbook property (via the onLoad routine).IS Windows 7 Professional better than Windows 7 Home Premium ?
  • Return the text for the editBox control when the RibbonX code requires it (rbnGetText routine)
  • React to manual changes of the text values in the EditBox control (via the rbnEditBox1_Change routine)
  • Respond to the button click requesting a worksheet name change (via the rbnButton_Click routine)
PLAIN TEXT
Visual Basic:

  1. Option Explicit
  2.  
  3. Const Btn1Name = “button1″
  4. Public Const editBox1Name = “editBox1″
  5.  
  6. Private Sub OnLoad(ribbon As IRibbonUI)
  7. ‘Set the RibbonUI to a workbook property for later use
  8. ThisWorkbook.ribbonUI = ribbon
  9. End Sub
  10.  
  11. Sub rbnGetText(control As IRibbonControl, ByRef returnedVal)
  12. ‘Get the text value for the editBox
  13. If control.ID = editBox1Name Then returnedVal = ThisWorkbook.EditBox1Text
  14. End Sub
  15.  
  16. Private Sub rbnEditBox1_Change(control As IRibbonControl, text As String)
  17. ‘Store the text value of the editBox for later use.
  18. If control.ID = editBox1Name Then
  19. ThisWorkbook.EditBox1Text = text
  20. End If
  21. End Sub
  22.  
  23. Private Sub rbnButton_Click(control As IRibbonControl)
  24. ‘Rename the worksheet
  25. Dim sNewSheetName As String
  26. Dim ws As Worksheet
  27.  
  28. If control.ID = Btn1Name Then
  29. ‘Retrieve intended sheet name
  30. sNewSheetName = ThisWorkbook.EditBox1Text
  31.  
  32. ‘Check if name is nothing
  33. If Len(sNewSheetName) = 0 Then
  34. MsgBox “I need a name, please.”, _
  35. vbOKOnly + vbCritical, “No name entered”
  36. Exit Sub
  37. End If
  38.  
  39. ‘Check for sheet name in use
  40. For Each ws In ThisWorkbook.Worksheets
  41. If ws.Name = sNewSheetName Then
  42. MsgBox “Sheet name already used.” & vbNewLine & _
  43. “Please pick another”, vbOKOnly + vbCritical, _
  44. “Name in use!”
  45. Exit Sub
  46. End If
  47. Next ws
  48.  
  49. ‘Would not be here if name not okay so set it
  50. ActiveSheet.Name = sNewSheetName
  51. End If
  52. End Sub

 

End Result

Once you’ve saved all the code, close and re-open the workbook. You’ll have a custom XLG tab (that you’ve become used to if you’re following the series.) On this tab you’ll find a group that looks like this:

The code fires in this order when the workbook is opened up (assuming Macros are enabled):

  • The RibbonX code is loaded, and the XLG tab is added
  • The Workbook_Open event is fired, setting the EditBox1Text property to the name of the active sheet in the workbook.Photoshop CS4 is so magic!
  • The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the “GetText” callback for the editBox is executed. This queries the property we just set, and adjusts the text in the editBox to the name of the active worksheet (which we just stored in the EditBox1Text property, as explained above. Because the button’s properties are set in the XML, no additional callbacks are required to build the button.Acrobat 9 is so useful!

Our Ribbon is now fully loaded, the editbox has a name in it, and the button is ready to be clicked. First though, try navigating to other sheets in the workbook. Every time you activate another worksheet in the workbook, you will trigger the following chain of events:

  • The Workbook_SheetActivate routine is fired as the new worksheet is selected
  • The name of the new sheet is sent to the EditBox1Text property and stored for later use (in the “sEditBox1Text” private variable)
  • The editBox is invalidated, forcing the RibbonX code to be rebuilt
  • During the rebuilding, the rbnGetText routine is called, which subsequently retrieves the worksheet name from the EditBox1Text property and builds it into the refreshed ribbon control

The only thing that looks any different now is that the sheet name will have changed in the edit box. You can click back and forth between sheets to watch it change, although that will probably get stale rather quickly.
So now, try typing something in the editbox and clicking the “Commit Name” button. If you check the active worksheet’s tab, you’ll see that the name has been updated. This was accomplished by the following actions, triggered when you clicked the button:

  • As control passes from the editBox field to the button, the rbnEditBox1_Change routine is fired, which sends the value of the editBox to the EditBox1Text property for later use
  • Once the above routine is complete, the rbnButton_Click routine is fired.Dreamweaver CS4 is very easy-to-use!
  • Control id is checked to make sure that we are reacting to the correct button. (Not really needed here, but would be if we added another button which used the same onAction routine.)
  • The editBox’s value is assigned to a string for checking
  • The string is evaluated to make sure it is not blank. If it is, we tell the user and exit the routine without changing the name
  • The string is checked to make sure that it doesn’t conflict with an existing sheet name. If it is, the user is informed and we exit the routine without making changes
  • If the string passed the above tests, we change the worksheet name.

While I can’t really think of a good reason that you would want to add this functionality to the ribbon, I hope that it does a decent job of explaining the “how” of ribbon control and application interaction.

Jun 23 2010

Brutal Date Format

Category: Office 2007 professional — admin @ 2:05 am

We recently updated our main property management system and are now going through the first month since. Over and over again now I’m running up against an issue in the way the vendor decided to start treating dates. In the past they just used “MMM DD’, which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good! QuickBooks 2010 is so Helpful!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:

MAY9/09

MAY10/09

MAY11/09

 

Unfortunately this does NOT translate well into a date format automatically. You’d think that it would convert when you pulled it through the text import wizard, but it doesn’t. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. Photoshop CS4 is so magic!As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can’t be used to drive date dependant formulas, and sort like this:

MAY1/09

MAY10/09

MAY11/09

MAY2/09

MAY20/09

 

This is very irritating, and many users would tell you that this isn’t trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&” “&MID(A9,4,FIND(“/”,A9,1)-4)&”, “&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a “MMM DD, YYYY” format, which it can interpret. Here’s the breakdown:

  • Month: LEFT(A9,3) &” “ returns the left 3 characters of the text string followed by a single space
  • Day: MID(A9,4,FIND(“/”,A9,1)-4)&”, “ returns the string in the middle starting with the 4th character, returning the number of characters between the slash and the 3rd character, plus a comma and a space.Acrobat 9 is so useful!
  • Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we’re not using this spreadsheet in 2100… I can safely say that it won’t be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.Dreamweaver CS4 is very easy-to-use!

Jun 23 2010

Professional Excel Development – 2nd Edition

Category: NEWS — admin @ 2:02 am

My wife sent me a message today to let me know that a review copy of Professional Excel Development – 2nd Edition had just arrived at my door.Microsoft Office 2007 is powerfull.The last version of the book was excellent: the only book on the market of its type, in fact. I’m expecting nothing less from this one, indeed I’m actually expecting more. Dennis Wallentin (XL-Dennis) was added to the team this time to bring in the .NET aspect, and I’m really looking forward to exploring those chapters. Hopefully I’ll actually be able to make a .NET add-in that actually works now!

I haven’t had a chance to dig into it yet, but one thing I’ll say about the book right now is that it should be able to hold your house down if a tornado hits it. This is one big book! IS Windows 7 Professional better than Windows 7 Home Premium ?

I should also mention that Dick Kusleika has acquired an extra copy to give away. You can find his competition here.

Jun 23 2010

Breaking text to two lines

Category: Microsoft Office 2007 — admin @ 2:01 am

I was working on my budget package today, and decided that I wanted to break the text so that it showed on two lines. For many novice users this means that they put one or two words in one cell, then the remaining text in the cell below. Personally I try to avoid that, as I find it unnecessary. Acrobat 9 is so useful!

So here’s what my output might look like:

I sized the column for illustration only, and would normally have it much narrower.

It’s true that you could accomplish this by checking the Wrap Text box (Format CellsàAlignment), but that only works if the column is at the right width to have the wrap where you want it. That’s not always good enough for me when I’m sizing my columns to hold the required numbers, and my headers might look all messy. By controlling where the text breaks, I can get it to look exactly how I want.Dreamweaver CS4 is very easy-to-use!

So how did I do this? After all, when you press Enter the data is committed to the cell and you move down a row.

The answer is to press ALT+ENTER together. The text will then break exactly where your cursor is.

Now that’s all good, but what about when you are filling the cell contents with a formula? Let’s look at the following example:

Have a look at the contents of C1. In this case it is actually driven by the following formula:

=YEAR(B5) & ” Budget vs ” & YEAR(B4) & ” Projected Actuals”

Now this is all good, but I’d like to force the 2009 to be on the second line. I can’t go in and type an ALT+ENTER in this case. Actually, I can but all it does is split my formula to show on two lines in the formula bar like this:

While that’s really helpful for auditing formulas, it doesn’t achieve the desired effect. (I believe that the option to expand the formula bar was added in Excel 2007 – just left click and drag down when your mouse turns into the up and down arrows on its bottom border – but in previous versions you’d have to edit the cell and use your arrow keys to see the different lines.).Ms office 2007 is so Charismatic.

The secret to breaking the text in a formula is to use the CHAR function to enter a non-printing character; a hard return. In this case CHAR(10) will do the job quite nicely. So I’d edit the formula to read as follows:

=YEAR(B5) & ” Budget vs ” & CHAR(10) & YEAR(B4) &” Projected Actuals”

And it would get me the following result:

Just a quick note as well… I use the & sign to join text instead of the horrible CONCATENATE function. It’s not required to have spaces each side of the & character, but it doesn’t hurt either. I normally wouldn’t put them in, but I did it to make the formula a bit more readable.Office 2007 professionaland Office 2007 ultimate are so powerfull.

And for the record, this should work as far back as at least Excel 97, if not further.

Jun 23 2010

Thanks a lot, macro recorder!

Category: Microsoft Office 2007 — admin @ 2:00 am

I think it’s pretty well known that the Excel macro doesn’t always record code when you’d like it to.  I know that there’s been several times I’ve recorded something to get a syntax, and it’s been an empty stub after I’m done.  This one was a new one to me though…QuickBooks 2010 is so Helpful!

I tried to record the creation of a conditional format in 2007 and here’s what I got:

PLAIN TEXT
Visual Basic:

  1. Sub Macro4()
  2. ‘
  3. ‘ Macro4 Macro
  4. ‘
  5.  
  6. ‘
  7. Selection.FormatConditions.Add Type := xlExpression, Formula1 :=
  8. Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  9. With Selection.FormatConditions(1).Font
  10. .Bold = True
  11. .Italic = False
  12. .ThemeColor = xlThemeColorDark1
  13. .TintAndShade = 0
  14. End With
  15. With Selection.FormatConditions(1).Interior
  16. .PatternColorIndex = xlAutomatic
  17. .Color = 255
  18. .TintAndShade = 0
  19. End With
  20. Selection.FormatConditions(1).StopIfTrue = False
  21. End Sub

 

The first line was even highlighted red. Seriously, it could get the rest, but not the formula? Photoshop CS4 is so magic!

Jun 23 2010

VBA IDE vs VS2008 IDE

Category: Microsoft Office 2007 — admin @ 1:59 am

On Friday night, I began reading my way through PED 2nd Edition. After something weird happened with Excel, (I think because I have Excel 2003 and 2007 installed,) I got the bright idea to create a very simple Windows Forms application. I figured that I could make a really easy math game for my daughter. (I’ll see about posting it over the next few days.).Ms office 2007 is so Charismatic.

So cool thing here… with very minimal VB.NET experience, I was able to knock up a fully working simple math game in about 3 hours that will install on Windows XP and Windows Vista. I was pretty damn impressed.

At any rate, here I am today, back working in Excel. And I can now say that I’m rather disgruntled with the state of the IDE for Excel VBA.Office 2007 professionaland Office 2007 ultimate are so powerfull.

Despite gnashing my teeth on certain stuff in VB.NET, the IDE in VS2008 is awesome. Maybe it’s for the big things, and I’m sure that there will be VBA IDE things I’ll miss, but it’s the little things that made my day. Little things like this:

  • You can collapse procedures/function and even entire regions of code. That seriously rocks! Microsoft Office 2007 is powerfull.
  • You type in With “whatever”, and it automatically puts in the End With. Actually, all constructs are like this… For Each, Select Case, Try/Catch and so on. Very, very cool!
  • The indenting everywhere is automatic.
  • The intellisense always suggests available items. I find this somewhat hit and miss in Excel’s IDE for some reason.

I really wish that they add the features above into our tired old IDE. It would make things so much faster for typing up code from scratch! IS Windows 7 Professional better than Windows 7 Home Premium ?

« Previous Page — Next Page »