macro v active x control
 

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button (click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's not
relevant in Access)

thanks
bruce

macro v active x control
 

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button (click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's not
relevant in Access)

thanks
bruce

macro v active x control
 

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

macro v active x control
 

this works for me
Private Sub CommandButton1_Click()
Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range ("A1").Select


End Sub

"BLJ" wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

macro v active x control
 

hmmmm.... tried the code with two new clean files, new command button and
still get the error - says

run-time error '1004'
select method of Range class failed

debug stops on Rows.........

put the code as a macro and merely tools-macro-run ........ works fine

????????

bruce

this works for me
Private Sub CommandButton1_Click()
Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range ("A1").Select


End Sub

"BLJ" wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just
as
a plain macro, but not as code within a control ... which I gather is
the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various
cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure
it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro -
so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

macro v active x control
 

Unqualified ranges in a General module will refer to the activesheet.

But unqualified ranges behind a worksheet will refer to the worksheet owning the
code.

Workbooks.Open Filename:="C:\stats.xls"
workbooks("stats.xls").Activate
workbooks("stats.xls").worksheets("whatsthesheetna me").Rows(3).Insert

I would guess that the workbook you just opened is already active.

So you cold drop the select and activate:

Workbooks.Open Filename:="C:\stats.xls"
workbooks("stats.xls").worksheets("whatsthesheetna me").Rows(3).Insert




BLJ wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce




--

Dave Peterson

macro v active x control
 

"BLJ" wrote in message
u...
Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" .

maybe you have a problem with context, since you have no object qualifier
for rows()

have you tried

dim wb as workbook, ws as worksheet
set wb=Workbooks("\stats.xls")
set ws=wb.activesheet
ws.rows("3:3").select