Combobox Events in an Add-in

Posted on May 8, 2007December 23, 2020 by Dick Kusleika

I have an application that displays a list of “documents” for a particular product. Most of the documents are pdfs, but some are iso images for burning a CD. The display sheet shows each CD associated with the product, and for each CD the iso, the label that’s stuck to the CD, and the pdfs that should accompany it. The user selects the product, operating system, and version from comboboxes to display the appropriate documents.

Normally, the code for a combobox lives in the containing sheet’s class module (like Sheet1). However, good coding practice dictates that all of the VBA be in the add-in and not in the template. To accomplish this, I made a custom class module to house the three comboboxes and respond to their events. The class is named CSheetEvents and contains these variable declarations:

Private WithEvents mobjProductCombo As ComboBox
Private WithEvents mobjSystemCombo As ComboBox
Private WithEvents mobjVersionCombo As ComboBox

Private WithEvents mobjProductCombo As ComboBox

Private WithEvents mobjSystemCombo As ComboBox

Private WithEvents mobjVersionCombo As ComboBox

The variables are private to the class module and I use Property Get and Property Set procedures to assign them. An example is:

Public Property Get ProductCombo() As ComboBox
 
    Set ProductCombo = mobjProductCombo
 
End Property
 
Public Property Set ProductCombo(objProductCombo As ComboBox)
 
    Set mobjProductCombo = objProductCombo
 
End Property

Public Property Get ProductCombo() As ComboBox

Set ProductCombo = mobjProductCombo

End Property

Public Property Set ProductCombo(objProductCombo As ComboBox)

Set mobjProductCombo = objProductCombo

End Property

When the user selects the appropriate menu item, the code opens the template and assigns the three comboboxes. The relevant portion of that code is:

With Wb.Sheets(1).OLEObjects
     Set gclsSheet.ProductCombo = .Item(“cbxProduct”).Object
     Set gclsSheet.SystemCombo = .Item(“cbxSystem”).Object
     Set gclsSheet.VersionCombo = .Item(“cbxVersion”).Object
End With

With Wb.Sheets(1).OLEObjects

Set gclsSheet.ProductCombo = .Item(“cbxProduct”).Object

Set gclsSheet.SystemCombo = .Item(“cbxSystem”).Object

Set gclsSheet.VersionCombo = .Item(“cbxVersion”).Object

End With

The variable gclsSheet is a global variable that holds an instance of the CSheetEvents so that it doesn’t go out of scope. With the comboboxes properly assigned inside the class, they will now respond to events. The change event of cbxProduct fills cbxSystem. The change event of cbxSystem fills cbxVersion. And the change event of cbxVersion lists the CDs and hyperlinks to all of the documents.

When the user has printed the documents, he closes the sheet. Since I’ve used a template, there is an unsaved document named AllCDConfigurations1 – a workbook created from the template AllCDConfiguration.xlt. I don’t want the user to be faced with a message to save this document, so I also included a workbook object in my class module. By defining a workbook variable with the WithEvents keyword, I can capture the Before_Close event and eliminate that save message.

Private Sub mobjWb_BeforeClose(Cancel As Boolean)
   
    On Error Resume Next
   
    Cancel = True
    Application.EnableEvents = False
        If Not EnableMenu(False) Then Err.Raise glHANDLED_ERROR
        mobjWb.Close False
    Application.EnableEvents = True
    Me.Terminate
   
End Sub

Private Sub mobjWb_BeforeClose(Cancel As Boolean)

On Error Resume Next

Cancel = True

Application.EnableEvents = False

If Not EnableMenu(False) Then Err.Raise glHANDLED_ERROR

mobjWb.Close False

Application.EnableEvents = True

Me.Terminate

End Sub

The Cancel = True part stops the normal close operation and the mobjWb.Close False part closes the workbook without saving changes.

Error Handling Template

Posted on May 4, 2007August 21, 2019 by Dick Kusleika

Since Professional Excel Development was published, I’ve been using the error handling method described in chapter 12. It’s very comprehensive and easy to implement. It’s no guarantee that my code is right, of course, but it does guarantee that the end user won’t be plopped into the VBE when an error occurs.

I also MZ-Tools while I’m coding. One of my favorite features is being able to add module headers, procedure headers, and error handling code easily. Below is what I have on the Error Handling tab under MZ-Tools > Options:

Dim bReturn As Boolean
    
    Const sSOURCE As String = “{PROCEDURE_NAME}()”
   
    On Error GoTo ErrorHandler
    bReturn = True
    {PROCEDURE_BODY}

ErrorExit:
    On Error Resume Next
    {PROCEDURE_NAME} = bReturn
    Exit {PROCEDURE_TYPE}

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

Dim bReturn As Boolean

Const sSOURCE As String = “{PROCEDURE_NAME}()”

On Error GoTo ErrorHandler

bReturn = True

{PROCEDURE_BODY}

ErrorExit:

On Error Resume Next

{PROCEDURE_NAME} = bReturn

Exit {PROCEDURE_TYPE}

ErrorHandler:

bReturn = False

If bCentralErrorHandler(msMODULE, sSOURCE) Then

Stop

Resume

Else

Resume ErrorExit

End If

If you haven’t read the book, all non-trivial, non-entry point procedures are made into Boolean functions that return TRUE if no error occurs. When I add the above code into a an entry point procedure, I have to delete any line with the variable bReturn in it because that’s only applicable for functions.

The part that I’m not totally clear on is for functions that return something other than Boolean. For instance, I have a function that reads a Jet table, fills an array, and returns a Variant array to be used to populate a ListBox. I can’t make this procedure a Boolean function because I need it to return an array. One option is to treat this function as an entry point, which has the effect of not pushing the error back into the calling procedure. This is the way I’ve been doing it. A second option is to make the function return a Boolean, but pass the “return” variable ByRef. This would change the way I call the procedure, but would still achieve the result I need.

Option 1:

Me.ListBox1.List = GetProducts(lProductID)

1	Me.ListBox1.List = GetProducts(lProductID)

Option 2:

If Not GetProducts(lProductID, aProducts()) Then Err.Raise glHandled_Error
Me.ListBox1.List = aProducts()

1 2	If Not GetProducts(lProductID, aProducts()) Then Err.Raise glHandled_Error Me.ListBox1.List = aProducts()

I’m having second thoughts about choosing Option 1. How do you do it?

Temperature Number Formats

Posted on May 2, 2007 by Dick Kusleika

I’m graphing some temperatures and need to display them with the degree symbol, but they need to remain numbers (not text) so they can be graphed. Here’s the number format:

+###°C;-###°C;_+0°C

The output looks like this:

60    +60°C
0    0°C
-20    -20°C

The three parts of the number format are separated by semicolons. The first part defines the format for positive numbers, the second part for negative numbers, and the third part for zero. The second and third parts are optional, as is a fourth part that defines the format for text.

To make the degree symbol, hold down the Alt key and type 0176 on the numeric keypad.

In the third part, I left enough space for a plus sign. To leave space, I include an underscore followed by the character whose width is the amount of space I want. I did this so that if the numbers are left aligned the C’s would line up. They don’t line up because for a couple of reasons. First, there can be multiple digits in the positive and negative numbers. Second, the plus sign and minus sign take up take up a different amount of space in Tahoma.

I’d like to come up with a format that lines up the C’s regardless of the cell alignment. I’d even be willing to limit the temperatures to -99 to +99. I assume this has will use conditions, but I couldn’t figure it out. Any ideas?