Running a macro in a worksheet from a module

[oam]

I need to set an ActiveX Combo Box to blank when the workbook closes and when I try to call the macro in ThisWorkbook (WorksheetActivate2()) on close it is unable to complete. So I tried to use the macro in Module1 to locate and call the macro located in “Details” worksheet but I keep getting the error “Method 'Name' of object '_VBComponent' failed”.

Bottom-line, I need to be able to set the ActiveX Combo Box to blank on close of the workbook, any ideas?

Thank you for any and all your help.

In This Workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call WorksheetActivate2
End Sub

Clear Combobox

Sub WorksheetActivate2()
    ComboBox1.Value = ""
End Sub

Module 1

Sub ComboBoxDefault()
'Sets the ActiveX Combo Box to a default of “”

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Dim VBC As Variant
    Dim SheetName As String
    SheetName = "Details"
    Dim wb As Workbook
    Set wb = Workbooks("Status.xlsm")
    For Each VBC In wb.VBProject.VBComponents
    If VBC.Properties.Item("Name").Value = SheetName Then
    Application.Run (wb.Name & "!" & VBC.Name & ".WorksheetActivate2")
    End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

[westconn1]

did you try like

Sub ComboBoxDefault() 
  thisworkbook.worksheetactivate2
end sub

if the module is in someother workbook, change to workbooks("myworkbook.xls").worksheetactivat2

[Bob Phillips]

Maybe

Call Details.WorksheetActivate2

[snb]

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Sheet1.combobox1.clear 
End Sub

or

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Sheet1.combobox1.listindex=-1 
End Sub

[mikerickson]

I'd declare the routines in an object's code module as Public. That would let all the other modules call those routines.

[oam]

First off, thank you all for your quick replies.
westconn1:
I received a compile error "Method or Data member not found"
xld and snb:
I received a Run-Time error '424': "Object required"
mikerickson:
I have been unable to get the Combobox to clear without having the "Clear Combobox" shown above to be in the worksheet. I would like it to be called either on close or open but at this point I have not been successful.

Again, thank you all for your help.

[GTO]

Hi oam,

snb's code should work. I think you may not be aware of the worksheet's CodeName property. Select the worksheet that has the combobox. Right-click the tab and select view code. You are now looking at that worksheet's object module. Presumably you should have the Properties window displayed. In it, you will notice two Name properties, one (at the top) that has parenthesis surrounding it like "(Name)". This is the CodeName and the one we care about. Use whatever is there in place of 'Sheet1' in snb's first example.

How's that work?

Mark

[oam]

GTO:
Below is the Property window for the sheets. If I am understanding your statement, I should be using "Sheet1"?

Property.jpg

[westconn1]

as all the examples should work, any other suggestions are likely to have problems

can you post a sample workbook that demonstrates the problem

[snb]

The problem is that no such ActiveX control as combobox1 exists in sheet1. Add it before running the code again.

[oam]

GTO and snb,
SNB's second code worked after I deleted and re-added the ActiveX Combo Box. It now clears the Combo Box on close.

Thank you for all your help and patients