Call a procedure in the workbook from a worksheet
 

I am using Excel 2000.
I create a button in a worksheet. On clicking this button, I like a
procedure "Main" in the workbook that contains the worksheet to run.
What is wrong with the following code in the worksheet ? When I click the
button, I obtain Run-time error '1004', Application-defined or object-defined
error.
Private Sub BtnRetrieve_Click()
ThisWorkbook.Main
MsgBox "End"
End Sub

For ThisWorkbook, I have
Option Explicit
Dim QtrData as string
Sub Main()
many VBA statements ....
End Sub

Call a procedure in the workbook from a worksheet
 

It sounds like you have an error in the Main procedure. Run it from the VBE
and see where it errors.

BTW, probably not a good idea to put general code in ThisWorkbook, better in
a normal code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Selina" wrote in message
...
I am using Excel 2000.
I create a button in a worksheet. On clicking this button, I like a
procedure "Main" in the workbook that contains the worksheet to run.
What is wrong with the following code in the worksheet ? When I click the
button, I obtain Run-time error '1004', Application-defined or
object-defined
error.
Private Sub BtnRetrieve_Click()
ThisWorkbook.Main
MsgBox "End"
End Sub

For ThisWorkbook, I have
Option Explicit
Dim QtrData as string
Sub Main()
many VBA statements ....
End Sub

Call a procedure in the workbook from a worksheet
 

Try declaring it as:

Public Sub Main()
many VBA statements ....
End Sub

--
Regards,
Tom Ogilvy

"Selina" wrote in message
...
I am using Excel 2000.
I create a button in a worksheet. On clicking this button, I like a
procedure "Main" in the workbook that contains the worksheet to run.
What is wrong with the following code in the worksheet ? When I click the
button, I obtain Run-time error '1004', Application-defined or
object-defined
error.
Private Sub BtnRetrieve_Click()
ThisWorkbook.Main
MsgBox "End"
End Sub

For ThisWorkbook, I have
Option Explicit
Dim QtrData as string
Sub Main()
many VBA statements ....
End Sub

Call a procedure in the workbook from a worksheet
 

is your main stored in thisworkbook code module?
NOT a good idea.
insert a normal module and put your code there.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Selina wrote :

I am using Excel 2000.
I create a button in a worksheet. On clicking this button, I like a
procedure "Main" in the workbook that contains the worksheet to run.
What is wrong with the following code in the worksheet ? When I
click the button, I obtain Run-time error '1004', Application-defined
or object-defined error.
Private Sub BtnRetrieve_Click()
ThisWorkbook.Main
MsgBox "End"
End Sub

For ThisWorkbook, I have
Option Explicit
Dim QtrData as string
Sub Main()
many VBA statements ....
End Sub