oam
08-18-2014, 05:58 PM
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
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