PDA

View Full Version : [SOLVED] Running a macro in a worksheet from a module



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

westconn1
08-19-2014, 03:29 AM
did you try like

Sub ComboBoxDefault()
thisworkbook.worksheetactivate2
end subif the module is in someother workbook, change to workbooks("myworkbook.xls").worksheetactivat2

Bob Phillips
08-19-2014, 05:14 AM
Maybe


Call Details.WorksheetActivate2

snb
08-19-2014, 06:43 AM
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
08-19-2014, 07:00 AM
I'd declare the routines in an object's code module as Public. That would let all the other modules call those routines.

oam
08-19-2014, 06:54 PM
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
08-19-2014, 07:27 PM
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
08-20-2014, 02:31 PM
GTO:
Below is the Property window for the sheets. If I am understanding your statement, I should be using "Sheet1"?

12166

westconn1
08-20-2014, 02:34 PM
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
08-21-2014, 06:39 AM
The problem is that no such ActiveX control as combobox1 exists in sheet1. Add it before running the code again.

oam
08-22-2014, 02:35 PM
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