passing variable

[sunilpatel]

i need to pass a variable (comname$) from worksheet code to an application
in a module.

i.e

Private Sub Worksheet_Activate()
comname$ = "Comboboxone"
Application.Run "test.xls'!loadcombo"
End Sub

in module 1...

Sub clearcombo()
ActiveSheet.com$.Clear 'where com$ is name of combobox
End Sub

Help will be appreciated.

Thanks

 

[Dave Peterson]

And this ClearCombo routine lives in a general module in the same workbook?

If yes, then you don't need to use Application.run.

I'd use this behind the worksheet

Option Explicit
Private Sub Worksheet_Activate()
Call ClearCombo(WhichCombo:=Me.ComboBox1)
End Sub

And this in a General module:
Option Explicit
Sub ClearCombo(WhichCombo As msforms.ComboBox)
WhichCombo.ListFillRange = "" 'just in case
WhichCombo.Clear
End Sub